associative array in vba like php

Question

I need your help for building analytics in Microsoft Excel using VBA.

I have one data sheet with y columns and n lines

Line Number  Firstname  Lastname  Country  Training Hours  Training Status
1            John       Smith     USA      1               Completed
2            Henri      Dupont    France   1               Completed
3            Paul       Walker    USA      25              Incomplete
4            Ron        Howard    USA      10              Completed

And I would like to use array like in php but using VBA

For i = 1 To nblines
    If(array[i]["Country"] = "USA" And array[i]["Training Status"] = "Completed") Then
        myval = myval + array[i]["Training Hours"]
    End If
Next

myval should be 11

Unfortunately I don't find THE solution. I've tried dictionnary, collection and array but without success. Any idea?

Have you considered putting this data in a table and pointing a pivot table at it? Or using a SUMIFS formula? Either could address the added information that you gave in Scott Craner's answer. No VBA needed and more flexible. — Doug Glancy
– Doug Glancy, Commented Oct 28, 2015 at 18:25

omegastripes · Accepted Answer · 2015-12-15 20:16:01Z

You can make the SQL query to the worksheet in the opened workbook (the same way as to any other workbook). In this case the query string will be as follows:

SELECT SUM([Training Hours]) AS Myval FROM [data sheet$] WHERE Country = 'USA' AND [Training Status] = 'Completed';

Here is the code

Sub TestSQLRequest()

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Select Case LCase(Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".")))
        Case ".xls"
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 8.0;HDR=YES;"";"
        Case ".xlsm"
            strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"
    End Select

    With CreateObject("ADODB.Connection")
        .Open strConnection
        With .Execute("SELECT SUM([Training Hours]) AS Myval FROM [data sheet$] WHERE Country = 'USA' AND [Training Status] = 'Completed';")
            Myval = .Fields("Myval")
        End With
        .Close
    End With

    MsgBox Myval

End Sub

Within the query string, the column names with spaces should be put into square brackets, as well as the name of the worksheet containing data followed by $. It goes without saying that the query can't access to the data, which wasn't saved to the file after some changes have been made to the sheet. Note that Excel 8.0 provider won't work on 64-bit Excel version, try to use Excel 12.0 provider instead (the second strConnection assignment).

Thanks a lot omegastripes, it works perfectly. It is exactly what I need.

Scott Craner · Accepted Answer · 2015-10-28 18:34:25Z

0

So I set my sheet up like this:

Then using the following code I loaded the array and cycled through each line in the array. I added the Hours as they met the condition and displayed the result in the immediate window.

Edit: As far as I know excel does not use column names. So the only way I could come up with is this, Where we add the first row into the array then cycle through them looking for the names in the first row of the array to get the position of that column then use the variable to find again.

A quicker method would likely be an advanced filter with subtotals. Filter on a different page than the data. Or, the best answer would be using a pivot table.

Sub nubuk()

Dim arr() As Variant
Dim smTtl As Double
Dim i&
Dim lastrow As Long
Dim lastcolumn
Dim t&
Dim cntry&, stus&, hrs&

With ActiveSheet
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    lastcolumn = Cells(1, .Columns.Count).End(xlToLeft).Column
    Debug.Print lastcolumn
    arr = .Range(.Cells(1, 1), .Cells(lastrow, lastcolumn)).Value
End With
For t = 1 To lastcolumn
    Select Case LCase(arr(1, t))
        Case "country"
            cntry = t
        Case "training status"
            stus = t
        Case "training hours"
            hrs = t
        Case Else
    End Select
Next t
For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i, cntry) = "USA" And arr(i, stus) = "Completed" Then
        smTtl = smTtl + arr(i, hrs)
    End If
Next

Debug.Print smTtl

End Sub

edited Oct 28, 2015 at 18:34

answered Oct 28, 2015 at 17:57

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

4 Comments

nubuk29 Over a year ago

nubuk29 Over a year ago

Nice approach Scott. Thanks a lot for your help. I will use an array with the column name used for filter and make a loop on it to get the column number.

MacroMarc Over a year ago

FYI as omegastripes said, you can make the data into an Excel table and then use column names directly in your code to access them.

nubuk29 Over a year ago

Hi omegastripes and MacroMarc, I will use an xls file and I want to store the filter used with values, the result needed : a sum, an average, a count in a cell to calculate dynamically the result. For example : I want to put in the cell G1 the result and the formula in the cell H1 to be re-editing later filter:country;equal;USA filter:training status;equal;Completed result:sum;training hours That's why I can't use a pivot table. Maybe MacroMarc you can provide me an example.

Collectives™ on Stack Overflow

associative array in vba like php

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related