P: 26

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,2003,"A1:G10") but I need it to work with Glookup(2003,2003,A1:G10)
Thanks
 
Hi. If you are passing in the range by calling the function within an Excel cell as a worksheet formula (e.g. "=GLookup(2003, 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.Cells(1, 1) to refer to cell A1) and the range object with the Cells method to return a range (for example ActiveSheet.Range(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 letternumber 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
Share this Question
Expert Mod 2.5K+
P: 2,545

Hi. If you are passing in the range by calling the function within an Excel cell as a worksheet formula (e.g. "=GLookup(2003, 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.Cells(1, 1) to refer to cell A1) and the range object with the Cells method to return a range (for example ActiveSheet.Range(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 letternumber 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
    Question stats  viewed: 17040
 replies: 1
 date asked: Jul 27 '10
