vba中的关联数组如php [英] associative array in vba like php

问题描述

我需要您的帮助，在Microsoft Excel中使用VBA构建分析。

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

我有一个数据表与y列和n行

I have one data sheet with y columns and n lines

行号|名字|姓氏|国家|培训时间|培训状况_
1 |约翰|史密斯|美国| 1 |已完成$ $ $ $ $亨利|杜邦|法国| 1 |已完成
3 |保罗|沃克|美国| 25 |不完整的
4 |罗恩|霍华德|美国| 10 |已完成< >

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

我想像php一样使用数组，但使用VBA

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应该是11

myval should be 11

不幸的是我找不到解决方案。我已经尝试了词典，集合和数组，但没有成功。
任何想法？我将非常感谢您的帮助。

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

推荐答案

您可以将SQL查询转换为打开的工作簿中的工作表（与到任何其他工作簿）。在这种情况下，查询字符串将如下所示：

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';

这里是代码

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

在查询字符串中，具有空格的列名称应放在方括号中，以及包含数据的工作表的名称后跟 $ 。
不用说，查询无法访问数据，在对表进行了一些更改后，该数据未保存到文件中。
请注意， Excel 8.0 提供程序将无法在64位Excel版本上工作，尝试使用 Excel 12.0 提供者（第二个 strConnection 赋值）。

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).

这篇关于vba中的关联数组如php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！