0

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.

And here is a screenshot of my data

Community
  • 1
  • 1
shecodes
  • 111
  • 9
  • 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 Oct 20 '16 at 08: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 Oct 20 '16 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 Oct 21 '16 at 06: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 Oct 21 '16 at 06: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](https://msdn.microsoft.com/en-us/library/27ydhh0d(v=vs.90).aspx). 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 Oct 21 '16 at 10:21
  • Thanks for that! I also managed to find an article on Excel Macro Mastery about dictionaries that was helpful. And I've managed to rewrite the code to do what I want, and I've answered my own question. – shecodes Oct 26 '16 at 05:54

1 Answers1

0

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.

shecodes
  • 111
  • 9