How do I pass an array as an argument to a VBA Function

[PaulFryer]

I cannot seem to find a way to pass an array to a Visual Basic function
from Excel, All I get is the #VALUE! error result on my worksheet,
telling me that "a value used in the formula is of the wrong data
type".

I have the VBA code as follows:

Function LastValue(Values()) As Single
Dim Hi As Integer, Lo As Integer, i As Integer
Dim thisValue As Single
Hi = UBound(Values)
Lo = LBound(Values)
For i = Hi To Lo Step -1
thisValue = Values(i)
If thisValue <> 0 Then
LastValue = thisValue
Exit Function
End If
Next i
LastValue = 0 ' if all entries in the array are zero
End Function

And the call on the worksheet is
=LastValue(B4:B11)

I get the same result if I try typecasting 'Values' as Variant, as
Single, etc., or if I use ByRef (ByVal gives a compile error), or if I
make the function Public.

This seems simple enough, and I can't see why it doesn't work. Is it
possible to pass arrays using Excel 2004 for Mac, and am I going about
it the right way?

Any help would be appreciated.

Thanks,
Paul.

 

[JE McGimpsey]

This seems simple enough, and I can't see why it doesn't work. Is it
possible to pass arrays using Excel 2004 for Mac, and am I going about
it the right way?

There are usually many different ways to accomplish the task. You're not
passing an array when you use

=LastValue(B4:B11)

Instead you're passing a range object. One way to deal with that would
be:

Public Function LastValue2(ByRef rng As Range) As Double
Dim i As Long
With rng
For i = .Count To 1 Step -1
If .Item(i).Value <> 0 Then
LastValue2 = .Item(i).Value
Exit Function
End If
Next i
End With
LastValue2 = 0
End Function


Note that i needs to be a Long to accommodate more than 32767 cells, and
the function should return a double, since that's what XL will coerce
the value to in any case.

One could make it more sophisticated by using variant as a parameter,
then testing for whether the parameter is a range object, an array of
values, or a simple Double, then deal with those separately within the
function.

Of course, it's probably easier, and definitely more efficient and
faster, to use built-in XL functions. For instance, if all cells are
filed up to the last one:

=INDEX(B4:B11,COUNTA(B4:B11))

Or, if there could be blanks:

=IF(COUNT(B4:B11),LOOKUP(9.99999999999999E+307,B4:B11),0)

 

[PaulFryer]

Thanks for the helpful reply. I am not a regular VB programmer, and I
normally only use it to create functions that I can't find any other
way.

I tried your suggestions for standard Excel functions, and several
variants, but none were able to do exactly what I need. So I am back
to creating something. The code example that you gave worked exactly
as I needed, and after some study of the documentation I sorted out how
it is working. So it gets the OK to be used.

A follow-on question though. Is this technique the way I should
proceed in future if I want to carry out some actions on a cell range
on a worksheet? Can I also use it to change worksheet range cell
values - I quickly tried it and it didn't work (rng.Item(i).Value =
expression), or is there something more I need to do? Or is there a
way to actually pass an array to the Function via the call from the
worksheet - as opposed to the range object I am currently passing?

Just trying to set myself straight for the next time!

Thanks again.

Paul.

 

[Bob Greenblatt]

Paul,

PMFJI, but by definition, a function called form a cell, like
=xxx(arguments) can ONLY return a value to the cell in which it is placed.
It is not possible to alter the contents or format of any other cell during
a function called during a calculation. If you need to change the values of
other cells you'll have to do it a different way, like an event macro, or
key activated macro. It just will not work if the macro is within the
calculation chain.

 

[PaulFryer]

Thanks, Bob. It's fairly obvious now you mention it, and I should have
realized that before. Something to file away for future reference.

I do appreciate this forum, and the time folks take to answer dumb
questions from people like me. I only wish I was knowledgeable enough
to do likewise, but I typically don't spend enough time in any
application to become an expert.

Anyway, thanks again.

Paul