Hi,

I am a newbie when it comes to writing VBA macros in Excel, so I will most likely get the jargon wrong. I have hit a dead end in searching for an explanation to an odd problem that I am having determining the size of array passed from Excel 2007 into my VBA function (macro). I will demonstrate with two examples.

Case 1 -------------------
An Excel cell has the following formula: =myFunction(A1:A6)

The macro is as follows:

Function myFunction(myArray As Variant)

Debug.Print IsArray(myArray)
myFunction = UBound(myArray, 1)

End Function

The Debug.Print statement outputs True, confirmning that myFunction is receiving an array. Yet the Excel cell evaluates to #VALUE! because of a type mismatch at the UBound assignment.

From what I have read the above should work. Contrast this with the following variation.

Case 2 -------------------
An Excel cell has the following formula: =myFunction1(A1:A6)

The macro is as follows:

Function myFunction1(myArray As Variant)

Temp = myArray

Debug.Print IsArray(Temp)
myFunction1 = UBound(Temp, 1)

End Function

This works.
-------------------

I don't understand what Temp = myArray does. Does it transfer a pointer? More importantly, why does it make the funciton work?

This is driving me nuts! Please. Help.

Cheers,

Sebulba1

Hi

It is not clear what you are trying to achieve (unless you really do want to know how many Rows are selected, but I would use the Range object (for that is what it is) ie.I would also recommend using Option Explicit to force variable declarations and syntax check. This can be done automatically by setting Require variable Declaration on the Edit tab on the Tool -> Option menu.

HTH


MTB

Hi MTB,

Thanks for reply. I had left out the meat of the code since I didn't want it to distract from the issue.

The actual function will receive a second parameter, in integer N. The goal is to take in an array of grades, sum the top N best and return the result.

I need to be able to determine whether a 1D or a 2D array is passed (for error checking). And, in the case of a 1D array I think that I need to know whether it is a row or a column vector. I will look into getting information on how ot use range objects.

Thanks also for the suggestion on the Option Explicit. I believe in tight programming. But until I could resolve my array passing ussue I had no idea of how to declare varaibles.

Cheers,

Sebulba1