Thread: How do you convert a string into a variable name?

Hello, I'm trying to setup a project so that all FILE UNIQUE constants are located in a "Global" module (for one-spot for an excel administrator to maintain) and declared as Public, and then used elsewhere in the project. The COUNT of these 'DataSheet' constants will vary from file to file (anywhere from 1 - ~15); and the NAMES of these Sheets follow NO particular structure whatsoever, could be any sort of name.
I'm looking for code that will convert a string into a variable so to use in a For/Next loop to accommodate this variation in COUNT #. Bad thing is that the line "Applicaton.Sheets("DataSheet" & i).EnableCalculation" below faults.
I'm looking for something kind of like the INDIRECT worksheet function. Does anything like that exist in VBA.

If there is no way to do this, then another way would be to build-up an array in a SUB in the Global module. So, administrator would only have to go to this SUB and tweak this SUB for the various files. Is this the only way to do what I want???

'These public constants located in a separate "Global" module
Public Const DataSheetCount As Integer = 2   'could vary from 1 to 15 depending on specific file
Public Const DataSheet1 As String = "Sheet1"
Public Const DataSheet2 As String = "Sheet2" 'add more in case where specific file has more than '2'

'This sub located in other module. The idea is that this code would not have to be altered
'for different files, where the DataSheetCount may be different than example '2'
Sub Update_Calcs()
 For i = 1 To DataSheetCount
  Applicaton.Sheets("DataSheet" & i).EnableCalculation = False 'Issue is that this line doesn't work
  Applicaton.Sheets("DataSheet" & i).EnableCalculation = True
 Next i
End Sub

Because all sheets belong to 1 collection you don't need to do it very complicatedly.
No need for 'variable constants'.

Sub Update_Calcs()
 For each sh in Thisworkbook.sheets
     sh.EnableCalculation = not sh.enablecalculation
 Next
End Sub

The reasons for the Enable Calc (F/T) is to update OSI PI mfgr processing plant data that does NOT update from a normal 'Calculate'. The various Data Sheets (1 up to ~15) contain cells that pull in data from this OSI PI server. Besides these Data Sheets, there are many other sheets that do normal Excel functions referencing these various PI data cells. I don't want to perform this "Enable Calcs (F/T)" task on EVERY sheet as this takes extra time that is unnecessary. I only want to perform this task on the sheets that contain the PI data functions. The other cells that only reference the PI function cells will go ahead and automatically update (via the normal Automatic Calculate) once the PI cells change; no need to do this Enable Calc F/T thing on them.

I had a 'For Each' collection loop, but now want to speed up this update process, so working on this modification led me to this question. ... I've got an array in the thing for now that the admin must manually tweak (and that isn't too bad), but the "Convert(String > Variable)" option would be a bit more user-friendly if such a trick existed. ... Any other ideas??? ... Thanks, sauerj

Maybe ...

Public Const gsDataSheets As String = "Sheet1/Sheet2/Bob's Your Uncle"

Sub Update_Calcs()
    Dim vSht As Variant
    
    For Each vSht In Split(gsDataSheets, "/")
        Worksheets(vSht).EnableCalculation = True
    Next vSht
End Sub

The / character is invalid in a sheet name, so splitting on it is safe.