Error messages from user defined Excel worksheet function
 

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 (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 ===================

Error messages from user defined Excel worksheet function
 

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" wrote in message
...
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 (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 ===================

Error messages from user defined Excel worksheet function
 

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" (AndThis) wrote in
message ...
#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" wrote in message
...
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 (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 ===================

Error messages from user defined Excel worksheet function
 

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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 (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 ===================