Array Overflow - Dynamic Variable Name 1

[economistleo]

Hi, this is my first post so I apologize in advance for anything.

I've been working on this issue for more than a week now, and still haven't solved it.

What I need is to dynamically name variables, so it can be 'called' acording to string strings within the sheet. More clearly, my data structure is (two columns):

COL1 COL2
1 77.2
2 52.6
101 95.7
102 (...)
10101
10102
10103
10201
10203
(...)
(...)
1010203010202010 87.6


What I need is to create a local variable named
var_1010203010202010 = 87.6
for each one of the cells in column1 and assign to it the values in column 2 and keep it in memory. Later, I need to call these variables using the values in column1. I`ve tried to do with an array but I get the OVERFLOW error
(imagine an array with 1010203010202010 lines). Besides, it seems like a waste of memory to declare thousands of lines just to use a few. I've come up to some posts about 'concatenating variables and string as variables names' but they all had a solution within the array scope.

Thank you in advance!

Leo

 

[SkipVought]

Why would you create that many variables?

With NATIVE Excel features...
[tt]
COL1 COL2
ID Val
1 77.2
2 52.6
101 95.7
102 (...)
10101
10102
10103
10201
10203
(...)
(...)
1010203010202010 87.6
[/tt]
Using Named Ranges (Insert > Name > Create Names -- Create names in TOP row.
If, lets say that Z1 contains 1010203010202010, then in AA1
AA1: =INDEX(ID,MATCH(Z1,Val,0),1)
[tt]
You get the corresponding value.

[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

I agree with skip - sounds like excel functions will do your job. vlookup is another one very stuited to this task.

If you really want these things to be variables in vba and the hangup is the indexing, you might try using a dictionary object which takes things other than integers (in your case strings) as an index.

 

[Markus4]

Hi,

as the desired variable name is left of the desired variable value (or maybe we better should use constants) there is another Excel feature to create them:

- select only the data in columns 1 and 2, not the headings
- Insert - Name - Create names*, a dialogue box with four options appears: "Left column"* should be already ticked and used

After creating these names each value can be accessed via the names collection.

BTW: The corresponding VBA command is

Selection.CreateNames Top:=False, Left:=True,Bottom:=False, Right:=False

Regards,

Markus
_______
*This is what I think the English command names are. In German it is: Einfügen - Name - Erstellen: aus linker Spalte

 

[electricpete]

That's another creative approach, but there is a snag....the first character of name for a range in excel must be a letter or an underscore (not a digit)

 

[Markus4]

Hi,
I see Electricpete's problem, but I just wamted to point to the right direction.

- In VBA: it should be easy to add "var_" to whatever variable (or better constant) name is desired.

- Or on the spreadsheet: a helper column adapts the name column to the desired criteria. This also can be manipulated in VBA.

The thing is: instead of defining hundreds of variables (better: constants), I suggest using an intermediate: creating lots of names in Excel, then use them in code via the names collection. (One might even think of deleting all the names after they'd become VBA constants.)

Regards,
M.

 

[SkipVought]

It is not a best and accepted practice to use data values as variables. This is similar to the problems encountered with non-normalized data structures in tables, where tables grow in STRUCTURE size (ie. additional columns as data values)

This results in real coding problems.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

You could use a dictionary object

Public Sub loadDictionary()
'need reference to scripting runtime
Dim lngCounter As Long
Dim maxRows As Long
Dim myDty As Dictionary
Dim ws As Worksheet
Set ws = Worksheets("Data")

maxRows = 3000
Set myDty = New Dictionary
For lngCounter = 1 To maxRows
myDty.Add CStr(ws.Cells(lngCounter, 1)), ws.Cells(lngCounter, 2)
Next lngCounter

MsgBox myDty.Item("101020")

End Sub

 

[economistleo]

Hi everybody, I'm that I couldn't answer before.

Skip and ElectricPete: I've tried to use the .find method before (on vba), but it's just not reliable enough. This dataset i'm dealing with is actually huge, and neither these Indexes ("1010002") nor their corresponding variables (the Column2 was just an example, I have more than 100 vars for each of this indexes) are static. In technical terms, this DS is structured as a PANEL, Skip, and it's structure is indeed very complex (and it comes with these coding problems you mentioned - this is why I've brought here). I apologize for not describing it truly.


ElectricPete, Markus4 and MajP: I think your approach fits me better. Actually, the dictionary approach seems to be what I had in mind. There's just another problem. VBA won't recognize the 'Scripting.Dictionart' on the CreateObject function. Any idea why this is happening?

Anyway, I really want to thank everybody. You guys are just great, and I'm willing to contribute to this forum too! Thank you,

Leo

 

[PHV]

VBA won't recognize the 'Scripting.Dictionart' on the CreateObject function
Add a reference to to Microsoft Scripting Runtime

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I am not really sure why you need to do this or if it is a good idea, but it will work. If you are using this a lot you may want to consider building a custom class and a custom collection to make this transparent.

class myObject
property Identifier (string)
property Value (numeric)
Property Range (excel range)

so for each one of your values not only can your store the label but the actual location.

So you could pull out of the collection or dictionary item
"101020", get its value, and return the range.

In your custom collection you can put an initialize method to pass in the range of the two columns and it will load the collection with the values.



PS. I even put a comment in the demo code to add a reference.

 

[economistleo]

Alright, I got it now! It works perfectly. I used MajP's approach (created a dictionary), and it solved my problem.
Thank you,

Leo