CODEWITHSUNDEEP
vbaexcel
Aurelius
Aurelius

Reputation: 485

Refer to Function name via passed varaible

This line:

Set ws = wb.Worksheets(Module2.FirstBBSName)

will set the worksheet as the name of the return from the function, which will be the name of a sheet.

However, in this sub, this will need to change to:

Set ws = wb.Worksheets(Module2.SecondBBSName)

or

Set ws = wb.Worksheets(Module2.ThirdBBSName)

and so on. I need to do this by passing the name from a call via a variable. Lets name it SheetNameReturn.

So if I send:

Call NameOfMySub ("SecondBBSName")

It will then not work if I use

Set ws = wb.Worksheets(Module2.SheetNameReturn)

As obviously it is looking for SheetNameReturn in Module2 and not the variable.

What do I need to do to make it look at the passed variable instead?

Upvotes: 2

Views: 46

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can run a function named by a variable with Application.Run.

See the code below - strFunction (could be SheetNameReturn) takes the name of the function (optionally with module prefix) and you then pass that string to Run to call the function.

Module1:

Option Explicit

Sub Test()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFunction As String

    Set wb = ThisWorkbook

    strFunction = "Module2.FirstBBSName"
    Set ws = wb.Worksheets(Application.Run(strFunction))
    Debug.Print ws.Name

    strFunction = "Module2.SecondBBSName"
    Set ws = wb.Worksheets(Application.Run(strFunction))
    Debug.Print ws.Name

    strFunction = "Module2.ThirdBBSName"
    Set ws = wb.Worksheets(Application.Run(strFunction))
    Debug.Print ws.Name

End Sub

Module2:

Option Explicit

Function FirstBBSName()
    FirstBBSName = "Sheet1"
End Function
Function SecondBBSName()
    SecondBBSName = "Sheet2"
End Function
Function ThirdBBSName()
    ThirdBBSName = "Sheet3"
End Function

Upvotes: 3

Related Questions