时间戳

首页 > 解决方案 > Excel VBA：如何使用嵌套数组

arrays - Excel VBA：如何使用嵌套数组

问题描述

实际上，我想创建一个可以使用类型的数组，并随着时间的推移拥有它。所以这意味着目标是有一个我可以调用的数组：Array(At a period = 1).Name.Variable = the expected Value. 所以我一直在设计数组看起来像：

    { Period1:{ 
               {A:{Revenue:5,ROE:5%},
                B:{Revenue:7,ROE:8%}
               }
      Period2:{ 
               {A:{Revenue:5,ROE:5%},
                B:{Revenue:7,ROE:8%}
                }
     }

我正在从一个 Excel 表中读取这些值，该表的名称 (A,B) 具有各自的收入和 ROE 值。然后，随着时间的推移，我会使用这些值对它们做一些事情。

这是我尝试过的，我得到以下编译错误：只有公共对象模块中的用户定义类型可以强制转换为变体或从变体强制转换或传递给后期绑定函数。

Option Explicit

Private Type RelationshipArray
    Revenue As Double
    ROE As Double
End Type

Public Type RelationshipProductCategory
      Period As Integer
      A as variant 
      B as variant
End Type
'
'Private this As RelationshipArray
'
'Public Property Get Revenue(ByVal value As Double) As Double
'    Revenue = this.Revenue
'End Property
'
'Public Property Get ROE(ByVal value As Double) As Double
'    ROE = this.ROE
'End Property



Function GetRelationshipValues(ByVal Period As Integer, ByRef B As Variant, ByRef A As Variant) As RelationshipProductCategory
    GetRelationshipValues.Period = Period
    GetRelationshipValues.A = A
    GetRelationshipValues.B = B

End Function


Sub RelationshipIncomeCalculation()
Dim ar(10)
Dim A As RelationshipArray
Dim B As RelationshipArray


'this table value is analogous to Range("A2") just more robust for my use
A.Revenue = TableValue("RelationShipIncomeInput", "A", "Revenue", 0, 0)
A.ROE = TableValue("RelationShipIncomeInput", "A", "ROE", 0, 0)

B.Revenue = TableValue("RelationShipIncomeInput", "B", "Revenue", 0, 0)
B.ROE = TableValue("RelationShipIncomeInput", "BUSINESS DEPOSITS", "ROE", 0, 0)
ar(0) = GetRelationshipValues(0, A,B)

标签： arraysexcelvba

解决方案

希望我的回答仍然有帮助，但是如果您已经解决了这个问题，如果您可以将其标记为答案，那就太好了

基本上，您不应该将RelationshipArrayand声明RelationshipProductCategory为Type，而是声明为Class Module（这是“公共对象模块中的用户定义类型”所指的内容）。

例如，右键单击项目并创建一个 Class Module RelationshipArray，并将这些粘贴到其中：

Public Revenue As Double
Public ROE As Double

并为RelationshipProductCategory.

要创建类模块的新实例，您应该这样做

dim v as RelationshipArray
set v = new RelationshipArray

将其设置为数组元素也是如此：

Dim ar(10) as RelationshipArray
Set ar(0) = v

因此，您的代码应如下所示：

Option Explicit

Function GetRelationshipValues(ByVal Period As Integer, ByRef B As Variant, ByRef A As Variant) As RelationshipProductCategory
    Dim rtn As RelationshipProductCategory
    Set rtn = New RelationshipProductCategory
    rtn.Period = Period
    Set rtn.A = A
    Set rtn.B = B
    Set GetRelationshipValues = rtn
End Function
Function TableValue(rel As String, prodasp As String, attr As String, v1 As Double, v2 As Double) As Double
    TableValue = Rnd
End Function

Sub RelationshipIncomeCalculation()
    Dim ar(10) as RelationshipProductCategory
    Dim A As RelationshipArray
    Dim B As RelationshipArray
    Set A = New RelationshipArray
    Set B = New RelationshipArray

    'this table value is analogous to Range("A2") just more robust for my use
    A.Revenue = TableValue("RelationShipIncomeInput", "A", "Revenue", 0, 0)
    A.ROE = TableValue("RelationShipIncomeInput", "A", "ROE", 0, 0)

    B.Revenue = TableValue("RelationShipIncomeInput", "B", "Revenue", 0, 0)
    B.ROE = TableValue("RelationShipIncomeInput", "BUSINESS DEPOSITS", "ROE", 0, 0)
    Set ar(0) = GetRelationshipValues(0, A, B)

    MsgBox "ar(0).A.Revenue = " & ar(0).A.Revenue
End Sub

我建议您更多地了解类型和类模块之间的区别。基本上，类模块在 IMO 中更有用。

推荐阅读