Declare objects as constants 1

[JTBorton]

Excel 2003 Prof.

Is there a way to declare and set objects as global constants other than doing it in the Workbook_Open()?

I ask because every time I hit the stop button while debugging I have to re-run the Workbook_Open() to reset my worksheet variables. I tried the following in a module:

const Set wkshtFormulas = Worksheets("Product Formulas")
const wkshtInventory as excel.Worksheet   'excel.Worksheet ("Inventory")

but neither are working.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

Make them Public variables.

Set them in a separate procedure.

Call that procedure whenever you restart.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JTBorton]

Well, I could do that, but I already pretty much do the same thing by running the Workbook_open() every time I restart (I don't have anything else in the workbook_open). So does that mean objects cannot be set as constants?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

So does that mean objects cannot be set as constants?

You don't have to ask my permission to try it.

But just think about it. Is an OBJECT a VALUE? That's what you assign to a constant--a VALUE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JTBorton]

Okay, that makes sense. Thanks again Skip.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

An alternative, in standard module - use property (or function). No other setting required:

Public Property Get wkshtFormulas() As Worksheet
    Set wkshtFormulas = ThisWorkbook.Worksheets("Product Formulas")
End Property

combo

 

[GVF]

My experience is that using the stop button crashes the code removing all values from memory whether they are values or objects or whatever. Same thing happens when your variables have been initialized with values and then you edit a userform that gets called. You have to re-initialize the variables again be they Private, Public or Global. When de-bugging... if you leftclick and hold on the left end of the yellow bar that signifies the active line of code and drag it to an End Sub or Exit Sub line and then step-execute that line, the procedure ends gracefully leaving the variables set with values. If the sub/function you are in was called by another sub then executing the End Sub line will jump you back to the calling sub and you will have to drag the yellow line down and exit that sub gracefully as well.
A workaround is if you really need to use the stop button while debugging then consider putting a temporary line of code (at the top of the sub you are debugging) that calls the Workbook_Open() sub and then put a breakpoint on the line below that line.
Anyway that's just my two cents worth.

Greg

 

[JTBorton]

Thanks all. I like combo's idea of class modules and GVF you bring up a good put, which really comes down to discipline.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

There is no need to use class module to declare property - can be done in a regular module. An advantage: no need to instantiate an intermediary object that will be removed when the project is resetted.

combo

 

[JTBorton]

That's a valid point Combo, and infact I ended up doing something like that before I got all the feedback here. My only hesitance to that approach is that you would have to perform an Is Nothing check at the beggninng of every procedure that uses the objects. With the class module method I can declare a global object As New [Class]. Then whenever I call the objects, clsObjects.MainSheet ... it would instance it when I need it wihtout any verification on my part.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

You can do everything (testing, setting objects/variables etc.) in the property/function procedure. In case of problems just set Nothing in output and test for Nothing in further code.

combo

 

[JTBorton]

Combo, I'm not sure I understand what you are saying. Can you please explain?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Are you saying I can use a Property Get statement OUTSIDE of a class module? If so how does this work?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

Yes, you can. The code from my first can be in standard module. Another example of silly code that interacts with the third worksheet if it exists:

Public Property Get TestName3() As String
If ThisWorkbook.Worksheets.Count < 3 Then
    TestName3 = ""
Else
    TestName3 = ThisWorkbook.Worksheets(3).Name
End If
End Property

Public Property Let TestName3(ByVal sNewName As String)
If ThisWorkbook.Worksheets.Count >= 3 Then
    ThisWorkbook.Worksheets(3).Name = sNewName
End If
End Property

Sub test()
MsgBox TestName3
TestName3 = "xxx"
MsgBox TestName3
End Sub

In the object browser in vba project TestName3 can be found as global proberty and property in a module where the code is. The 'test' is a global method. After deleting the Property Let procedure the property will be marked as read-only.


combo

 

[JTBorton]

Combo, that's truely amazing! At first I was really lost at what you were trying to say but now you have expanded my little VBA mind. This is exciting news. Thank you so much!

Now let me ask you this: Are there any other advantages to using this method other than setting objects? Say for example I had a user-defined type and I wanted to manage the way it recieves information by using Property Get/Let/Set statements. Would it be more efficient to make a class object to manage my udf? Or would it be better to use these Get/Set/Let statements in a standard module INSTEAD of a user-defined type? What I'm really trying to ask is, when is it more appropriate to just make a class object?

I'm currently tied up with other projects at the moment so I don't have much opportunity to test anything in VBA. But I'm always open to hear or learn anything that will help me later.


-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

For me the advantages of using properties in standard module:
- initialisation of variables (all types),
- error checking,
- access limitation.
Why not class modules? If there is no need for multiple instances, there is no need to write and instantiate a class too. However, as long as you follow basic programming rules, it's up to you what solution you choose. There are many ways to skin the cat.
As I suggested earlier, instead of property you can use a function.
Finally, some links I foung on properties in standard module:

http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba-property_procs_in_std_module.htm

http://vb.mvps.org/hardcore/html/initializingstandardmodulevariables.htm

combo

 

[SkipVought]

combo,

I didn't know that any one else 'founged'. Welcome to the foungers! ;-)

PS: My fungers do that 'most ev'r day!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Sorry Skip, I have no spoiler in the brew(s)er.

combo