Function v/s Procedure (VBA/All/2000)

Topic

New Reply

In an effort to limit the user’s visibility of procedures within the code, I habitually set procedures as private wherever possible. However when calling such a procedure from a different module it must be public. To overcome this conflict, I am inclined to change the called procedure to a function. Recognizing that this is only possible if there are no arguments being passed; are there any other drawbacks to this approach of which I should be aware?

TIA

Reply | Quote

Replies

Don,
I’m not sure what makes you think that you cannot pass arguments to a function? You certainly can. In broad terms the only real difference between a function and a subroutine is that a function returns a value (though you don’t have to use it for anything).

Reply | Quote

Rory
Lets put it down to creative memory. Thanks for the clarification.

Reply | Quote

If by “limit user’s visibility”, you mean that you don’t want the procedures to appear in the Macros dialog (Tools->Macro->Macros), you can assign an unused optional parameter:

Visible from Macros dialog:

Sub HelloWorld()
MsgBox "Hello, World!"
End Sub

Not visible:

Sub HelloWorld(Optional bNull as Boolean)
MsgBox "Hello World"
End Sub

Both can be called identically from other procedures, ie:

Call HelloWorld

Reply | Quote

Thank you for that Andrew
That little stunt is going to receive a whole pile of use.

Reply | Quote

for this little trick!

Reply | Quote

In Excel, you can also use
Option Private Module
at the top of the module and don’t put in the Private keywords in front of the subs.
Funny enough this doesn’t work for Word, where it doesn’t recognize the subs in such a module when called from others.
Amd of course you can protect your project to hide their subs from the dialog.

Reply | Quote

> Amd of course you can protect your project to hide their subs from the dialog.

Is that in Excel only? Because I have a protected global template in the Word 2003 Startup folder and you cannot see the Subs and Functions in the VBE editor without the password. But the Public Subs are still visible in Word: Tools | Macro | Macro’s will produce a list and the macro’s can be selected and run from there.

Reply | Quote

Errm, I made a mistake. Protecting the code has no effect on the visibility.

Reply | Quote

> I am inclined to change the called procedure to a function.

I am a man of few principles, but one of them is to make my functions Public, and therefore available to applications across the board, and to use SUBs as Public with No arguments ONLY for the user interface, i.e. user macros.

If you scan a template of mine, only those very few things that are permitted as a user interface are Public Subs; all the rest are Public Functions, with at least one argument.

I am excluding the obvious “Private” functions from code for a UserForm in this littel speech.

Reply | Quote

I, on the other hand, use functions, public and private, to create loosely coupled code in standard modules. Since classes are a slightly different animal, I relax that approach in a class module because a class is tightly coupled by design. I use subs where I don’t need an indication of success or a return value.

Reply | Quote

Don,

As a bit of an aside, there may be added bonuses in returning a value from a procedure in terms of a success or failure of that procedure. For instance:

Function ReadNextXYZ() As Boolean
‘ Return True if read is successful, False otherwise

Then in the calling code:

If ReadNextXYZ=True then
‘ Do stuff with newly read XYZ
Else
‘ Do other stuff, now that all XYZs have been read
End If

Alan

Reply | Quote