Can variables be used to select a fully qualified range?

[MPastor]

Is it possible to "build" VBA commands using variables (to avoid hardcoding)? If so, how do I actually "execute" them once the command is constructed?

I'm trying to use a set of UserForm defined variables for WORKBOOKS, SHEETS, etc. to select a range using a fully qualified reference with Workbooks(vWorkbookName).Sheets(vWorksheetName).Range(vRangeName), but I'm having a problem actually getting it "select" the range when the code is executed.

I think it may be because the variables for Workbook, Sheet and Range need to be enclosed in quotations, but I'm not sure.

I have three variables with the values indicated below:
vWorkbookName = "Coffee Break Server Rotation Tracking Updated.xls"
vSheetName = "Tracking"
vRangeName = "Week_Ending" (this is an actual Named Range)

I'm trying to create logic to execute the following without hardcoding the workbook, sheet and range as these values will change depending on the options selected by the user.

'Workbooks("Coffee Break Server Rotation Tracking Updated.xls").Worksheets("Tracking").Range("Week_Ending").Select

First I tried:

    Workbooks(vWorkbookName).vSheetName(vSheetName).Range(vRangeName).Select

But I received the error: Run-time error '438': Object doesn't support this property or method

Then I tried "creating" the same instruction by concatenating text strings and variables, as follows:

    vWhere = "Workbooks(""" & vRotationControlFileName & """).Worksheets(""" & _
              vRotationControlSheet & """).Range(""Week_Ending"")"
    vWhere.Select

The result of my string concatenation is shown here:

But, I received a COMPILE ERROR: Invalid qualifier with the vWhere highlighted before the ".Select".

My third, and final, attempt was to create a Function that I pass the vWhere variable string to the function. The ultimate goal is to select the desired range and perform a FIND command looking for the desired value. Here's the Function I attempted to use:

Public Function FindFunction(vWhat, vWhere) As Boolean
'   Returns TRUE if the Search Criteria is found
Dim vFindResults
vFindResults = vbFalse

MsgBox "vWhere: " & vWhere

vWhere.Select

MsgBox "vWhat: " & vWhat

On Error Resume Next
vFindResults = Selection.Find(What:=vWhat, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

If vFindResults Then
    MsgBox "SUCCESS!!! Desired text found!!!   vFindResults: " & vFindResults
Else
    MsgBox "Week ending date: " & vWhat & " not found in Rotation Control file."
End If

End Function

This time I received the message: Run-time error '424': Object Required
On the statement: vWhere.Select (the line between the two MsgBox statements)

Can someone help me with my approach(es) or suggest another way to accomplish this??

 

[Andrew Poulsom]

You were very close with your first attempt. This should work:

Workbooks(vWorkbookName).Worksheets(vSheetName).Range(vRangeName).Select

Object qualifires can be strings or variables containing strings.

 

[MPastor]

Andrew, THANK YOU!! When I read your reply, I realized I had a typo in my original post in the syntax for my first attempt: Workbooks(vWorkbookName).vSheetName(vSheetName).Range(vRangeName).Select

with the .vSheetname(vSheetname), however your one-liner definitely is working and now I'm not sure what my original syntax was. I've been battling what now appears to be a "self-inflicted gremlin" for two days, so THANK YOU!!!