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