0

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

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Anthony
  • 311
  • 1
  • 3
  • 13

1 Answers1

2

use a Dictionary object:

Option Explicit

Sub main()

    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

    Dim cell As Range
    With Worksheets("myWorksheetName")
        For Each cell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            dict(cell.Value & "_" & cell.Offset(, 1).Value) = dict(cell.Value & "_" & cell.Offset(, 1).Value) + cell.Offset(, 2).Value
        Next
    End With

    Dim key As Variant
    For Each key In dict.keys
        Debug.Print key, dict(key)
    Next
End Sub

so what you call a "string" to access each different tuple is actually the Key of the dictionary object (eg.: "Red_Square"

DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • Thank you! This is really helpful – Anthony Oct 06 '19 at 19:17
  • 1
    that line just updates the item (i.e. the area) associated with the the current key, just summing it the area value read from the cell two columns right of the current one – DisplayName Oct 06 '19 at 19:19
  • Yup it just clicked now. Thanks so much! Just realized I'd have to initialize the variables as Strings instead of Integers, and then use the values to store the running totals – Anthony Oct 06 '19 at 19:21
  • Hi, one followup, but are dictionaries non-existent on Mac OS? I'm trying to replicate this and get an "Run-Time Error ‘429’: ActiveX component can’t create object" error on the line where I create one – Anthony Oct 06 '19 at 20:05
  • https://stackoverflow.com/questions/19869266/vba-excel-dictionary-on-mac – braX Oct 07 '19 at 01:02