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

 

[TheAceMan1]

How are ya MePenguin . . . . .

It would be sweet if we could [blue]reference a variable thru a string[/blue], but since Access 1.0 I've never heard of, or found, such a method.

The closest I can get will require you to reference the variables form the form (variables are now textboxes and code will be greatly reduced). [green]If the form Idea doesn't appeeal to you, no sense reading anything farther.[/green]
[ol][li]With the form [blue]FMCRAdvanced[/blue] in design view and in the [blue]Tag[/blue] property of the [blue]CheckStat*[/blue] textboxes, enter a question mark [purple]?[/purple].[/li]
[li]In the [blue]header[/blue] or [blue]footer[/blue] of the form, insert an [blue]unbound textbox[/blue] for each variable you have in code (total of 7) and [blue]set the following properties[/blue] for each:
[ol a][blue][li]Visible [purple]Yes[/purple][/li]
[li]Name [purple]VariableNameInCode [/purple] (post appended with var)[/li][/blue][/ol][/li]
[li]In a module in the modules window, copy/paste the following routine:

[blue]Public Sub CheckStat()
   Dim frm As Form, Ctl As Control
   
   Set frm = FormsFMCRAdvanced
   
   For Each Ctl In frm.Controls
      If Ctl.Tag = "?" Then frm(Ctl.Name & "var") = Ctl
   Next
     
   Set frm = Nothing

End Sub[/blue]

[/li][/ol]
[purple]Thats it! . . .[/purple]

See Ya! . . . . . .

 

[MePenguin]

Hi TheAceMan1, thanks for the suggestion!

A nice solution, and I'll try that for setting the variables.
Any ideas on an alternative for reading them - I'd like to loop the variables set above in another module (where referencing the form isn't an option).

At one point I expand an array that stores stats results by one row if the (check box set) variable is true:

If CheckStatMCRJackMeanvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)
If CheckStatPseudoMeanvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)
If CheckStatSEJackvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)
If CheckStatVJackvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)
If CheckStatBJackvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)
If CheckStatBRedJackvar = True Then ReDim Preserve StatsArray(9, UBound(StatsArray, 2) + 1)

(I suppose I could use your routine to store the true/falses in an array, and instead of the above loop the array and redim if true... comments?)

And then later in the same sub I store data at positions in the array defined by a row counter and a column loop, if the appropriate checkbox variable is true... example below for the first two variables:

    If CheckStatMCRJackMeanvar = True Then
        StatsArray(0, StatsRowPointer) = sample
        StatsArray(1, StatsRowPointer) = NSpec
        StatsArray(2, StatsRowPointer) = OverMean
        StatsArray(3, StatsRowPointer) = "MCRJackMean"
        StatsArray(LoopVar + 3, StatsRowPointer) = MCRJackMean
        StatsRowPointer = StatsRowPointer + 1
    End If
    If CheckStatPseudoMeanvar = True Then
        StatsArray(0, StatsRowPointer) = sample
        StatsArray(1, StatsRowPointer) = NSpec
        StatsArray(2, StatsRowPointer) = OverMean
        StatsArray(3, StatsRowPointer) = "PseudoMean"
        StatsArray(LoopVar + 3, StatsRowPointer) = PseudoMean
        StatsRowPointer = StatsRowPointer + 1
    End If

The items in "" are the names of the statistics that are stored IF that particular variable is true... I think the array method might work here too... need more coffee... and any advice welcome!

Thanks,

Phil

 

[TheAceMan1]

[blue]I'd like to loop the variables set above in another module ([purple]where referencing the form isn't an option[/purple]).[/blue]

Not sure about that at all . . . I Query!

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

If you don't mind [purple](and forgetting anything about arrays)[/purple], goback to when you were brainstorming and explain what your doing here . . . . the results you seek & why [purple](and forgetting anything about arrays).[/purple]

See Ya! . . . . . .

 

[MePenguin]

OK, you asked for it
But I don't mind at all - nothing better than trying to explain something to help understand it yourself!

This could get babbly...

The background:

I'm working on software for reconstructing temperatures from fossil beetles - you can download the app from

http://www.bugs2000.org/bugsmcr/bugsmcrbeta.html

if you want to see it (it's an MDE, so let me know if you have trouble getting at the code). The method is called Mutual Climatic Range - hence MCR all over the code.

The temperature data, and a species index are stored in an mdb, the user's data is imported as Excel worksheets in the format:

CODE,Species Name,Sample1,Sample2,Sample3,etc...
1.001007,Cicindela campestris L.,2,1,0
1.00401,Carabus problematicus Hbst.,0,0,4
1.008001,Pelophila borealis (Payk.),3,0,0
etc...

(Where code is the unique id for beetle species, and the numbers show the frequency of each beetle species in each sample)

For each sample, the species present are compared with a table of species-with-climate-data (TMCRNames), and the MCR-species-list passed to a routine to calculate a temperature reconstruction. Results are eventually output in a new Excel file, example:

http://www.bugs2000.org/bugsmcr/software/images/results_glan.gif.

The problem:

What I'm trying to do now is an enhancement to the program that calculates additional statistics using a method called [purple]Jackknifing[/purple] - aka "leave-one-out analysis" - a resampling statistical method.

This recalculates the temperature reconstruction for every combination of the species list with one species removed, and basically goes like this (forgetting about arrays...):

Import Excel file

For SamplesLoop = 1 to NumberOfSamples 'Loop through samples in imported file

	Get FullSpeciesList for Sample(SamplesLoop)
	'temperature reconstruction for all species in sample:
	Call CalculateMCR for FullSpeciesList
	
	[b]'Jackknifing
	'Loop through species in selected sample
	For SpeciesLoop = 1 to NumberOfSpecies 
		Remove Species(SpeciesLoop) from FullSpeciesList
		Call CalculateMCR for ReducedSpeciesList
	Next SpeciesLoop[/b]
	
	'This is the other module in my reply above
	[i]Calculate summary statistics for Jackknifed results[/i]

Next SampleLoop

Export results to Excel

The jackknife results file contains two worksheets:
1) Temperature reconstructions for each reduced species list
2) Summary statistics calculated (in Access) on (1), the user selects which ones are exported with checkboxes.

Example of (1) for 2 samples (commas inserted to make it fit here):

Sample,TMaxLo,TMaxHi,TMinLo,TMinHi,TRangeLo,TRangeHi,NSPEC,Overlap,NSpecRemoved,RemovedCODE1
GL138	7	11	-31	-10	20	38	6	100	0	
GL138	7	11	-31	-10	20	39	5	100	1	1.0290142
GL138	7	11	-31	-10	20	38	5	100	1	1.0290245
GL138	6	11	-31	-10	20	38	5	100	1	1.065031
GL138	7	13	-31	-10	20	38	5	100	1	1.0650621
GL138	7	11	-31	-4	15	38	5	100	1	7.005023
GL138	7	11	-31	-10	19	38	5	100	1	23.038001
GL110	9	11	-26	-10	20	36	6	100	0	
GL110	9	11	-26	-10	20	36	5	100	1	1.009002
GL110	7	11	-31	-10	19	39	5	100	1	1.012002
GL110	9	11	-26	-10	20	36	5	100	1	1.012003
GL110	9	11	-26	-10	20	36	5	100	1	1.0290245
GL110	9	14	-28	-10	20	39	5	100	1	1.0650621
GL110	9	11	-26	-4	14	36	5	100	1	7.005023

Example of (2) for 2 samples:

Sample,NSPEC,MeanOverlap,Stat,TMaxLo,TMaxHi,TMinLo,TMinHi,TRangeLo,TRangeHi
GL138	6.00	100.00	MCRStd	7.00	11.00	-31.00	-10.00	20.00	38.00
GL138	6.00	100.00	MCRJackMean	6.83	11.33	-31.00	-9.00	19.00	38.17
GL138	6.00	100.00	PseudoMean	7.83	9.33	-31.00	-15.00	25.00	37.17
GL138	6.00	100.00	SEJack	0.83	1.67	0.00	5.00	4.08	0.83
GL138	6.00	100.00	VJack	0.69	2.78	0.00	25.00	16.67	0.69
GL138	6.00	100.00	BJack	-0.83	1.67	0.00	5.00	-5.00	0.83
GL138	6.00	100.00	BRedJack	7.83	9.33	-31.00	-15.00	25.00	37.17
GL110	6.00	100.00	MCRStd	9.00	11.00	-26.00	-10.00	20.00	36.00
GL110	6.00	100.00	MCRJackMean	8.67	11.50	-27.17	-9.00	18.83	37.00
GL110	6.00	100.00	PseudoMean	10.67	8.50	-20.17	-15.00	25.83	31.00
GL110	6.00	100.00	SEJack	1.67	2.50	4.17	5.00	4.90	3.16
GL110	6.00	100.00	VJack	2.78	6.25	17.36	25.00	24.03	10.00
GL110	6.00	100.00	BJack	-1.67	2.50	-5.83	5.00	-5.83	5.00
GL110	6.00	100.00	BRedJack	10.67	8.50	-20.17	-15.00	25.83	31.00

I originally did a version of this using SQL's constructed on the fly, and multiply reused temporary tables, but encountered serious problems with the size of the Access file expanding during calculation - at one point I had an mdb increasing from 15Mb to 1Gb! The user's Excel files can easily contain 50 samples and 300 species... ie. 15000 iterations of the temperature calculations, plus the stats. Which is why I switched to arrays (difficult to forget about them, sorry!)

Any wiser? Or Have I just babbled incoherently - it wouldn't be the first time since I started working on this!

It's not really a complex problem, either mathematically or programatically I think - and as you say, I could be making it more complicated than it needs to be, my coding skills are not as good as I'd like.

If you want to see the jackknife code I can upload it onto the bugs2000 website.

Help much appreciated!

Phil

 

[Tranman]

Phil,
Fascinating subject (if you did a search, I'm sure you found where other people have attempted to deal with the same 'variable names in variables' problem.)

I have not tried this, and don't have time this morning, but I've always thought that I would probably instantiate a global collection object, and either load in the textboxes in question (don't think this would work because they would only exist as addresses in the collection, and would probably not be valid when the form is closed), or load in their names as indexes and their tags as values (assuming you use the controls' tags as TheAceMan1 suggested). I'm pretty sure the second techniqe would work, AND it would allow you to iterate through the collection object with a For Each loop.

Just a thought.

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

 

[TheAceMan1]

MePenguin . . . . .

Very interesting subject and reading . . . along with a good lookin DB . . . . [blue]nice work![/blue]

I pretty much understand the resolution your looking for, but after reading your post several times, I constantly kept seeing a [purple]performance/memory issue in need of correction before anything else (which is what I believe your after)! Wether thru code or any other way, gotta make this hurdle first. Until this issue is resolved your performance will always be low . . .

Your necessary method generates more than enough data to use up most (if not all) your memory, and I'm sure your aware that at a certain point [blue]windows starts swaping out to the swapfile.[/blue] When this happens, [purple]performance for you dies[/purple], as code now has to wait for swapping to complete, or, . . . [purple]your code only runs as fast as the swapfile allows.[/purple] I can just see, a steady harddrive access LED on your machine . . . [blue]not even a flicker![/blue] I feel sorry for the user who has say . . . only 128MB ram.

[blue]Swapping, a thousand times more than the code, is the key here. Anything you do has to result in cutting memory usage back enough that swapping isn't triggered, or she'll never fly . . .[/blue]

Maybe you can work with your data in smaller packets and build form there . . .

See Ya! . . . . . .

 

[MePenguin]

Hi Tranman,

if you did a search, I'm sure you found where other people have attempted to deal with the same 'variable names in variables' problem.

Yep, but a bit low on VBA solutions - at least that I understand.

Could you explain the " I would probably instantiate a global collection object ... load in their names as indexes and their tags as values " a bit more? Sounds interesting.

I've tried putting the following into a Class module (ClassVar):

Option Compare Database
Option Explicit

Public CheckStatVJackvar, CheckStatBJackvar, CheckStatMCRJackMeanvar As Boolean
Public CheckStatPseudoMeanvar, CheckStatSEJackvar, CheckStatBRedJackvar As Boolean

Which allows me to refer to the items as members of the class... but I'm not sure how to procede - my object referencing skills let me down... is this the same a global collection?
Do I need a additional public declaration outside of the ClassVar mod to declare them as variables as well (which is what I have now)...

Thanks,

Phil

 

[MePenguin]

Hi AceMan, you're right.

However, those of my users that will be running this part of the program will be people used to leaving their PC on over the weekend to finish calculations (and praying). Of course, any performance improvement is desirable, but my main concern is to get this working - and then tidy it up... but then again with good programming the one comes from the other, I suppose...

Maybe you can work with your data in smaller packets and build form there . . .

I've considered this, and have been sketching out a solution.
In the current version the MCRvalues for each iteration of calculations for each sample are stored in ResultsArray(9,n). The values in StatsArray(9,m) are calculated per sample from the values stored in ResultsArray().

Since the Stats routine is called at the end of the calculations for each individual sample, there is no code related reason (see below) that ResultsArray() shouldn't just contain the iterations for each sample.

I'll stop before I ramble on too much - but here's my concern over this:
The entire ResultsArray() has to be dumped to Excel. Currently this is done once, at the end of the calculations with

...
Set ObjExcel = GetObject(filename)
Set ExcelApp = ObjExcel.Parent
...
With ExcelApp
...
.Cells(3, 1).Resize(UBound(ResultsArray, 2) + 1, UBound(ResultsArray, 1) + 1).Value = .Transpose(ResultsArray)

[red]If ResultsArray() was to be reset after each sample, then the transfer to Excel would have to occur once per sample - which I imagine would be more of a performance loss than the memory hungry code? Correct me if I'm wrong, please![/red]

I'm also working on an enhancement of this whole Jackknifing thing that recursively increases the number of species removed for each recalculation set - the number of iterations can be worked out using this:

http://mathforum.org/~ken/pascal.html

- now THAT uses swapdisk!

Still want to loop those variables (or equivalent) though...

. . . along with a good lookin DB . . . . nice work!

Thanks!
(The previous version had purple swirly fractal plasmas on it... went for the brushed metal to reduce headaches)

 

[TheAceMan1]

[blue] . . . but my main concern is to get this working . . .[/blue]

Your post origination queried a more efficient routine.
Whats not working?

[blue]If you want to see the jackknife code I can upload it onto the bugs2000 website.[/blue]

Please do & notify . . . .

See Ya! . . . . . .

 

[Tranman]

MePenguin,
Sorry I've been so slow responding. Busy as heck. Anyway, here is a way to store off the values of your controls (I used textboxes, but it could be checkboxes or whatever), and identify them by where they came from.

The collection is declared (not in the form's class module) as:

Global col as New Collection

When you click the button, each textbox's value is stored in a collection item, and the item is indexed with the name of the control (indexes are strings here).

Here's the code:

Private Sub Command9_Click()
Dim tb As Control
Dim var As Variant

On Error GoTo Err_Command9_Click

For Each tb In Me.Controls
  Select Case tb.ControlType
    Case acTextBox
      tb.SetFocus
      col.Add item:=tb.Text, Key:=tb.Name
  End Select
Next

For Each var In col
  Debug.Print var
Next

Exit_Command9_Click:
    Exit Sub

Err_Command9_Click:
    MsgBox Err.Description
    Resume Exit_Command9_Click
    
End Sub

As you can see, you can iterate through the collection object with a For Each loop.
For Each var In col
mystring = var
Next

You can also reference any member of the collection by its ordinal position in the collection (1 relative):
mystring = col(1)

Or you can reference the item by its key value (what I think you're talking about):
mystring = col("<textboxname>")

To empty out the collection, just delete item number 1 over and over:
for ndx = 1 to col.Count
col.Remove(1) 'THAT'S NOT A TYPO
next

Anyway, that is how I've thought I'd solve the problem of storing variable names in variables. It's not elegant, but it seems like it would do what you want. Of course, you can play with the key value (var) etc.

I also once built a string out of the concatenated value and name of a textbox, and added that to a collection...

Hope that helps.

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

 

[PHV]

Tranman, isn't a Scripting.Dictionary object much efficient than a VBA.Collection ?

 

[TheAceMan1]

[blue]I'm also working on an enhancement of this whole Jackknifing thing that [purple]recursively increases the number of species removed for each recalculation set[/purple] - the number of iterations can be worked out using this:

http://mathforum.org/~ken/pascal.html

- [purple]now THAT uses swapdisk![/purple][/blue]

I reread this tread several times and decided, if I were in your shoes, [purple]eliminating recursion would be my first order of business.[/purple] I havn't seen the routines yet but you should be able to do it. [blue]This will put an end to that exponential memory explosion thats killing you.[/blue]

As an example, take a typical recursive Factorial function like:

[blue]Function Factorial(n)
   Dim x As Long
   
   [purple][b]x = n[/b][/purple]
   
   If n <= 1 Then
      Factorial = 1
   Else
      Factorial = n * Factorial(n - 1)
   End If
End Function[/blue]

The focus here is [purple]x = n[/purple], which takes up memory on each recursion. Only you have quite a few more variables. Compare this to the following non-recursive routine:

[blue]Public Sub Factorial(n)
   Dim x As Long, y As Long, Ans As Long
   
   [purple][b]x = n[/b][/purple]
   Ans = n
   
   For y = n To 2 Step -1
      Ans = Ans * (y - 1)
   Next
   
   Factorial = Ans
   
End Sub[/blue]

Here [purple]x = n[/purple] occurs once and only once! I believe you see my point . . .

[purple]BTW . . . Pascal's Triangle! . . . Wow! . . .for someone like me, it was a joy to see![/purple]

See Ya! . . . . . .

 

[MePenguin]

Hi peoples,

[blue]Tranman[/blue]
- I'll get to work using your example, and let you know how it goes. Looks workable, and thank's for your time.

[blue]Aceman[/blue]

Your post origination queried a more efficient routine. Whats not working?

You're right. My brain?
It works, but I need to work out which/how/why statistics to output, and how to display them (but that's another story).

[red]Jackknifing code:[/red] It occurs to me that I don't want to post a link to the entire app until it's published - can you send me an email at phil.buckland@arke.umu.se and I'll email the link? I'll tell you which mods are relevant in the email. Alternatively, I can extract the relevant code bits into a new mdb and link to that - what would be most helpful? (or rather what would be best to help you be helpful to me - this is all very much appreciated!)

[green](Sorry about the paranoia, but I've had previous experience of scientific plagarism, and this application of the method is something new. I have no problem sharing the program code though, especially considering the amount of help I've had with it! The entire db will be freely available after publication.)[/green]

[red]Recursion:[/red] I'm not sure that what I was doing was recursive programming - rather a recursive statistical method, in that it repeats the same method whilst changing the number of data items involved (resampling, in statistics). Bad language on my part, sorry! Here's the outline (at the danger of becoming side tracked):

Import Excel file

For SamplesLoop = 1 to NumberOfSamples

    Get FullSpeciesList for Sample(SamplesLoop)
    'temperature reconstruction for all species in sample:
    Call CalculateMCR for FullSpeciesList
    
    'Recursive incremental Jackknifing
    For RemovalsLoop = 1 to NumberOfSpeciesInSample-1
        'Loop through species in selected sample
        For SpeciesLoop = 1 to NumberOfSpecies STEP RemovalsLoop
            N = RemovalsLoop
            Remove N SPECIES FROM from FullSpeciesList
            Call CalculateMCR for ReducedSpeciesList
        Next SpeciesLoop
    Next RemovalsLoop

    Calculate summary statistics for Jackknifed results
Next SampleLoop

Export results to Excel

Somewhat simplified, but I think illustrates the idea that MCR is recalculated with increasing numbers of species removed. (I'm avoiding looking at the code I wrote for the latter to see if it was recursive in the programming sense, since it's a mess, and really suffered from the growing mdb problem.) I'll build a new version based on the main jackknifing routine.

I feel I'm straying from the original post here - I'll try the collection idea, and get back to you all.

Thanks!

[fuchsia]PS. Funny thing with Pascal's Triangle - I spent a while working out how many iterations would be needed etc... and er..."invented" Pascal's Triangle - only to find he'd already done it 400 years ago![/fuchsia]

 

[Tranman]

PHV,
Hmmmm Scripting.Dictionary Yeah, maybe... I've used both, and like some things about both.

The text keys in col objects can be a joy OR a challenge. The possible use of numeric keys in S.D can also be a joy OR a challenge. Cols throw good errors. S.D errors can be cryptic. S.D lets you empty the whole thing in one operation while you have to iterate with cols. Cols can be added to in relative position.

Who knows? I appreciate the fact that you brought up S.D because it's another tool to consider. I have no idea which would be more efficient from a machine point of view. My guess is that it would probably be a wash because the two objects appear to be so similar in methods and properties.

In reality, you could instantiate a stand-alone recordset object, and accomplish the same thing.

Thank you for the incisive comment.

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

 

[CajunCenturion]

MePenguin - Fascinating challenge.

First off, for the case statement in the original post, the Forms collection can be addressed directly by control name, so as long as your naming conventions are complete, you should be able to do the following:

For i = 0 To Forms!FMCRAdvanced.Count - 1
   If Left(Forms!FMCRAdvanced(i).Name, 9) = CheckName Then
      BoxName = Forms!FMCRAdvanced(i).Name
      Forms.Controls(BoxName) = Forms!FMCRAdvanced(i)
   End If
Next i

-------------------------------
As already said, VBA does not support indirect addressing, but you can build your own symbol table using the Dictionary Object so that any entry in the table can be referenced by its name. In other words, instead of declaring a new variable (VarName), you would add an entry to the Dictionary with the key "VarName". This allows you to simulate indirect addressing by using a variable as the dictionary key. The following rather standard code block

Dim VarName as Integer
Dim VarName2 as Integer

VarName = 1
ThisVal = VarName2

would be replaced with something like the following:

ThisVar = "VarName"
DictionaryObject(ThisVar) = 1
ThisVar = "VarName2"
ThisVal = DictionaryObject(ThisVar)

I haven't been in this forum for a while, but I know there are numerous threads about using the Dictionary Object. I prefer the Dictionary Object to a Collection because of the update problem with Collections.
-------------------------------
Having read throught thread, it doesn't look like the Jackknifing code is recursive, although it is iterative. This does look like a good application for a collection object as you can build a collection of your sample sets and remove items from the collection as you iterate through the calculation loop. I'm going to give this a little more thought.

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]

Tranman,

There's a reasonable chance I'm being stupid here, but how do I READ the key property of the collection as I loop through them ordinally?

For Each var In col
	Debug.Print var
	debug.print var.key... or key(var) type of thing?
Next

I've stored the names of my checkbox's and their respective values in the collection as you suggested:

CheckName = "CheckStat"

For Each tb In Me.Controls
    Select Case tb.ControlType
        Case acCheckBox
            If Left(tb.Name, 9) = CheckName Then
                tb.SetFocus
                CheckStatus = tb
	    	    VarName = tb.Name
                col.Add Item:=CheckStatus, Key:=VarName
            End If
    End Select
Next

But the checkbox values (item) are of limited use to me without knowing the respective name (key) - i.e. I would like to know exactly which checkboxes are true/false.
Then I could do some:

For Each var In col
IF VAR=TRUE THEN
	DO GENERAL STUFF (redim array, store general data in (way) above code)
	IF COL.NAME="CHECKSTATJACKGRAPH" THEN
		DO SPECIFIC STUFF (store specific data)
	ELSEIF COL.NAME="CHECKSTATJACKGRAPH" THEN
		DO OTHER SPECIFIC STUFF
	END IF
END IF
next

Type of things...

Thanks,

Phil

PS. I thought Satan worked on Dell's support hotline?

 

[CajunCenturion]

==> There's a reasonable chance I'm being stupid here, but how do I READ the key property of the collection as I loop through them ordinally?

You can't. That is another advantage to using the Dictionary Object. The Dictionary Object has a method called "Keys" which returns a variant array containing all the keys.

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]

Thanks CajunCenturian, that info will save me some time and confusion!

 

[Tranman]

MePenguin,
Or maybe I'm being stupid...

I don't think you can read the key. You have to know ahead of time which one you want to access. That's why I added the comment:

I also once built a string out of the concatenated value and name of a textbox, and added that to a collection...

What I was getting at was that you might have to use the name of the control as a key, and also concatenate it with the value you're storing for when you iterate through the collection. Something like T,myControl1 F,myControl6... whatever. 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...

There are two main things that make collections/ dictionaries useful--they can store objects of different types together and you can access those objects via their key values. (You know which key you're looking for, and use it to refer to the item that contains the data you want--a sort of indirect addressing.)

Does that make sense? Sorry I was so oblique.

Tranman

P.S. From what I could tell, based upon the > 16 hours I spent there before solving my own problem, NOBODY works on Dell's support hotline.

"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