Why doesn't "private" work?

[joeu2004]

According to Walkenbach, Private "indicates that the
Function procedure is accessible only to other procedures
in the same module", and "when you create a function that
should be used only in a VBA procedure, you should declare
it Private so that users don't try to use it in a formula".

That is exactly the behavior that I want. But when I create
the following functions, both are callable from formulas in
the spreadsheet:

function foo1(x)
foo1 = foo2(x)
end function

private function foo2(x)
foo2 = x
end function

My intent is that the spreadsheet can have =foo1(1), but
not =foo2(2). However, the latter works :-(.

Bottom line: How can I declare function foo2 so that it is
callable only from the procedures in the same module and
specifically not callable from formulas in spreadsheets?

 

[Peter T]

I must admit I have wondered about that before. It seems Private does not
affect UDF's, even if the module is headed "Option Private Module". Private
functions can also be called in another workbook eg =MyBook.xls!foo2

However is it really an issue. You could name your 'private' function as
something that could never be typed accidentally. Perhaps put your private
functions in a module headed Option Private Module so they do no appear in
the functions list (Shift-F3) in the User defined category.

Alternatively pass an argument to a Sub, eg

Function foo3(x)
foo4 x
foo3 = x
End Function

Sub foo4(x)
x = x * 2
End Sub

You could prefix the Sub with Private but as it has an argument it will not
appear in the macro list, Alt-F8.

Regards,
Peter T

 

[GB]

Truely interesting. I had never tried to hide a function from the user for
the reason that you are trying. I.e, I have hidden subs and functions so
that they could not be seen when looking at a list of macros, but not so that
they could not be used in the spreadsheet.

I attempted to implement your foo2(x) function and came across the same
thing. There are a few things you can do to prevent a user from knowing that
the function exists in the first place, which is to obviously make it
private, and then not allowing the user to see the VBA code so that they do
not obviously know that it exists. (Password protecting the code from
reading.)

Another aspect you can try is to read cells as they get changed. If the
cell contains a function you want hidden then do something with the text of
the cell or prompt the user about it or both. Something like that.

You could basically implement a "ReservedFunction" and then on each sheet
that exists you can add into, I think the Worksheet_Change routine, that if
ReservedFunction of the changed cells is true, then perform the actions
desired. Now however, this does not cover newly created sheets if the user
decides that they want to add a sheet.

There is a reference utility that will allow you to manipulate the VBA code
of a project from within the VBA program. You could then also establish code
in the workbook NewSheet routine, that will add the VBA code to handle your
worksheet issue.

A little complex I know, but the first thing is really, don't tell (allow
viewing of the code to) the user what functions exist, especially the ones
you don't want them to use.

 

[Chip Pearson]

You can put those functions that you don't want to be callable
from a worksheet in a worksheet module (make them public), and
call them with the worksheet codename qualifier. E.g.,

' in Sheet3
Public Function Foo(X)
Foo = X
End Function

' in Module1
Sub AAA()
Dim Res
Res = Sheet3.Foo(123)
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"(e-mail address removed)"

 

[GB]

Oooh good idea. In fact you could create a new worksheet, that will house
all of your hiden functions, then in excel, if you look at the worksheet
properties, you can make it Very Hidden. Thus the user would not really know
that the worksheet was present unless they got into the VBA code and looked
at the list of sheets, did a comparison of what they could see and what they
couldn't and found a way then to implement the functions you have but through
alternate means. Let's not forget, if a user really wants something to
happen, they are likely to find a way to make it so...

 

[joeu2004]

Thanks for all the suggestions, notably from Peter, GB and
Chip (up to this point in time).

The "private" functions are intended to be documented as
part of an example. So suggestions for hiding their
existence from the user are not applicable. And since I
want to make the documented example easy to use, it is
counter-productive for me to move some functions into
other modules; too difficult to document. Of course, the
simple solution is to include the comment "internal; do not
call from spreadsheet". But I was hoping for something a
little more ironclad and less verbose.

I am surprised that Walkenbach got this "wrong". But
arguably, that is subject to interpretation of what he means
by "so that users __don't__ try to use it". Perhaps he was
intending to use "private" as simply a documentation tool
in that context.

PS: I neglected to mention that I am using Excel 2003.
That is moot at this point, but I should have mentioned it.
Thanks again for the responses.

 

[Peter T]

I haven't read what you refer to by John Walkenbach but I would doubt he got
it wrong, perhaps not entirely complete as regards UDF's. Click on
"Private", press F1 and read VBA's help on all it's usages and implications.
But even Help is not complete.

If your concern is only documentation, instead of -ve comments like
"internal; do not call from spreadsheet perhaps simply ' UDF might suffice.
If you have a largish project perhaps put all your UDF's in a dedicated
module named "mod_UDFs".

Although functions for internal use and as UDF's may be written identically,
typically they tend to be one purpose or the other. A dead give away that
indicates my UDF's is all have an error handler to return something like Foo
= CVErr(xlErrValue)

Regards,
Peter T

PS I don't think there is any difference between versions on this subject