Solved: Excel 97 Class Issue

[Ken Puls]

Okay, so hopefully someone remembers this beast!

I've created a menu and am managing the Click event using a class module. Works great in 2003, but on taking it back to 97, I was shocked to get an error on the following line:

Public WithEvents cmbItem As CommandBarButton
The specific error is:

Compile Error
Object does not source automation events

Did Excel 97 really not support events for custom commandbarbuttons? That seems strange...

[Ken Puls]

Grrr...

I have something sinister going on on my server again. Any new workbook that I've created has an issue. When you go into the VBE, it "Can't find path" when you go to check the references. This may be related.

I hate using Office 97 on a Citrix server. I really wish I could afford to upgrade here...

I'll get back to you all on this...

[Zack Barresse]

This is going to be tough without a book on it. Here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_ancexcel.asp) MS doesn't even list 97 anymore. And 2000 is found here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/xltocobjectmodelapplication.asp). I have a 97 VB book at home, I'll check up on it this weekend. Wasn't VBA still based on VB5 then? Or was that when it migrated to 97??

[Ken Puls]

I believe that VBA in it's original form (97) was based on VB5, yes. I think it went to VB6 when it was upgraded to 2000. Could be wrong there though.

[Ken Puls]

Oh! And btw... I rebooted my server this morning. My reference issues have gone away (nothing like a little reboot to clear up issues), but the error listed above still remains.

[Ken Puls]

Update. A friend just tested on their machine. Same issues there. So at least it's not just my install. I guess that's sort of good. LOL!

[Killian]

Are you still looking for info on this, Ken?
I don't remember using toolbars/controls withevents but I have some source and notes from old projects at work to look up and an NT/97 build to test if you haven't confirmed your worse fears already. (You're right about 97 being VBA5, which doesn't bode well)

[Ken Puls]

Hi Killian,

I'd love to be able to make it work. What I'm trying to do, (if I didn't say above,) is make a dyanmic menu. I want to pull in a bunch of file names, populate the menu, and then have it call a routine based on the name of the button that was clicked.

I'm not sure what the chances are of making this work, but to be honest, haven't spent a lot of time working with it since I posted. I thought about making a toolbar, instead of a commandbar, but would far prefer the commandbar overall.

If you can shed anything, Killian, I'd be grateful. :)

[Zack Barresse]

I say make the '97'ers make do with the toolbars!! Down with 97'ers!! :devil2:

[Ken Puls]

You're lucky this forum censors, Barresse!:viking:

[Bob Phillips]

Why are you using a class to manage the click on a commandbarbutton?

[Ken Puls]

Seemed like fun at the time?

Seriously, I don't want to have to write a routine on the fly via code to assign to the OnAction property.

[Ivan F Moala]

you are going to have to use onaction as 97 doesn't support withevents and commandbuttons......

probably best to use conditional compiler constants for Visual Basic.

eg



#If VBA6 Then
'XL2000+
#Else
'Xl97
#End If



Look it up in Help

[Ken Puls]

Cheers, Ivan. Thank you.

That is exactly what I was afraid of. :(

[Bob Phillips]

Seemed like fun at the time?

Seriously, I don't want to have to write a routine on the fly via code to assign to the OnAction property.

So couldn't you just assign them all to the same macro and run it through there? The class is to simulate a control type aarray I presume, which doesn't seem necessary with commandbars.

[Zack Barresse]

The Class method will give you more control over user interactivity with the commandbar system, but beyond that there isn't much benefit that I'm aware of. If the menu is to be dynamic or heavy on the user interactivity, I like the Class method.. :)

[Killian]

Cheers, Ivan. Thank you.
That is exactly what I was afraid of. :(
Indeed, I'd forgotten about the joys of Office 97 (or have I just erased the painful memories?). I started to look at some documentation on a recent project and the first block of code that runs..?#If VBA6 Then
Init
#Else
'97 not supported
#End IfA colleague described it as a "usable object model filter", which I thought was a bit harsh...

[Zack Barresse]

If I don't use a Class, I'm generally using the application.caller(1) method. With popups in your custom menu, it's always been a real PITA for me to see which button is being clicked. I know I could do it if you dropped all of the sub popups, but that might make for a long list.. Course maybe those people using 97 deserve that... :devil2:

[Ken Puls]

So couldn't you just assign them all to the same macro and run it through there? The class is to simulate a control type aarray I presume, which doesn't seem necessary with commandbars.

Here's what I've done...

I'm populating a menu with file names from a certain directory. I'm also assigning the file path for those items to the DescriptionText property.

So I could end up with:

File1.xls
File2.xls
File2.xls

up to ...? Who knows. It depends on how many files are in there.

I could run a single "OnAction" procedure, yes, but how would I determine which button was clicked? How do I know that File1.xls was clicked so that I can open it?

There must be a way, I just haven't worked it out yet...

[Bob Phillips]

Same as you would with the class method. You can set separate tag or Parameter property values for each control and test that, or just test the caption in the ActionControl



With Application.CommandBars.ActionControl
Select Case .Tag
Case "value 1" : '...
Case "value 2" : '....
'etc
End Select

[Ken Puls]

Bob, you beat me posting by about 30 seconds! I just found it and ran a test. Looks like I may be able to offer 97 support after all. :)

[Bob Phillips]

The Class method will give you more control over user interactivity with the commandbar system, but beyond that there isn't much benefit that I'm aware of. If the menu is to be dynamic or heavy on the user interactivity, I like the Class method.. :)

Don't know what you mean, commandbars are ALL about user involvement. They are immaterial until a user does something to them.

[Ken Puls]

Flipping fantastic! :) :thumb

So basically, instead of using this in a class module:
Public WithEvents cmbItem As CommandBarButton

Private Sub cmbItem_Click(ByVal ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Select Case Len(ctrl.DescriptionText)
Case Is > 0
Call LaunchFile(ctrl)
Case Is = 0
Call OtherHandler(ctrl)
End Select
End Sub
I now use this in a standard module:
Private Sub WhatToDo()
Dim ctrl As CommandBarControl
Set ctrl = Application.CommandBars.ActionControl

Select Case Len(ctrl.DescriptionText)
Case Is > 0
Call LaunchFile(ctrl)
Case Is = 0
Call OtherHandler(ctrl)
End Select
End Sub
The actual assignment is much easier too... Just assign every button an OnAction property of "WhatToDo", instead of having to link it up with 3 lines of code linking it to the button event.

Btw.. yes, I could have just as easily used Tag instead of DescriptionText. I've not seen that DescriptionText actually shows anywhere either though, so any advantages to using one over the other?

[Ken Puls]

Funny things...

Now that I've got past the WithEvents deal, I find other stuff that is seemingly not supported by 97. I had to change a userform procedure to
Private Function Confirm() As Boolean

because
Private Function Confirm() As vbMsgBoxResult
wouldn't fly.

It's funny how much stuff we take for granted working in a later version. :) (I guess that's why you should develop in the oldest you want to use.)

At any rate, that was the last incompatible line of code, so know I'm not to testing the functinality in 97. :thumb

[Bob Phillips]

Flipping fantastic! :) :thumb

So basically, instead of using this in a class module:
Public WithEvents cmbItem As CommandBarButton

Private Sub cmbItem_Click(ByVal ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Select Case Len(ctrl.DescriptionText)
Case Is > 0
Call LaunchFile(ctrl)
Case Is = 0
Call OtherHandler(ctrl)
End Select
End Sub
I now use this in a standard module:
Private Sub WhatToDo()
Dim ctrl As CommandBarControl
Set ctrl = Application.CommandBars.ActionControl

Select Case Len(ctrl.DescriptionText)
Case Is > 0
Call LaunchFile(ctrl)
Case Is = 0
Call OtherHandler(ctrl)
End Select
End Sub
The actual assignment is much easier too... Just assign every button an OnAction property of "WhatToDo", instead of having to link it up with 3 lines of code linking it to the button event.

Btw.. yes, I could have just as easily used Tag instead of DescriptionText. I've not seen that DescriptionText actually shows anywhere either though, so any advantages to using one over the other?

Stick with me son, I'll set you on the path to enlightenment. As I said, it is used exactly as in the class module, without the added complication of WithEvents.

Can't see any benefit of Tag or Parameter over DescriptionText, other than the latter just doesn't sound right.

[Ken Puls]

Stick with me son, I'll set you on the path to enlightenment.

ROFL!

Okay, so one more question.

I've never had to use the conditional # that Ivan mentioned. What's the significance, and why is it important?

[Bob Phillips]

Conditional compilation allows you to have statements that will action differently depending upon whether the condition is True or False.

Ivan's code tested a built-in constant, VBA6, which is how you can test for the running Excel environment is Excel 97 (which was VBA5) or later. This way you can have code that is Excel 97 specific, and Excle 97 not-specific.

For example



#IF VBA6 Then
'do things that are might not work in Excel 97
#Else
'do things that do work in Excel 97
#End

When the code compiles, it checks the conditional tests and compiles that part of the code that resolves to true, which is really neat.

Another great thing about it is that you can create your own conditional compilation constants. As I said, VBA6 is an built-in, Mac is another. But if you create your own, you can have your code work different ways. For instance, you could have an evaluation version of your code, and production all in the same code, just use a consitional compilation constant to toggle it. More helpful, one that I always use is this



#Const RunAsTesting = True


then in my procedure error handling I have



pDBLoad_error:
utlSevereError "Record Load"

#If RunAsTesting Then
igFileNumber = FreeFile
Open ThisWorkbook.Path & "\" & AppId & ".log" For Append As #igFileNumber
Print #igFileNumber, "Build: " & sBuild
Print #igFileNumber, "Timestamp: " & Format(Date, "dd mmm yyyyy hh:mm:ss")
#End If

pDBLoad_exit:
Application.ScreenUpdating = True


or even write to the log fiel in other parts of the code. This way, when I am developing I can set the constant to true and I can put traing into the code.

All I have to do is set it to False before release. Actually, because I declare this constant in all modules, I have code that runs through the modules and sets it to true or false, even based upon an environment variable. That way I can't release testing version into production.

[Ken Puls]

Hmm... very cool!

I've gone with a DebuggingMode workbook property, which works quite well. I can see how this might have an advantage though. For normal things, it sounds like it might be similar to the application you described above. But for working between versions... now that is something super special. I actually did have to implement it in another area of this project, and knew that it did work, I was just curious why.

Thanks for the explanation! :)