Excel VBA: How to use objects defined in a previous module in a new module without defining them again

Question

I have previously defined Report_wb in an module Obtain_Data, and now I am writing a new module Module1. I want to use the object Report_wb without defining it again. Is there any way to do that? Thanks!

If you make `Report_wb` public, then you can reference it in other modules as `Obtain_Data.Report_wb`. — xidgel, Apr 25 '17 at 16:08

score 0 · Answer 1 · answered Apr 25 '17 at 16:05

0

rather than declaring Report_wb as a dim within a sub, write it as a public declaration at the top of the module

i.e.

'Module 1

Public stringTest as string

Sub SetString()
stringTest = "Hello World!"
End Sub

and then in another module:

'Module 2

Sub TestString()
call SetString
debug.print stringTest
End Sub

answered Apr 25 '17 at 16:05

Zerk

1,595
9
16

the `Call` is not necessary. – Paul Ogilvie Apr 25 '17 at 16:07
2

@PaulOgilvie `Call` isn't required, but it may be used as a style preference by the author. – David Zemens Apr 25 '17 at 16:08
@PaulOgilvie syntactic sugar that has no impact on the purpose or functionality of the example. – Zerk Apr 25 '17 at 16:21

Excel VBA: How to use objects defined in a previous module in a new module without defining them again

1 Answers1