Is this possible in theory? - Workaround needed working with variables

[sramsay]

I posted a few days ago looking for a solution to this but didn't get much back, however, it was a rather complex way of putting it, so I am hoping by recreating the issue in a simple scenario, I may just get some help

Sub varTest()


Dim i As Integer
Dim theSum As Integer


    i = 1
    X = Left("integer", 1) 'returns the letter 'i', the 1st variable's name
    
    Debug.Print i 'at this point, this returns the number 1
    Debug.Print X 'at this point, this returns the letter i
    
    theSum = X + i 'Essentially, I would want this to be 1+1
    
    Debug.Print theSum
    Debug.Print "The answer I need to get is 2, but it errors"


End Sub

 

[njimack]

You're adding a number and a text string and expecting a numerical result. I think you need to explain what you're trying to achieve.

 

[sramsay]

long story short, I have a variable, that will always be the name of another variable. So, i am looking for a way for it to be treated as the variable's value rather than a literal string.

 

[RoryA]

First question: why do you have a variable that is the name of another variable? Almost every time I have seen similar questions, it has been down to a convoluted way of doing things.

There are ways to do what you want (class properties, dictionaries/collections) but let's figure out first if you really need it.

 

[sramsay]

Ok, I have a user form with roughly 250 text boxes. the value of these text boxes will vary, depending on selections within 2 list boxes.


First of all the textbox values will all be empty, user adds information and this is then written to a 'data dump' on a spreadsheet.
Then if the user comes back to this part of the user form (i.e same selection within the 2 listboxes) the data will be loaded backinto the text boxes.


Now, I previosuly had a proceedure written for each text box, but as they grew and I hit 8000 lines of code, this convention was not appropriate for a small project turned large.


This is where I created a translation table along with a loop - The table holds the names of all the text boxes along with related variable names. In this instance, I loop the objects in a userform, when TypeOf object is textbox, it takes the name of the textbox and looks up the variable name from the table which defines what column this information sits in.


Hopefully this makes sense? I'm not the best at hand written explanations

 

[RoryA]

I'm not really clear on why a variable name is required in that scenario. Can't you just store the control name and the relevant value?

 

[sramsay]

When the userform is invoked, a variable is created for each column header. It passes the column header name to a function which returns the column number.
Now when looping through the controls, I can easily say that the textbox I have called tb_tenRate1 will relate to a value stored in a column called Rate 1 as tb_tenRate2 will be Rate 2 etc.
So instead of saying the value I want to retrive sits in Cells(2, 3) I can say it sits in Cells(2, colRate1) - tb_tenRate1 will always be related to colRate1 etc etc
I want to avoid having to write code that explicty defines what value to pull into each text box.


I want to loop each control.
If control is of type text box, what is its name? tb_tenRate1. Look up in the translation table what the variable name is for the column. colRate1 is returned. This is correct, however it then treats it as a string "colRate1" and not the column number. This is what I want to acheive :-/

 

[Kyle123]

Look up in the translation table what the variable name is for the column

This is the bit that's wrong. This function should return a column number, not a variable name. One cannot normally convert variable names to strings and vice versa

 

[sramsay]

This is the bit that's wrong. This function should return a column number, not a variable name. One cannot normally convert variable names to strings and vice versa

So is there no way around this? I dont want to have to make the column positions static. Users being users, will change the columns, usually by adding new ones.

 

[Kyle123]

You could search by column header as a string and return the index from the function.

Post your actual code that does this, this is all speculation without seeing what you are actually doing.