Re-entrant call to VBA

[LesHurley]

About a week ago I posted a question about using VBA functions in formulas.
Here
it is again with a much simpler function. XtoN raises a square matrix to an
integer power. The code is as follows. Notice that the third line is active
while the fourth is commented. As it stands here the call =xton(xton(M,2),2)
works ok but an essential test is missing. If I comment the third line and
make the fourth line active, the same call makes the function crash. Can
anyone say why, and how can I make the same test without crashing?

Function XtoN(Matrix, N)
Fraction = Abs(N) - Int(Abs(N))
'(Line 3)
If Fraction <> 0 Then
'(Line 4)
'If Matrix.Rows.Count <> Matrix.Columns.Count Or Fraction <> 0 Then
MsgBox "X must be a Square Matrix" & vbNewLine & "N must be an
Integer => 1"
Exit Function
End If

Dim i As Integer

B = Matrix
For i = 1 To N - 1
B = WorksheetFunction.MMult(Matrix, B)
Next i
XtoN = B
End Function


'Thanks for your help

 

[Charles Williams]

You can only use Matrix.Rows.Count if Matrix is a range object (or in your
case a variant containing a range object).

But XtoN does not return a Range, it returns a Variant containing a
2-dimensional array of numbers, so the outer call has Matrix as a variant
and Matrix.Rows.Count fails.

You either need to test what type of data Matrix contains, or (probably
better) just assign it to a variant:
something like this (not tested)

Function XtoN(Matrix as variant, N) as variant
Dim i as long
Dim M as variant

M=Matrix ''' converts range to variant containing an array

Fraction = Abs(N) - Int(Abs(N))
If Fraction <> 0 Then
If ubound(M,1)<> ubound(m,2) Or Fraction <> 0 Then
MsgBox "X must be a Square Matrix" & vbNewLine & "N must be an
Integer => 1"
Exit Function
End If
end if


B = Matrix
For i = 1 To N - 1
B = WorksheetFunction.MMult(Matrix, B)
Next i
XtoN = B

End Function


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[LesHurley]

OK Charles, I'll try out those ideas. Actually I use
MRows=Object.Rows.Count, etc. in a number of functions that use them to
Dimension arrays with no trouble unless I try to use the function in a
formula. So getting this problem straitened out will solve a bunch of
others. Thanks. Les

 

[LesHurley]

Well Charles, the lights just came on: I can see why all those functions
work as stand alone procedures but not as I am using them.
Object.Rows.Count applies to an object in the Excel worksheet. But similar
Worksheet functions work Ok such as
MMult(MMult(a,b),c) etc. So there is a solution and I have some work to do
to find it in the general case.
Thanks for your help