using a dictionary and array to count cell values

Question

This is an extension of this question. I want to do something similar, but I am not very familiar with dictionary objects, and the code provided in the answer is very advanced, so I am having trouble understanding it. For instance, some of the syntax is not very clear, and variable names are not very obvious/intuitive. I am creating a new question because the original problem has been solved.

I want to do exactly the same thing as in the linked question, but instead of counting cell values in column H, I want to count AOI entries (and ignore exits) per trial and block in column I, and print the number in column U.

If you could also provide an explanation of the solution to accompany your solution (so that I understand what is going on), that would be appreciated. Or at least explain what is going on in the previous solution.

Here is a link to my most up to date sample data and code.

This looks like a pretty specific solution, what are you actually trying to do? Can you post your sample data in a pic/table here? You will get a better response — Preston
– Preston, Commented Oct 20, 2016 at 8:48
Unfortunately can't see your screen print so don't completely understand the question. But on your other post, with Tim's code, can you be more specific as to which part of the code you don't understand? might be able to help with that — Zac
– Zac, Commented Oct 20, 2016 at 10:49
@Zac I've attached a link to download the file in this question. I will edit with question with a screenshot of the data as well. I don't understand the syntax of the line: 'Dim d, r As Long, k, resBT()' I get that d and r are counters and resBT() is the array, but k? I haven't seen a dim statement written like this before. Also struggling with this line: 'dBT(k) = dBT(k) + IIf(d(r, COL_ACT) <> "", 1, 0)' I don't get the parameters, and never come across the IIF argument before. — shecodes
– shecodes, Commented Oct 21, 2016 at 6:01
@tompreston after I've counted the button presses per trial per block (which was what the previous question was about) I want to exclude trials with more than one button press from further analysis, and I want to count AOI entries per trial per block. — shecodes
– shecodes, Commented Oct 21, 2016 at 6:20
First the DIM statement: VBA will accept this format of writing DIM statement. I tend to write a dim statement per variable, more for reading purpose. Read it as separate dim statements. So in this case both 'k' and resBT() are Variant types as they have no declaration. Now IIF: have a read of this article. Essentially, IIF evaluates all 3 arguments where an IF will evaluate the first argument and then evaluate either the TRUE or FALSE argument based on the result of the first argument — Zac
– Zac, Commented Oct 21, 2016 at 10:21

shecodes · Accepted Answer · 2016-10-26 05:39:04Z

I've figured it out. Here is the code:

Dim dBT As Object 'global dictionary

Sub buttonpresscount()

    'constants for column positions
    Const COL_BLOCK As Long = 1
    Const COL_TRIAL As Long = 2
    Const COL_ACT As Long = 7
    Const COL_AOI As Long = 8

    Dim rng As Range, lastrow As Long, sht As Worksheet
    Dim d, r As Long, k, resBT()

    Set sht = Worksheets("full test")
    lastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set dBT = CreateObject("scripting.dictionary")

    Set rng = sht.Range("B7:I" & lastrow)

    d = rng.Value  'get the data into an array

    ReDim resBT(1 To UBound(d), 1 To 1) 'resize the array which will
                                        '  be placed in ColT

    'get unique combinations of Block and Trial and pressedcounts for each
    For r = 1 To UBound(d, 1)
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k) = dBT(k) + IIf(d(r, COL_ACT) <> "", 1, 0)
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
    Next r

    'place array to sheet
    sht.Range("T7").Resize(UBound(resBT, 1), 1) = resBT

    'clear dictionary
    dBT.RemoveAll

'count AOI entries
 For r = 1 To UBound(d, 1)
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k) = dBT(k) + IIf(d(r, COL_AOI) = "AOI Entry", 1, 0)
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
    Next r

    'place array to sheet
    sht.Range("U7").Resize(UBound(resBT, 1), 1) = resBT


End Sub

I basically duplicated the previous code, added another constant for the relevant column and changed the relevant references to columns, and made sure to clear the dictionary inbetween counting tasks.

Collectives™ on Stack Overflow

using a dictionary and array to count cell values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related