Hi,
I'm struggling with replacing a somewhat repetative "Select Case" block with something more efficient, which entails constructing the name of a variable from a string. Here's the working version:
As you can see the code loops through the controls on the form, if the controlname starts with "CheckStat" then it assigns the value of the control (all checkboxes) to a variable - the name of which is controlname & "var".
I've tried variations on a theme of:
...and having the sub in a class module, and refering to the global variables as objects, but I always have trouble with that sort of thing... help please!
Thanks in advance,
Phil
I'm struggling with replacing a somewhat repetative "Select Case" block with something more efficient, which entails constructing the name of a variable from a string. Here's the working version:
Code:
'dim...etc (all variables called CheckStat* are global & boolean)
CheckName = "CheckStat"
'Loop through controls on Form
For i = 0 To Forms!FMCRAdvanced.Count - 1
'If control is a stats checkbox then check status
If Left(Forms!FMCRAdvanced(i).Name, 9) = CheckName Then
BoxName = Forms!FMCRAdvanced(i).Name
'Set appropriate global variable to status to of checkbox
Select Case BoxName
Case "CheckStatVJack"
CheckStatVJackvar = Forms!FMCRAdvanced(i)
Case "CheckStatBJack"
CheckStatBJackvar = Forms!FMCRAdvanced(i)
Case "CheckStatMCRJackMean"
CheckStatMCRJackMeanvar = Forms!FMCRAdvanced(i)
Case "CheckStatPseudoMean"
CheckStatPseudoMeanvar = Forms!FMCRAdvanced(i)
Case "CheckStatOverMean"
CheckStatOverMeanvar = Forms!FMCRAdvanced(i)
Case "CheckStatSEJack"
CheckStatSEJackvar = Forms!FMCRAdvanced(i)
Case "CheckStatBRedJack"
CheckStatBRedJackvar = Forms!FMCRAdvanced(i)
End Select
End If
Next i
As you can see the code loops through the controls on the form, if the controlname starts with "CheckStat" then it assigns the value of the control (all checkboxes) to a variable - the name of which is controlname & "var".
I've tried variations on a theme of:
Code:
Dim CheckVar As ClassVar
Set CheckVar = New ClassVar
...
varname = "'" & BoxName & "var'"
CheckVar.Eval(varname) = Forms!FMCRAdvanced(i)
'which would represent:
'"CheckVar.CheckStatBJackvar = Forms!FMCRAdvanced(i)"
'etc... if I had my way.
...and having the sub in a class module, and refering to the global variables as objects, but I always have trouble with that sort of thing... help please!
Thanks in advance,
Phil