Variable name in a variable? 4

[MePenguin]

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:

'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:

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

 

[Tranman]

He shoots, he scores!!! Three points for CC.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[MePenguin]

So Tranman, maybe the critical comment there for me was

If you're only going to iterate, and you don't have a list of the control names, you have to wonder why not just use an array of concatenated strings...

If I forget the title of my original post, and do this:

Dim tb As Control
Dim var As Variant
Dim CheckName, VarName As String
Dim CheckStatus As Boolean
Dim CheckCounter, testloop As Integer

CheckCounter = 0
CheckName = "CheckStat"
ReDim CheckArray(1, 0) 'Global, variant

For Each tb In Me.Controls
    Select Case tb.ControlType
        Case acCheckBox
            If Left(tb.Name, 9) = CheckName Then
                ReDim Preserve CheckArray(1, UBound(CheckArray, 2) + 1)
                tb.SetFocus
                CheckStatus = tb
                VarName = tb.Name & "Var"
                CheckArray(0, CheckCounter) = VarName
                CheckArray(1, CheckCounter) = CheckStatus
                CheckCounter = CheckCounter + 1
            End If
    End Select
Next

... and then I can do howevermany (that's like more than many, but limited) loops and if's using the global CheckArray()

You get a star for bringing me back to Earth.

This is where TheAceMan steps in and says 'I told you so!'

You appear to be going the long way around to accomplish something . . . .

... I don't know how this staring works - should he get a star for that too?

Will work through the other sections of the program and let you know if it works.

Phil

 

[MePenguin]

...and the CajunCenturian gets a * for the dictionary idea, looks very useful.

 

[Tranman]

Phil,
We've come the whole circle, haven't we? It's funny how we sometimes go around and around to end up back where we started, but wiser. Sort of like programming mirrors life.

Your logic looks like it would work fine. The idea of using a multi-dimensional array with the "ID" hard-coded is a good one.

The star concept is unique. Some people are really motivated by them. Others, much less so. If you nose around on the site, you'll even find a place where you can buy shirts and coffee cups that say things like, "Will program for little stars".

Whether to give someone a star or not is a fairly subjective thing--we're all supposed to be IT professionals here, and as such, should reasonably be expected to be able to solve normal IT problems. Some people give stars to people who hang in there for a long time solving a problem. I personally tend to award them to people whose contributions are either technically creative, or very insightful (like Cajun's suggestion to use the Scripting.Dictionary so you could fetch an array of key names).

Some people who hang out here, like PHV and CajunCenturion, are so consistently creative AND insightful that they must have whole constellations of little stars reeling around their heads. I don't let that stop me from giving them another one if they deserve it .

It sounds like you're well on your way to a solution. Please do post back and let us know how the project comes out.

Good Luck,
Tranman



"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[MePenguin]

Solved. Many thanks for everybody's help!

Ironically, I could use the dictionary object idea in the end - it sounded so good I couldn't let it lie.

Here's the gist of it, if you're interested:

Put checkbox names and status in ChecksArray

For Each tb In Me.Controls
    Select Case tb.ControlType
        Case acCheckBox
            If Left(tb.Name, 9) = CheckName Then
                ReDim Preserve ChecksArray(1, UBound(ChecksArray, 2) + 1)
                tb.SetFocus
                ChecksArray(0, CheckCounter) = tb.Name
                ChecksArray(1, CheckCounter) = tb
                CheckCounter = CheckCounter + 1
            End If
    End Select
Next

Expand StatsArray according to how many check boxes are true (results go in this)

StatsRowsCounter = 0
For ChecksLoop = 0 To UBound(ChecksArray, 2)
    If ChecksArray(1, ChecksLoop) = True Then
        ReDim Preserve StatsArray(7, UBound(StatsArray, 2) + 1)
        StatsRowsCounter = StatsRowsCounter + 1
    End If
Next ChecksLoop

...calculate things...

Store stats in dictionary object

Dim d As Dictionary
Set d = New Dictionary
    d.Add "MCRJackMean", MCRJackMean
    d.Add "PseudoMean", PseudoMean
    d.Add "SEJack", SEJack
    d.Add "VJack", VJack
    d.Add "BJack", BJack
    d.Add "BRedJack", BRedJack

And finally store the dictionary items in StatsArray, for those checkboxes that are true

For ChecksLoop = 0 To UBound(ChecksArray, 2)
    If ChecksArray(1, ChecksLoop) = True And ChecksArray(1, ChecksLoop) <> "JackGraph" Then
        CurrentCheck = Right(ChecksArray(0, ChecksLoop), Len(ChecksArray(0, ChecksLoop)) - 9)
        StatsArray(0, StatsRowPointer) = sample
        StatsArray(1, StatsRowPointer) = CurrentCheck
        StatsArray(LoopVar + 1, StatsRowPointer) = d.Item(CurrentCheck)
        StatsRowPointer = StatsRowPointer + 1
    End If
Next ChecksLoop

before exporting all the arrays to Excel.

Couldn't have done it without all of you. I'll upload the whole db on my website later this summer, need to figure out how to USE the stat's, now that I can calculate them efficiently.

May you be forever showered in tiny stars

 

[CajunCenturion]

Outstanding MePenguin.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MePenguin]

Hi guys, a little update, if anyone is interested. These dictionary things are great! Got rid of the CheckArray and replaced it with a dictionary too.

Map the checkbox names and values to Checkdict dictionary:

For Each checkb In Me.Controls
    Select Case checkb.ControlType
        Case acCheckBox
            If Left(checkb.Name, 9) = CheckName Then
                checkb.SetFocus
                CheckDict.Add checkb.Name, checkb
            End If
    End Select
Next

...so the statsarray expander becomes:

ChecksKeys = CheckDict.Keys
For checksloop = 0 To CheckDict.Count - 1
    If CheckDict.Item(ChecksKeys(checksloop)) = True Then
        ReDim Preserve StatsArray(7, UBound(StatsArray, 2) + 1)
    End If
Next checksloop

...and so the stat's storing loop becomes:

ChecksKeys = CheckDict.Keys
For checksloop = 0 To CheckDict.Count - 1
    If CheckDict.Item(ChecksKeys(checksloop)) = True And ChecksKeys(checksloop) <> "CheckStatJackGraph" Then
            CurrentCheck = Right(ChecksKeys(checksloop), Len(ChecksKeys(checksloop)) - 9)
            StatsArray(0, StatsRowPointer) = sample
            StatsArray(1, StatsRowPointer) = CurrentCheck
            StatsArray(LoopVar + 1, StatsRowPointer) = d.Item(CurrentCheck)
            StatsRowPointer = StatsRowPointer + 1
    End If
Next checksloop

...and it allows me to much more easily do things like:

If CheckDict.Item("CheckStatJackGraph") = True Then Call CompileJackLimitsArray(Int(StatsStart))

Hope that's of use to someone.

Phil

 

[CajunCenturion]

A star for you MePenguin for taking the time to post back and share your updates. Tek-Tips is all about IT professionals working together and sharing for mutual benefit. Thank you.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein