Organising large amounts of VBA code

[Walter Briscoe]

I use Excel 2003 from Vista
As a naive Excel VBA user, I wrote code to Application.StartupPath &
"\personal.xls"

That file consists of 2 modules.

The first is nearly 4k lines of my own code.

The second is Chip Pearson's modRegistry code.
(I use CreateObject("InternetExplorer.Application") to access http URLs
and found IE can be painfully slow if images are downloaded.
RegistryCreateValue HKCU, "Software\Microsoft\Internet Explorer\Main", _
"Display Inline Images", "no"
switches off image downloading.
RegistryCreateValue HKCU, "Software\Microsoft\Internet Explorer\Main", _
"Expand Alt Text", "yes"
puts placeholders in place for images.)

I now want to write code in a second file (foo.xls), sharing
personal.xls code. How do I make personal.xls code usable from foo.xls?
Thanks!

 

[Bob Phillips]

Run the procedures using Application.Run

Application.Run "Personal.xls!myMacro"

 

[Walter Briscoe]

In message <[email protected]> of Mon, 22 Mar 2010
09:52:55 in microsoft.public.excel.programming, Bob Phillips

Run the procedures using Application.Run

Application.Run "Personal.xls!myMacro"

Thanks! I did that. An example is:

In personal.xls:

....
Dim IE As Object ' IWebBrowser2 ' SHDocVw.InternetExplorer
....

Private Sub TxURL(ByVal URL1 As String)
If IE Is Nothing Then
Set IE = CreateObject("InternetExplorer.Application")
....

In Oyster.xls:
Application.Run "personal.xls!TxURL", _
"https://oyster.tfl.gov.uk/oyster/entry.do"

I am pleasantly surprised to see that the Private procedure TxURL is
visible on the interface between the files. I did not want to make such
interfaces visible in the list accessed by Tools/Macro/Macros... aka
Alt+F8.

Shared data interfaces in personal.xls - such as IE above - are
invisible from Oyster.xls.
How do I make such interfaces visible?
If nothing better offers, I can probably use optional procedure
interfaces, so that internal calls can use shared data and external
calls use parameter interfaces. This would minimise the need to change
personal.xls.

 

[Bob Phillips]

You can make TxURL public, it will not be visible to Tools/Macro/Macros...
because it has an argument.