Versioning Question

[ExcelMan]

Is there a way to use a VBA feature in Excel 2000 when running in Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to run in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I get a
compile error because Excel 97 does not accept the vbModeless parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I can
use here?

Thanks.

 

[Bob Phillips]

ExcelMan,

Have you tried

If Application.Version > 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that into an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

 

[Ron de Bruin]

You are right Bob

I use this also in the Google Add-in

 

[Bob Phillips]

Harald,

Thanks for showing me that. I don't have 97 on this machine to test, and
although what you say is obvious (once someone tells you that is <vbg>), I
just strung together logical code. Ain't that always the way, logical code
doesn't always work. I guess that is why we spend so much on testing!

Regards

Bob

 

[Bob Phillips]

What's a Mac <vbg>

Bob

PS Actually I had an Apple IIe back in 1980, then one of the early Macs. I
wanted a Lisa, but could never afford it.

 

[Harald Staff]

Hi Bob

Except for the brilliant conditional compilation, I think isolating it is the only way
around it. This was one of the colorful debates back in the Hawley days, that's why I
remember it so well:
http://makeashorterlink.com/?M2C4167F5

quote: "I have not tested this but if the UserForm ShowModal Property is set to False, it
will simply show as Modal in pre 2000 versions and show as not Modal in >97 versions. So
is there any need to test?"



HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

 

[Bob Phillips]

Thanks, Harald, you confirm what I expected, but that makes an interesting
read anyway.

I see what Dave was getting at, but a very loose phrasing certainly confused
a few folks.

By the way, you are Norwegian are you not? If so, I would have expected you
to spell colourful correctly <vbg>.

Regards

Bob

 

[Ron de Bruin]

My Add-in is working correct in Office 2003 Tom.
So It must be VBA6 also

 

[J.E. McGimpsey]

What's a Mac <vbg>

It's what Excel was written for, years before there was a "Windows".
<g>