时间戳

首页 > 解决方案 > 将多个字典引用到工作簿模块

excel - 将多个字典引用到工作簿模块

问题描述

我想Sub从工作簿模块 ThisWorkbook 调用单独的过程，以在工作簿打开时运行计算。

我正在使用早期绑定来创建字典，然后Sub我调用下一个Sub并引用字典。

我可以将整个字典创建过程放在一个单独的模块中，从工作簿模块 ThisWorkbook 调用它，然后在Sub从工作簿模块调用的下一个过程中引用字典吗？

这就是我所拥有的：

'----------------------------
'Workbook module ThisWorkbook
'----------------------------

Option Explicit
Private Sub Workbook_Open()

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim MRMT As Workbook
Dim ER As Worksheet

Set MRMT = Excel.Workbooks("MRMT")
Set ER = MRMT.Worksheets("Sheet1")

With ER
    .Name = "Exposure Report"
End With

Import_Exposure_Report MRMT, ER

' I would like to have "Create_Dictionaries" procedure in a separate 
' module and then pass the dictionaries to the Sub below from this workbook module

Import_Historical_Data MRMT, ER  'Key1, Key2, Key3 ect.

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

标签： excelvbadictionaryreference

解决方案

Option Explicit
Public Sub Workbook_Open()

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    Dim MRMT As Workbook
    Dim ER As Worksheet

    Dim CommoditiesDict As New Scripting.Dictionary
    Dim IndexesDict As New Scripting.Dictionary
    Dim StocksDict As New Scripting.Dictionary
    Dim CryptoDict As New Scripting.Dictionary
    Dim BondsDict As New Scripting.Dictionary
    Dim FXDict As New Scripting.Dictionary

    Set MRMT = Excel.Workbooks("MRMTool_V7")
    Set ER = MRMT.Worksheets("Sheet1")

    ER.Name = "Exposure Report"

    With MRMT

        Import_Exposure_Report MRMT, ER

        Create_Dictionaries BondsDict, CryptoDict, CommoditiesDict, IndexesDict, FXDict, StocksDict, MRMT

        Populate_Historical_Data BondsDict, CryptoDict, CommoditiesDict, IndexesDict, FXDict, StocksDict, MRMT, ER

    End With

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

End Sub

推荐阅读