[dba-Tech] Excel referencing examples

Hi Susan

Here's a small example including a useful RLookup function I have used to look up configuration data in a worksheet.
It of course uses ranges. It was made in Excel 97 but should work in any newer version as well.

<code>

Function DLookupSystem(strFieldName As String) As Variant

' Look up a value in the System range in the data worksheet.

  Const cstrWorkbookData As String = "PeriodsData.xls"
  Const cstrWorkSheet As String = "System"
  Const cstrRange As String = "xlsSystem"
  
  Dim wkb As Workbook
  Dim wks As Worksheet
  Dim rng As Range
  
  Dim varTemp As Variant
  
  Set wkb = Workbooks(cstrWorkbookData)
  Set wks = wkb.Worksheets(cstrWorkSheet)
  Set rng = wks.Range(cstrRange)
  
  varTemp = RLookup(rng, strFieldName, xlByRows)
  If IsNull(varTemp) Then
    varTemp = 0
  End If
  
  Set wkb = Nothing
  Set wks = Nothing
  Set rng = Nothing
    
  DLookupSystem = varTemp

End Function


Function RLookup(ByVal rngLookup As Range, _
                 ByVal strField As String, _
                 Optional intSearchOrder As Integer = xlByRows, _
                 Optional intOffset As Integer = 1) As Variant

' Looks up a value in the named range rngLookup from the cell,
' offset intOffset rows below or columns right from the found cell.
'
' 2000-07-30.
' Cactus Data ApS, Gustav Brock

' Default intSearchOrder is xlByRows, as named ranges built this way
' are easily attached by Access.
  
  Dim rng       As Range
  Dim intRow    As Integer
  Dim intColumn As Integer
  
  ' No special errorhandling.
  On Error Resume Next

  If intSearchOrder = xlByRows Then
    ' Search by rows.
    ' Read from found row, offset intOffset rows, and found column.
  Else
    ' Search by columns.
    ' Read from found row and found column, offset intOffset columns.
    intSearchOrder = xlByColumns
  End If
  
  With rngLookup
    ' Start search from upper left cell in range by starting in lower right cell.
    ' Search case sensitive for whole words in values only.
    Set rng = .Find(strField, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, intSearchOrder, , True)
  End With
  If Not rng Is Nothing Then
    ' Searched value found.
    ' Lookup value to retrieve.
    With rng
      intRow = .Row - (intOffset * (intSearchOrder = xlByRows))
      intColumn = .Column - (intOffset * (intSearchOrder = xlByColumns))
      ' Return value, offset intOffset rows or columns from cell with found value.
      RLookup = .Worksheet.Cells(intRow, intColumn).Value
    End With
    Set rng = Nothing
  End If

End Function

</code>

/gustav


>>> ssharkins at gmail.com 21-08-2009 18:36 >>>
I'm writing about referencing workbooks and worksheets using VBA. As usual, 
the hardest part of the entire article is coming up with realistic examples. 
Right now, I'm stuck with such inventive snippets as

Function ActivateWB(wbname As String)
  'Activate wbname.
  Workbooks(wbname).Activate
End Function

How exciting! ;)

If you'd like to share code that uses any VBA method to reference a workbook 
or worksheet (not charts), I'd be glad to take a look at it. Please send 
short procedures -- just a few lines. Also, needs to be fairly generic. 
After all, the syntax is the article's purpose, not the code's purpose.

As usual, you'll get a bit of fame, as I'll give you credit for the code in 
the article.

Susan H.