Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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 " wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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. " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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 " wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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... "Chip Pearson" wrote: 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 " wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't "private" work?
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 " wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D41) | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) | |||
How does "Private" work | Excel Programming |