By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Function GLookup(VLookup, HLookup, GArray)
  2. Dim TArray As String
  3. Dim BArray As String
  4. Dim VArray As String
  5. Dim HArray As String
  6. Dim VRef As Integer
  7. Dim HRef As Integer
  8. Dim Grid As Range
  9.  
  10. TArray = Left(GArray, InStr(GArray, ":") - 1)
  11. BArray = Right(GArray, Len(GArray) - (Len(TArray) + 1))
  12. VArray = TArray & ":" & IIf(IsNumeric(Mid(TArray, 2, 1)), Left(TArray, 1), Left(TArray, 2))
  13. VArray = VArray & IIf(IsNumeric(Mid(BArray, 2, 1)), Right(BArray, Len(BArray) - 1), Right(BArray, Len(BArray) - 2))
  14. HArray = TArray & ":" & IIf(IsNumeric(Mid(BArray, 2, 1)), Left(BArray, 1), Left(BArray, 2))
  15. HArray = HArray & IIf(IsNumeric(Mid(TArray, 2, 1)), Right(TArray, Len(TArray) - 1), Right(TArray, Len(TArray) - 2))
  16.  
  17. VRef = WorksheetFunction.Match(VLookup, Range(VArray), 0)
  18. HRef = WorksheetFunction.Match(HLookup, Range(HArray), 0)
  19.  
  20. GLookup = WorksheetFunction.Index(Range(GArray), VRef, HRef)
  21.  
  22.  
  23. End Function
  24.  
  25.  
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
Jul 27 '10 #1

✓ answered by 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(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:
Expand|Select|Wrap|Line Numbers
  1. Function GLookup (VLookup, HLookup, CellRange as Range)
  2. .
  3. .
  4. VRef = WorksheetFunction.Match(VLookup, CellRange, 0) 
  5. HRef = WorksheetFunction.Match(HLookup, CellRange, 0)
  6. .
  7. .
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 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:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Excel.Application
  2. ...
  3. Set objExcel = New Excel.Application
  4. ... (code to open a workbook missed out)
  5. ...
  6. With ObjExcel.ActiveSheet
  7.   .range(.Cells(lngRow, lngCol), .Cells(lngEndRow, lngEndCol)) = 0
  8. End With
  9.  
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
Share on Google+
1 Reply


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:
Expand|Select|Wrap|Line Numbers
  1. Function GLookup (VLookup, HLookup, CellRange as Range)
  2. .
  3. .
  4. VRef = WorksheetFunction.Match(VLookup, CellRange, 0) 
  5. HRef = WorksheetFunction.Match(HLookup, CellRange, 0)
  6. .
  7. .
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 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:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Excel.Application
  2. ...
  3. Set objExcel = New Excel.Application
  4. ... (code to open a workbook missed out)
  5. ...
  6. With ObjExcel.ActiveSheet
  7.   .range(.Cells(lngRow, lngCol), .Cells(lngEndRow, lngEndCol)) = 0
  8. End With
  9.  
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
Jul 27 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.