Referencing multiple dictionaries to workbook module
1
I would like to call separate Sub procedures from the workbook module ThisWorkbook to run calculations upon workbook open.
I am using early binding to create dictionaries and from that Sub I am calling the next Sub and referencing the dictionaries.
Can I place the whole dictionary creation procedure in a separate module, call it from the workbook module ThisWorkbook and then reference the dictionaries in the next Sub procedure that is called from the workbook module?
This is what I have:
'----------------------------
'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
excel vba dictionary reference
edited Dec 3 '18 at 7:19
Community♦
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
|
show 10 more comments
1
I would like to call separate Sub procedures from the workbook module ThisWorkbook to run calculations upon workbook open.
I am using early binding to create dictionaries and from that Sub I am calling the next Sub and referencing the dictionaries.
Can I place the whole dictionary creation procedure in a separate module, call it from the workbook module ThisWorkbook and then reference the dictionaries in the next Sub procedure that is called from the workbook module?
This is what I have:
'----------------------------
'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
excel vba dictionary reference
edited Dec 3 '18 at 7:19
Community♦
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
1
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
1
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56
|
show 10 more comments
1
1
1
I would like to call separate Sub procedures from the workbook module ThisWorkbook to run calculations upon workbook open.
I am using early binding to create dictionaries and from that Sub I am calling the next Sub and referencing the dictionaries.
Can I place the whole dictionary creation procedure in a separate module, call it from the workbook module ThisWorkbook and then reference the dictionaries in the next Sub procedure that is called from the workbook module?
This is what I have:
'----------------------------
'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
excel vba dictionary reference
edited Dec 3 '18 at 7:19
Community♦
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
I would like to call separate Sub procedures from the workbook module ThisWorkbook to run calculations upon workbook open.
I am using early binding to create dictionaries and from that Sub I am calling the next Sub and referencing the dictionaries.
Can I place the whole dictionary creation procedure in a separate module, call it from the workbook module ThisWorkbook and then reference the dictionaries in the next Sub procedure that is called from the workbook module?
This is what I have:
'----------------------------
'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
excel vba dictionary reference
excel vba dictionary reference
edited Dec 3 '18 at 7:19
Community♦
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
edited Dec 3 '18 at 7:19
Community♦
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
edited Dec 3 '18 at 7:19
Community♦
11
edited Dec 3 '18 at 7:19
Community♦
11
edited Dec 3 '18 at 7:19
Community♦
11
11
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
asked Nov 18 '18 at 0:40
I. Я. NewbI. Я. Newb
1269
1269
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
1
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
1
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56
|
show 10 more comments
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
1
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
1
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
1
1
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
1
1
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56
|
show 10 more comments
1 Answer
1
active
oldest
votes
1
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
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53356894%2freferencing-multiple-dictionaries-to-workbook-module%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
add a comment |
1
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
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
add a comment |
1
1
1
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
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
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
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
edited Dec 3 '18 at 22:48
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
answered Dec 1 '18 at 1:38
I. Я. NewbI. Я. Newb
1269
1269
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53356894%2freferencing-multiple-dictionaries-to-workbook-module%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I’m not sure I understand - why it is you want to do this?
– Marcucciboy2
Nov 18 '18 at 0:53
@Marcucciboy2, the dictionary creation is quite long - about 650 lines and, also, I may be needing them in a later state of the code. Just wandering if I can make them "Global" for the workbook and reference them.
– I. Я. Newb
Nov 18 '18 at 0:56
@Marcucciboy2, also, I wouldn't like to create the dictionaries in the class module... Although this is the only way I see at present to have what I want.
– I. Я. Newb
Nov 18 '18 at 1:02
1
If it’s that involved you could pass the resulting dictionary out of the function you create it in as an object
– Marcucciboy2
Nov 18 '18 at 1:03
1
Ahh hm. Then it might not hurt just to post the whole thing so I can understand how it’s so long if the declaration is that simple
– Marcucciboy2
Nov 18 '18 at 17:56