Setting variable value given string name of the variable



I'm a beginner to VBA and I have an excel file with 2 columns, Color and Shape. There are 9 different combinations that can exist, like (Red,Circle), (Red,Square), (Purple,Square) to name a few. But there are hundreds of rows, so each (Color,Shape) tuple appears more than once in the dataset.

There is a 3rd column named Area that is just the area of the shape.

I want to iterate through the dataset and calculate the total area of each (Color,Shape) combination.

I've previously defined 9 variables that are a combination of the two column names, so like "Color_Shape" is the general format, with specific examples being Red_Circle, Red_Square, and Purple_Square (all initialized as Integers)

However, I don't want to have 9 If statements on each iteration (for each combination).

If I create a String using the current row's column values for Color and Shape, then how do I access the variable whose name is the same as the string and add the "Area" value to that variable?

I've looked into CallByName but I think that only modifies properties of an object and not variables.

Here is a short snippet of what I'd like to do:

enter image description here

Continue reading...