|
Friday, June 12, 2009 - 8:13 AM
Storing worksheets and other objects in public variables is a great way to speed up event macros, subs called by your application's users, and even some worksheet UDFs (user-defined functions). That's because public variables are persistent even when code stops executing (as long as you don't use the "End" statement). This is not totally reliable, but either all public variables will remain set or none will, so testing whether gvTest remains set is sufficient. I call the PopGVs sub with SubStart, which you can find under the Application category on this site, because SubStart is always called when any of my code starts executing.
Option Explicit
Option Base 1
Public Sub PopGVs()
'populate global variables (this is just an example)
'requires reference to Microsoft Scripting Runtime (for dictionary object)
'uses myUbound function
'set ws arrays (storing sheets as global variables so code runs faster)
''could also use tab color (or anything else) to distinguish data, UI, and hidden sheets
'' or could loop through a list stored in a range or an array
'requires all data sheets' names contain "data"
'requires all hidden sheets' names end with "_"
'requires all UI sheets' names don't contain "_"
Dim ws, i&(4): ReDim wsData(1): ReDim wsTable(1): ReDim wsUI(1): ReDim wsHidden(1)
For Each ws In ThisWorkbook.Sheets
If CBool(InStr(1, ws.Name, "data", vbTextCompare)) Then
'data sheets' names must contain "data"
i(1) = i(1) + 1: ReDim Preserve wsData(i(1)): Set wsData(i(1)) = ws
ElseIf CBool(InStr(1, ws.Name, "table", vbTextCompare)) Then
'table sheets' names must contain "table"
i(2) = i(2) + 1: ReDim Preserve wsTable(i(2)): Set wsTable(i(2)) = ws
End If
If Right(ws.Name, 1) = "_" Then
'hidden sheets' names end with "_"
i(3) = i(3) + 1: ReDim Preserve wsHidden(i(3)): Set wsHidden(i(3)) = ws
ElseIf Not CBool(InStr(1, ws.Name, "_", vbTextCompare)) Then
'UI sheets' names don't contain "_"
i(4) = i(4) + 1: ReDim Preserve wsUI(i(4)): Set wsUI(i(4)) = ws
End If
Next
gvTest = True
End Sub
Public Function IndexFromWSArray&(WSName$, sType$)
'could use dictionaries to store sheet names instead
If Not gvTest Then PopGVs
Dim wsArray, i&
Select Case sType
Case "Data"
wsArray = wsData
Case "Table"
wsArray = wsTable
Case "UI"
wsArray = wsUI
Case "Hidden"
wsArray = wsHidden
End Select
For i = 1 To UBound(wsArray)
If (wsArray(i).Name) = WSName Then IndexFromWSArray = i: Exit Function
Next
End Function
|
|
| << Navigate to Friday, June 12, 2009 |
Add New Comment |