[dba-Tech] Excel referencing examples

Hi Susan

Too involved? Then just look at DLookupSystem and explain it pulls a value from the referenced range.
It doesn't make much sense to reference objects without doing anything to or with them.

/gustav


>>> ssharkins at gmail.com 21-08-2009 19:12 >>>
LOL -- wow!

Seriously, this is going to be hard for any of you to fulfill... if the code 
is too involved, it distracts from the article's purpose.

But thanks for playing -- please try again! ;)

Susan H.



> 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.