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

QuestionBaker

Board Regular
Joined
Apr 12, 2019
Messages
106
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: 6

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Can you please post your actual code, rather some form of pseudo code.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Solution
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. :)
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top