How to pass a Range to a Function in VBA (In Excel)

[evilbungle]

Hi,

I know this is for Access but I was hoping someone may know about Excel and can help me.

I have created a Function in VBA for looking up a value in a grid it works fine but only if I place the Range as a string and I really need to be able to just select the cells as you would with a standard function.

Here is my Function

Function GLookup(VLookup, HLookup, GArray)
Dim TArray As String
Dim BArray As String
Dim VArray As String
Dim HArray As String
Dim VRef As Integer
Dim HRef As Integer
Dim Grid As Range

TArray = Left(GArray, InStr(GArray, ":") - 1)
BArray = Right(GArray, Len(GArray) - (Len(TArray) + 1))
VArray = TArray & ":" & IIf(IsNumeric(Mid(TArray, 2, 1)), Left(TArray, 1), Left(TArray, 2))
VArray = VArray & IIf(IsNumeric(Mid(BArray, 2, 1)), Right(BArray, Len(BArray) - 1), Right(BArray, Len(BArray) - 2))
HArray = TArray & ":" & IIf(IsNumeric(Mid(BArray, 2, 1)), Left(BArray, 1), Left(BArray, 2))
HArray = HArray & IIf(IsNumeric(Mid(TArray, 2, 1)), Right(TArray, Len(TArray) - 1), Right(TArray, Len(TArray) - 2))

VRef = WorksheetFunction.Match(VLookup, Range(VArray), 0)
HRef = WorksheetFunction.Match(HLookup, Range(HArray), 0)

GLookup = WorksheetFunction.Index(Range(GArray), VRef, HRef)


End Function

Vlookup is the Value to find at the Left, Hlookup is the value to find at the top and GArray is the Range to look at.

Like I say it will work with Glookup(2003,20 03,"A1:G10") but I need it to work with Glookup(2003,20 03,A1:G10)

Thanks

[Stewart Ross]

Hi. If you are passing in the range by calling the function within an Excel cell as a worksheet formula (e.g. "=GLookup(2 003, 2003, A1:G10)") you can change your definition of parameter GArray to be a range object. This has the advantage that you don't have to convert the cell references to a range, as the argument is already a range and can be passed to other functions directly as needed.

For clarity I would define the argument type explicitly in the header:

Function GLookup (VLookup, HLookup, CellRange as Range)
.
.
VRef = WorksheetFunction.Match(VLookup, CellRange, 0) 
HRef = WorksheetFunction.Match(HLookup, CellRange, 0)
.
.

The argument representation you want to use - GLookup(2003, 2003, A1:G10) - is only valid if you are calling the function from within an Excel worksheet formula, where it is natural to pass a range directly to the function.

If you are not calling the function from an Excel cell formula it would help if you could explain further why a string representation of a range is not suitable.

For general purpose representation of ranges I use the Cells(row, column) method to return a single cell (for example ActiveSheet.Cel ls(1, 1) to refer to cell A1) and the range object with the Cells method to return a range (for example ActiveSheet.Ran ge(Cells(1,1), Cells(10, 8)) to refer to the range A1:G10). This allows general processing of cells by representation of rows and columns using numeric variables within loops and so on, which is easier and more natural to work with (and code) than the letter-number representation of A1 and so on.

By the way, the use of the Cells method within a range object as I've put it above will only work without further qualification directly in an Excel workbook, where the Activesheet is implicit if there is no qualification. If the code was running within another office application using Excel as an automation server the Excel object and worksheet involved would need to be referred to explicitly:

Dim objExcel as Excel.Application
...
Set objExcel = New Excel.Application
... (code to open a workbook missed out)
...
With ObjExcel.ActiveSheet
  .range(.Cells(lngRow, lngCol), .Cells(lngEndRow, lngEndCol)) = 0
End With

The Cells method returns a Range object. It may seem strange then to use it within a Range object but it is entirely valid to do so.

-Stewart