在VBA中以字符串作为索引添加到数组 [英] Adding to an array in VBA with strings as the index

问题描述

不确定我是否已正确标记此标签.

Not sure I've labelled this correctly.

我有一堆包含数据字符串的单元格.每个单元格都包含以下内容:

I have a bunch of cells containing strings of data. Each cell consists of something like this:

q1 = 1   | q2 = 3.2 | q3 = 5.6
q1 = 1.8 | q3 = 2.1 | q5 = 1.4

*注意:定界符是乱写的，所有文本都在单个单元格中，并带有竖线字符.

*Note: The delimiter is litteral, all that text is in a single cell, with a pipe char.

我想遍历每个单元格，使用竖线(|)分隔符将其展开(以使用PHP术语)，然后再使用=符号再次进行此操作.

I want to loop through each cell, explode (to use the PHP term) by the pipe (|) delimiter, and then do so again by the = sign.

我想为等号左侧的每个可能值创建一个数组，然后将在数组右侧找到的值添加到该数组中(不加总和，不添加追加到数组中).

I want to create an array for each possible value to the left of the equal sign, and add the value found to the right to the array (not add as in sum, add as in append to the array).

从视觉上看，我认为数组应该看起来像这样:

Visually, I think the array should look something like this:

Vars[
q1 [ 1,1.8 ],
q2 [ 3.2 ],
q3 [ 5.6,2.1]....] 

最终目标是我想获取q1，q2和q3的平均值，均值和中位数.

End goal being I'd like to get the average, mean and median for each of q1, q2 and q3.

这在VB中可行吗?我对PHP较为熟悉，但希望将其保留在Excel中.

Is this doable in VB? I'm more familiar with PHP, but would like to keep this in Excel.

谢谢.

推荐答案

这将处理任意数量的键"(q1，q2等)

This will handle an arbitrary number of "keys" (q1,q2, etc)

Sub Tester()

'needs a reference to microsoft scripting runtime
Dim d As New Scripting.dictionary

Dim c As Range
Dim arrP, arrE
Dim q, v, tmpV, tmpP, tmpArr, uB
Dim i As Long, n As Long
Dim k

    For Each c In Selection.Cells
        tmpV = Trim(c.Value)

        If InStr(tmpV, "=") > 0 Then
            arrP = Split(tmpV, "|") 'split on pipe

            For i = LBound(arrP) To UBound(arrP)
                tmpP = arrP(i)

                If InStr(tmpP, "=") > 0 Then
                    q = Trim(Split(tmpP, "=")(0))
                    v = Trim(Split(tmpP, "=")(1))

                    If IsNumeric(v) Then
                        If Not d.exists(q) Then
                            d.Add q, Array(v)
                        Else
                            tmpArr = d(q) 'get dict value into temp array
                            uB = UBound(tmpArr) + 1
                            ReDim Preserve tmpArr(0 To uB) 'extend array
                            tmpArr(uB) = v
                            d(q) = tmpArr 'put back into dict
                        End If
                    End If
                End If
            Next

        End If 'cell has at least one "="

    Next c

    'dump the dictionary to the immediate pane
    For Each k In d.keys
        Debug.Print k, Join(d(k), ",")
    Next k


End Sub

这篇关于在VBA中以字符串作为索引添加到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！