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.
First I tried:
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:
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:
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??
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.
Code:
'Workbooks("Coffee Break Server Rotation Tracking Updated.xls").Worksheets("Tracking").Range("Week_Ending").Select
Code:
Workbooks(vWorkbookName).vSheetName(vSheetName).Range(vRangeName).Select
Then I tried "creating" the same instruction by concatenating text strings and variables, as follows:
Code:
vWhere = "Workbooks(""" & vRotationControlFileName & """).Worksheets(""" & _
vRotationControlSheet & """).Range(""Week_Ending"")"
vWhere.Select
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:
Code:
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
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??