How to update value of an element in array in dictionary?

QuestionBaker

Board Regular
Joined
Apr 12, 2019
Messages
99
Office Version
  1. 365
Platform
  1. Windows
I have a data similar to the following
1632301595933.png

I want sum of Data1, Data2, Data3 for Tag 1, Tag2 and so on
I am using a dictionary with Tags as keys and 3 len array as value whose each element is initialized to 0
I am unable to update values in the array
My code looks something like this (not the actual code)
VBA Code:
dim dict as new dictonary
dim arr(1 to 3) as double
arr(1) = 0
arr(2) = 0
arr(3) = 0
for each non empty cell in column A from A2
    if not dict.exist(cell.value) then
        dict(cell.value) = arr
    end if
    dict(cell.value)(1) = dict(cell.value)(1) + corresponding cell in B Col
    dict(cell.value)(2) = dict(cell.value)(2) + corresponding cell in C Col
    dict(cell.value)(3) = dict(cell.value)(3) + corresponding cell in D Col
next cell
But the reassignment does not work
If I check the immediate window, it outputs False
 

Attachments

  • 1632301077059.png
    1632301077059.png
    5 KB · Views: 5

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,994
Office Version
  1. 365
Platform
  1. Windows
Can you please post your actual code, rather some form of pseudo code.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,845
Office Version
  1. 365
Platform
  1. Windows
According to this, you need to read the array from the dictionary into a variable, alter the array item(s) then replace the whole dictionary item with the changed array.

However, my questions are:
  1. Why bother with putting in the array of zeros in to the dictionary the first place. Why not put the cell values in an array and then into the dictionary directly?

  2. Actually, you said you wanted the sum of the values, so why bother putting the individual values into the dictionary at all? Why not just put the sum in?
 

QuestionBaker

Board Regular
Joined
Apr 12, 2019
Messages
99
Office Version
  1. 365
Platform
  1. Windows
I found a workaround to using arrays.
Instead of using an array, I am simply using those many keys in my dict
So, for Tag1, I have 3 keys
Tag1D1, Tag1D2, Tag1D3
and I can update its values without any problem.
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,845
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Instead of using an array, I am simply using those many keys in my dict
So, for Tag1, I have 3 keys
Tag1D1, Tag1D2, Tag1D3
and I can update its values without any problem.
Sure, but I still don't understand why you are putting them in in a dictionary at all if you want to sum them as you initially stated in post 1?

But if you are happy, so am I. :)
 

QuestionBaker

Board Regular
Joined
Apr 12, 2019
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Sure, but I still don't understand why you are putting them in in a dictionary at all if you want to sum them as you initially stated in post 1?

But if you are happy, so am I. :)
Even though all I need is the to sum them, it isn't the complete picture.

There are (multiple) columns to the right which act as a qualifier, which are not there in my example.
Instead of looping through list multiple times for each tag, I am running through it once and if the IF conditions are true, then adding to the dict value.
so, when I am entering my data into my final table, I can simply pass dict values and get the desired value.
And I am not sure if my table will have the same tags or not, so I thought dictionary is a good solution.
I had a limited exposure to dictionaries in python, and that inspired me to use the same here.

I would be happy to know about your thoughts on this
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,845
Office Version
  1. 365
Platform
  1. Windows
I would be happy to know about your thoughts on this
Without knowing the detail of what is in your sheet and what you are doing with the values to the right I can't offer anything constructive. (Even knowing those things I might not either. :unsure:)

In any case, if you have something that is working the way you want, no need to change anything. :)
 

QuestionBaker

Board Regular
Joined
Apr 12, 2019
Messages
99
Office Version
  1. 365
Platform
  1. Windows
The only reason why I didn't give info to the right cause I wanted to keep question short and to the point.
The code is used to make a report and multiple charts and tables are generated, it would be next to impossible to understand what is going in the code, so I shared only the part that was bugging me.
 

Forum statistics

Threads
1,148,244
Messages
5,745,596
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top