In
Property procedures in a standard module
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba-property_procs_in_std_module.htm
I show that it is possible to declare a Property in a standard module and briefly discuss the pros and cons with doing so. This tip results from something I was doing some months ago. In the midst of coding — I’ve long since forgotten what — it struck me was that, if I were writing a class module, what I was doing was ideally suited to being made a property. So, I tried it in a standard module in Excel 2003 and, lo and behold, it worked.
Names and formatted formulas
http://www.tushar-mehta.com/publish_train/data_analysis/08.htm
is a draft chapter in a book. It looks at the use of names referencing references using absolute address as well as relative addresses as well named constants and formulas. Still to come is the use of names that refer to the current sheet. The draft chapter also looks at formatting a formula using line breaks and spaces, both of which are “white space” that Excel ignores. I vaguely recall from several years ago that I did something that caused Excel to collapse all the pretty formatting in a cell but I cannot recall what it was.
Formatting Formulas: Excel Formula Formatter
It will turn a complex formulas into whitespace formatted, which you can use as your real formula.
Maybe I’ll rewrite it for 2007 – I have a better technique in mind for next time.
Tushar – intersesting stuff. A couple of questions:
1. Do the normal rules apply for classifying the property as module-level or Public? Will this work if built into a referenced add-in? Any guidance on when module-property should be used instead of Class Modules?
2. One of the struggles I have had with named formulas is that (I think) one cannot simply make a “global” named formula that would be used the same way on multiple sheets. My solution was to use the INDIRECT Function in the named formulas to make them transportable from sheet to sheet. Is there a better way?
Nice article Tushar (only read the second one though)
I did miss some text between “An add-in that lists all names in a comprehensive list” and “Summary” though.
My own article about Defined names has some similarities and differences to yours:
http://www.jkp-ads.com/Articles/ExcelNames.htm
One point I would like to make is that you guys have probably been using class modules for some time now. They are Forms…
Alex J,
The person that instanciates the object of your class is the owner.
He can use it how he pleases, he can make it private or public if he wants.
“Property Let” writes the value “Property Get” returns the value.
The user never gets to play with the internal variables.
You can control it and use it like any other variable.
Bear in mind that someone wrote a worksheet class and you use it all the time.
My advice is that if you wrap the function up in a class it is normally complicated and has properties that interact and methods that can be run to change multiple properties.
Example of use of a complex class module:
Sub AlexWalking ()
Dim Alex as WalkingThing
DIm Jan as Walkingthing
On Error Goto AlexFellOver
Alex.Legs=4 ‘ Property being Set
Alex.Walk(5) ‘ sub being run in class
Alex.CanHop = False ‘ Property being Set
Alex.Legs = 1 ‘ Property being Set
Alex.Walk(5) ‘ sub being run in class if some criteria
If not(Alex.fallen) then Jan.Walk(Alex.Walked)
Set Alex = Jan.Friends.Add
Debug.print Jan.Friends.Count
Jan.Walk(10)
Debug.print Alex.Walked
Exit Sub
AlexFellOver:
Msgbox “Alex fell over because he only had ” & Alex.legs & ” leg(s)” ‘ Property being “got”
End Sub
If it is getting complex then make it a class.
Classes can be used to interact with each other like the line in the middle.
Because we declared them both as a class they can even perhaps effect each other and use a simple interface. In this case it’s simply a walking program but perhaps it is a debt structure that follows another debt structure depending on a criteria or a grid of powerstations that turn them selves on and off based on the others around it. etc the options are endless.
The tricky bit here would be building the class module called WalkingThing
:-)
Now with VB Tags….Fingers crossed….
Dim Alex as WalkingThing
Dim Jan as Walkingthing
On Error Goto AlexFellOver
Alex.Legs=4 ‘ Property being Set
Alex.Walk(5) ‘ sub being run in class
Alex.CanHop = False ‘ Property being Set
Alex.Legs = 1 ‘ Property being Set
Alex.Walk(5) ‘ sub being run in class if some criteria
If not(Alex.fallen) then Jan.Walk(Alex.Walked)
Set Alex = Jan.Friends.Add
Debug.print Jan.Friends.Count
Jan.Walk(10)
Debug.print Alex.Walked
Exit Sub
AlexFellOver:
Msgbox “Alex fell over because he only had ” & Alex.legs & ” leg(s)” ‘ Property being “got”
End Sub
Rob: Thanks for the pointer. From the limited tests with the add-in it would appear that it doesn’t account for operator precedence and parenthesis overrides. Consequently, it did nothing for the formula I used in my example. Any plans to include that capability in the add-in?
Also, did you by any chance get the BNF for Excel formulas? I would love to lay my hands on it — and particularly for the chart SERIES formula.
Alex:
1) (a) Yes, they should. Though, IMO, making variables in a class public defeats the purpose of good OOP design.
(b) Yes, it should work in an add-in. As I wrote I don’t quite remember where I actually used it but I don’t release any code that is not an add-in. So, it must work in an add-in.
(c) Where would I use it? First of all, with caution since this use is not really documented anywhere and I have not tested it with anything but 2003. Also, keep in mind that a standard module always exists (there’s no instantiation of an object based on it) and only one copy of it exists (there’s no instantiation of it). So, it’s a “cute” way to simplify access to something that other parts of the code might want to treat as a variable while internally support a more complex implementation.
One place where this might find some value is as a class level property or method. I forget what it is called — one should be able to find the terminology in the OOP literature including the .Net literature — but essentially it is global, i.e., shared by all instances of a class.
2) Chip Pearson has (had?) documented a non-standard (and a non-documented?) way of creating a global that is global to the Excel instance. You should be able to find the material, if it still exists, on his website at http://www.cpearson.com
JKP: The missing section is something I haven’t done yet. It will be an add-in that lists all the names in a workbook. Doubt if it will be anything very sophisticated and for all I know the enhanced Name Manager in XL2007 might do all that I have planned for it. But, we will see.
Jan: I am not sure what point you were trying to make but one clarification. Class modules are not forms. Technically, it’s the other way around. A form is a class…well, kinda since it has some special capabilities built into it.
Agreed Forms are Class modules.
“A form is a class…”
and sometimes it’s also an object! lol!
Private Sub UserForm_Activate()
Dim form As UserForm1
Dim x As Integer
Me.Caption = “I was born on “ & Time() & ” – Is this visual inheritance?, lol”
For Each form In UserForms
form.Top = 100 + x
x = x + 30
Next
End Sub
Private Sub UserForm_Click()
Dim form As UserForm1
Set form = New UserForm1
form.Show
End Sub
T M, you are absolutely right. My ramble was unclear and vague.
My intentions were to show that class modules are easy to use and that its a good idea to find a reason to use one. Also that people have already been using them for some time in the form or forms :-), which presumably they design and instanciate all the time.
I haven’t ever tried to use property proc in a normal module, I didn’t know they existed but to be honest I am not quite sure why I would use them, as its the same as a function with a static variable, and an optional parameter,(which have the added benefit of less module level variables and procedures, or even less modules as you don’t need to setup a specific option private module).
Also it might be worth considering the use of Enum to allow easy access to specific group of constants for example like the primary colours in Tushars interesting example.
Nice form Ross cute demo.
TM,
Thanks for the great reply. I’m not having much luck finding that item on Chip’s site, though.
Tushar: please e-mail me your formula and a brief description of what you expected to see.
rob@ vangelder.co.nz
I’m not aware of any formulas it cannot handle.
Cheers
Tushar: “It will be an add-in that lists all the names in a workbook”
Wouldn’t it be simpler to just refer to my Name Manager?
http://www.jkp-ads.com/OfficeMarketPlaceNM-en.asp