Indirect referencing variable names

[alpder]

I have a spreadsheet that has the names of variables in column 1 and the values in column 2. I need to use the values of those variables in further calculations usinf Visual Basic. If for example cell A1="volume" and cell A2="25.38", how can I read the value of 25.38 into a variable called "volume".

I have simplified this example. In reality I have approximately 100 variables that I need to retrieve in this way.

I hope you can help me.

 

[DaleWatson123321]

From your description, it seems like you might want to add the values in your second field that correspond to the variable in the first field.

If this is the case, an option is to use Excel's database formulas.

If your variables are constants, then you could set up a summary with database formulas that would add the values in the second field.

If your variables are not constant, then you could have a routine that would extract a UNIQUE set of the variables, and then copy each variable to a common cell referenced by a database formula. The result would then be copied to a summary of totals by each variable.

If this seems reasonable, but you don't have any experience in using Excel's database formulas, I can help. The best way would be for you to email me your worksheet, and I'll insert the necessary VBA code formulas and criteria, and return the file. If you happend to have sensitive data, replace it with fictitious data.

I hope this can help. A "bonus" is that you will be given an "insight" into one of Excel's MOST POWERFUL yet seldom used features.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[RobBroekhuis]

Dale's approach sounds powerful, and out of my range of knowledge. Let me suggest a different approach, which might also work depending on exactly how the variables would be used.
I'm thinking it might make sense to assign each "variable" a range name. This could be done as the variables are entered, but if it needs to be dynamic, a simple piece of code to create/manipulate the ThisWorkbook.Names object would be run to set things up. Once this is done, you could reference the variable by range("volume&quot, etc.
Rob

 

[VBslammer]

You can store the values in a 2-dimensional global array:

Public varPairs() As Variant

Function LoadVariables()
  Dim c As Object
  Dim varVal As Variant
  Dim i As Integer
  
  ' size the array to the number of cells in the range
  ReDim varPairs(ActiveSheet.Range("VarNames").Cells.Count, 1)
  
  ' loop through the range and use VLookup to find the values.
  For Each c In ActiveSheet.Range("VarNames").Cells
  
    varVal = Application.WorksheetFunction.VLookup( _
                  c.Value, Range("BothColumns"), 2, False)
                  
    varPairs(i, 0) = c.Value     ' Add the variable name.
    varPairs(i, 1) = varVal      ' Add the lookup value.
    
    ' increment counter.
    i = i + 1
    
  Next c
  
  ' print out the values to test.
  For i = 0 To UBound(varPairs) - 1
    Debug.Print varPairs(i, 0), varPairs(i, 1)
  Next i
    
End Function

VBSlammer

Unemployed in Houston, Texas