Error messages from user defined Excel worksheet function

I am developing a VBA function to be used as an Excel worksheet function.
Some error checking is included in the VBA. My problem is that Excel
executes the function before all input arguments have been specified. This
gives rise to some very disturbing messages from my error checking.

How can I prevent Excel from running the function during the specification
of input ?

I have attached a screen dump with a small example that illustrates the
problem. The VBA is below. The screen dump shows the situation when I have
entered ARange and is going on to enter BRange. I click C3 and (while
holding down the Shift key) I try to click D4. But inbetween Excel runs the
function and recognizes that BRange is too small, i.e. BRange is C3.

Any ideas ? I could suggest one myself : Remove all calls to MsgBox, and
assign an error value #VALUE! to the function. Any better ideas ?

Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).

Kind regards,
Helge

'=================== VBA start ===================
Option Explicit
Option Base 1

Function AAAA(ByVal aRange As Range, _
ByVal bRange As Range) As Variant

Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b As
Integer
Dim R As Integer, C As Integer

Nrow_a = aRange.Rows.Count
Nrow_b = bRange.Rows.Count
Ncol_a = aRange.Columns.Count
Ncol_b = bRange.Columns.Count

If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
Call MsgBox("The two input ranges should have the same number of rows
and columns.", _
vbCritical, "ERROR")
End If

AAAA = 0
For R = 1 To Nrow_a
For C = 1 To Ncol_a
AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
Next C
Next R

End Function
'=================== VBA end ===================

I would disagree that showing a message box in a function that will be used
in a worksheet is desirable. It certainly doesn't conform to the way other
functions work. When an error does occur, should the msgbox pop up everytime
there is a recalc? also, even though you show the messagebox, your code go
aheads and raises another error by trying to execute with the flawed ranges.

Since the error is a reference, I would show a #Ref and get out.

--
Regards,
Tom Ogilvy


"Helge V. Larsen" wrote:

> I am developing a VBA function to be used as an Excel worksheet function.
> Some error checking is included in the VBA. My problem is that Excel
> executes the function before all input arguments have been specified. This
> gives rise to some very disturbing messages from my error checking.
>
> How can I prevent Excel from running the function during the specification
> of input ?
>
> I have attached a screen dump with a small example that illustrates the
> problem. The VBA is below. The screen dump shows the situation when I have
> entered ARange and is going on to enter BRange. I click C3 and (while
> holding down the Shift key) I try to click D4. But inbetween Excel runs the
> function and recognizes that BRange is too small, i.e. BRange is C3.
>
> Any ideas ? I could suggest one myself : Remove all calls to MsgBox, and
> assign an error value #VALUE! to the function. Any better ideas ?
>
> Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).
>
> Kind regards,
> Helge
>
> '=================== VBA start ===================
> Option Explicit
> Option Base 1
>
> Function AAAA(ByVal aRange As Range, _
> ByVal bRange As Range) As Variant
>
> Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b As
> Integer
> Dim R As Integer, C As Integer
>
> Nrow_a = aRange.Rows.Count
> Nrow_b = bRange.Rows.Count
> Ncol_a = aRange.Columns.Count
> Ncol_b = bRange.Columns.Count
>
> If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
> Call MsgBox("The two input ranges should have the same number of rows
> and columns.", _
> vbCritical, "ERROR")
> End If
>
> AAAA = 0
> For R = 1 To Nrow_a
> For C = 1 To Ncol_a
> AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
> Next C
> Next R
>
> End Function
> '=================== VBA end ===================
>
>
>
>

#Ref or #VALUE! ?
Excel uses #VALUE! as shown in the attached JPG file.
--- and it is errors equivalent to the one shown in the JPG file that I
would like to trap. (The two ranges are not of the same size.)
Regards,
Helge
_______________________


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>I would disagree that showing a message box in a function that will be used
> in a worksheet is desirable. It certainly doesn't conform to the way
> other
> functions work. When an error does occur, should the msgbox pop up
> everytime
> there is a recalc? also, even though you show the messagebox, your code
> go
> aheads and raises another error by trying to execute with the flawed
> ranges.
>
> Since the error is a reference, I would show a #Ref and get out.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Helge V. Larsen" wrote:
>
>> I am developing a VBA function to be used as an Excel worksheet function.
>> Some error checking is included in the VBA. My problem is that Excel
>> executes the function before all input arguments have been specified.
>> This
>> gives rise to some very disturbing messages from my error checking.
>>
>> How can I prevent Excel from running the function during the
>> specification
>> of input ?
>>
>> I have attached a screen dump with a small example that illustrates the
>> problem. The VBA is below. The screen dump shows the situation when I
>> have
>> entered ARange and is going on to enter BRange. I click C3 and (while
>> holding down the Shift key) I try to click D4. But inbetween Excel runs
>> the
>> function and recognizes that BRange is too small, i.e. BRange is C3.
>>
>> Any ideas ? I could suggest one myself : Remove all calls to MsgBox, and
>> assign an error value #VALUE! to the function. Any better ideas ?
>>
>> Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).
>>
>> Kind regards,
>> Helge
>>
>> '=================== VBA start ===================
>> Option Explicit
>> Option Base 1
>>
>> Function AAAA(ByVal aRange As Range, _
>> ByVal bRange As Range) As Variant
>>
>> Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b
>> As
>> Integer
>> Dim R As Integer, C As Integer
>>
>> Nrow_a = aRange.Rows.Count
>> Nrow_b = bRange.Rows.Count
>> Ncol_a = aRange.Columns.Count
>> Ncol_b = bRange.Columns.Count
>>
>> If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
>> Call MsgBox("The two input ranges should have the same number of
>> rows
>> and columns.", _
>> vbCritical, "ERROR")
>> End If
>>
>> AAAA = 0
>> For R = 1 To Nrow_a
>> For C = 1 To Ncol_a
>> AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
>> Next C
>> Next R
>>
>> End Function
>> '=================== VBA end ===================
>>
>>
>>
>>

If the range argument is improper, I would think #Ref, but I guess that is up
to you.

I don't see any .jpg file.

--
Regards,
Tom Ogilvy


"Helge V. Larsen" wrote:

> #Ref or #VALUE! ?
> Excel uses #VALUE! as shown in the attached JPG file.
> --- and it is errors equivalent to the one shown in the JPG file that I
> would like to trap. (The two ranges are not of the same size.)
> Regards,
> Helge
> _______________________
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> >I would disagree that showing a message box in a function that will be used
> > in a worksheet is desirable. It certainly doesn't conform to the way
> > other
> > functions work. When an error does occur, should the msgbox pop up
> > everytime
> > there is a recalc? also, even though you show the messagebox, your code
> > go
> > aheads and raises another error by trying to execute with the flawed
> > ranges.
> >
> > Since the error is a reference, I would show a #Ref and get out.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Helge V. Larsen" wrote:
> >
> >> I am developing a VBA function to be used as an Excel worksheet function.
> >> Some error checking is included in the VBA. My problem is that Excel
> >> executes the function before all input arguments have been specified.
> >> This
> >> gives rise to some very disturbing messages from my error checking.
> >>
> >> How can I prevent Excel from running the function during the
> >> specification
> >> of input ?
> >>
> >> I have attached a screen dump with a small example that illustrates the
> >> problem. The VBA is below. The screen dump shows the situation when I
> >> have
> >> entered ARange and is going on to enter BRange. I click C3 and (while
> >> holding down the Shift key) I try to click D4. But inbetween Excel runs
> >> the
> >> function and recognizes that BRange is too small, i.e. BRange is C3.
> >>
> >> Any ideas ? I could suggest one myself : Remove all calls to MsgBox, and
> >> assign an error value #VALUE! to the function. Any better ideas ?
> >>
> >> Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).
> >>
> >> Kind regards,
> >> Helge
> >>
> >> '=================== VBA start ===================
> >> Option Explicit
> >> Option Base 1
> >>
> >> Function AAAA(ByVal aRange As Range, _
> >> ByVal bRange As Range) As Variant
> >>
> >> Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b
> >> As
> >> Integer
> >> Dim R As Integer, C As Integer
> >>
> >> Nrow_a = aRange.Rows.Count
> >> Nrow_b = bRange.Rows.Count
> >> Ncol_a = aRange.Columns.Count
> >> Ncol_b = bRange.Columns.Count
> >>
> >> If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
> >> Call MsgBox("The two input ranges should have the same number of
> >> rows
> >> and columns.", _
> >> vbCritical, "ERROR")
> >> End If
> >>
> >> AAAA = 0
> >> For R = 1 To Nrow_a
> >> For C = 1 To Ncol_a
> >> AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
> >> Next C
> >> Next R
> >>
> >> End Function
> >> '=================== VBA end ===================
> >>
> >>
> >>
> >>
>
>
>

Try the below adjustments. (untested)

Function AAAA(ByVal aRange As Range, _
ByVal bRange As Range) As Variant

Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b As
Integer
Dim R As Integer, C As Integer
Dim rng as Range

Nrow_a = aRange.Rows.Count
Nrow_b = bRange.Rows.Count
Ncol_a = aRange.Columns.Count
Ncol_b = bRange.Columns.Count
On error resume next
set rng = Application.Caller
On error goto 0
if rng is nothing then
If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
Call MsgBox("The two input ranges should have the same number of rows
and columns.", _
vbCritical, "ERROR")
End If
End if
AAAA = 0
For R = 1 To Nrow_a
For C = 1 To Ncol_a
AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
Next C
Next R

End Function

--
Regards,
Tom Ogilvy

"Helge V. Larsen" <Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis)> wrote in
message news:[email protected]...
> #Ref or #VALUE! ?
> Excel uses #VALUE! as shown in the attached JPG file.
> --- and it is errors equivalent to the one shown in the JPG file that I
> would like to trap. (The two ranges are not of the same size.)
> Regards,
> Helge
> _______________________
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> >I would disagree that showing a message box in a function that will be
used
> > in a worksheet is desirable. It certainly doesn't conform to the way
> > other
> > functions work. When an error does occur, should the msgbox pop up
> > everytime
> > there is a recalc? also, even though you show the messagebox, your
code
> > go
> > aheads and raises another error by trying to execute with the flawed
> > ranges.
> >
> > Since the error is a reference, I would show a #Ref and get out.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Helge V. Larsen" wrote:
> >
> >> I am developing a VBA function to be used as an Excel worksheet
function.
> >> Some error checking is included in the VBA. My problem is that Excel
> >> executes the function before all input arguments have been specified.
> >> This
> >> gives rise to some very disturbing messages from my error checking.
> >>
> >> How can I prevent Excel from running the function during the
> >> specification
> >> of input ?
> >>
> >> I have attached a screen dump with a small example that illustrates the
> >> problem. The VBA is below. The screen dump shows the situation when I
> >> have
> >> entered ARange and is going on to enter BRange. I click C3 and (while
> >> holding down the Shift key) I try to click D4. But inbetween Excel runs
> >> the
> >> function and recognizes that BRange is too small, i.e. BRange is C3.
> >>
> >> Any ideas ? I could suggest one myself : Remove all calls to MsgBox,
and
> >> assign an error value #VALUE! to the function. Any better ideas ?
> >>
> >> Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).
> >>
> >> Kind regards,
> >> Helge
> >>
> >> '=================== VBA start ===================
> >> Option Explicit
> >> Option Base 1
> >>
> >> Function AAAA(ByVal aRange As Range, _
> >> ByVal bRange As Range) As Variant
> >>
> >> Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b
> >> As
> >> Integer
> >> Dim R As Integer, C As Integer
> >>
> >> Nrow_a = aRange.Rows.Count
> >> Nrow_b = bRange.Rows.Count
> >> Ncol_a = aRange.Columns.Count
> >> Ncol_b = bRange.Columns.Count
> >>
> >> If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
> >> Call MsgBox("The two input ranges should have the same number of
> >> rows
> >> and columns.", _
> >> vbCritical, "ERROR")
> >> End If
> >>
> >> AAAA = 0
> >> For R = 1 To Nrow_a
> >> For C = 1 To Ncol_a
> >> AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
> >> Next C
> >> Next R
> >>
> >> End Function
> >> '=================== VBA end ===================
> >>
> >>
> >>
> >>
>
>
>

Guess it doesn't show in the communities interface (or I don't know where to
look).

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> If the range argument is improper, I would think #Ref, but I guess that is
up
> to you.
>
> I don't see any .jpg file.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Helge V. Larsen" wrote:
>
> > #Ref or #VALUE! ?
> > Excel uses #VALUE! as shown in the attached JPG file.
> > --- and it is errors equivalent to the one shown in the JPG file that I
> > would like to trap. (The two ranges are not of the same size.)
> > Regards,
> > Helge
> > _______________________
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > >I would disagree that showing a message box in a function that will be
used
> > > in a worksheet is desirable. It certainly doesn't conform to the way
> > > other
> > > functions work. When an error does occur, should the msgbox pop up
> > > everytime
> > > there is a recalc? also, even though you show the messagebox, your
code
> > > go
> > > aheads and raises another error by trying to execute with the flawed
> > > ranges.
> > >
> > > Since the error is a reference, I would show a #Ref and get out.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Helge V. Larsen" wrote:
> > >
> > >> I am developing a VBA function to be used as an Excel worksheet
function.
> > >> Some error checking is included in the VBA. My problem is that Excel
> > >> executes the function before all input arguments have been specified.
> > >> This
> > >> gives rise to some very disturbing messages from my error checking.
> > >>
> > >> How can I prevent Excel from running the function during the
> > >> specification
> > >> of input ?
> > >>
> > >> I have attached a screen dump with a small example that illustrates
the
> > >> problem. The VBA is below. The screen dump shows the situation when I
> > >> have
> > >> entered ARange and is going on to enter BRange. I click C3 and
(while
> > >> holding down the Shift key) I try to click D4. But inbetween Excel
runs
> > >> the
> > >> function and recognizes that BRange is too small, i.e. BRange is C3.
> > >>
> > >> Any ideas ? I could suggest one myself : Remove all calls to MsgBox,
and
> > >> assign an error value #VALUE! to the function. Any better ideas ?
> > >>
> > >> Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).
> > >>
> > >> Kind regards,
> > >> Helge
> > >>
> > >> '=================== VBA start ===================
> > >> Option Explicit
> > >> Option Base 1
> > >>
> > >> Function AAAA(ByVal aRange As Range, _
> > >> ByVal bRange As Range) As Variant
> > >>
> > >> Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer,
Ncol_b
> > >> As
> > >> Integer
> > >> Dim R As Integer, C As Integer
> > >>
> > >> Nrow_a = aRange.Rows.Count
> > >> Nrow_b = bRange.Rows.Count
> > >> Ncol_a = aRange.Columns.Count
> > >> Ncol_b = bRange.Columns.Count
> > >>
> > >> If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
> > >> Call MsgBox("The two input ranges should have the same number
of
> > >> rows
> > >> and columns.", _
> > >> vbCritical, "ERROR")
> > >> End If
> > >>
> > >> AAAA = 0
> > >> For R = 1 To Nrow_a
> > >> For C = 1 To Ncol_a
> > >> AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
> > >> Next C
> > >> Next R
> > >>
> > >> End Function
> > >> '=================== VBA end ===================
> > >>
> > >>
> > >>
> > >>
> >
> >
> >