Preventing compile errors when referencing objects ehanced after 1997
 

Hey Guys and Gals:

I am using the Hyperlink object for an Excel application intended for
use on Excel 97 and Excel 2000. I developed the code in 2000 with
syntax like:

ActiveSheet.Hyperlinks.Add Anchor:=Selection Address:="",
SubAddress:="", ScreenTip:="This is it, yea"

...but oops, darn it.

In 1997, ScreenTip was not around..so I'm talking space shuttles when
the program's talking Kitty Hawk..so I tried (code below) and got a
"compile error"

if left(Application.version,1) <= 8 then

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=""

else

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="", ScreenTip:="This is it, yea"

end if

Compile error makes sense in retrospect. (but its always rush, rush,
rush and not much thinky, thinky, thinky)

Matt

Preventing compile errors when referencing objects ehanced after 1997
 

Sub Tester3()
#If VBA6 Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:="", ScreenTip:="This is it, yea"

#Else

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:=""

#End If
End Sub

This specific code Untested in xl97, but should work.
--
Regards,
Tom Ogilvy

"Matt Somers" wrote in message
m...
Hey Guys and Gals:

I am using the Hyperlink object for an Excel application intended for
use on Excel 97 and Excel 2000. I developed the code in 2000 with
syntax like:

ActiveSheet.Hyperlinks.Add Anchor:=Selection Address:="",
SubAddress:="", ScreenTip:="This is it, yea"

..but oops, darn it.

In 1997, ScreenTip was not around..so I'm talking space shuttles when
the program's talking Kitty Hawk..so I tried (code below) and got a
"compile error"

if left(Application.version,1) <= 8 then

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=""

else

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="", ScreenTip:="This is it, yea"

end if

Compile error makes sense in retrospect. (but its always rush, rush,
rush and not much thinky, thinky, thinky)

Matt

Preventing compile errors when referencing objects ehanced after 1997
 

Tom,

Thanks. I have a couple follow-ups.

1. Is it necessary to include "Option Compare Text" in modules using
this directive.

2. Can the #if..Then..#Else conditional-compiler directive be used in
any module or must it be placed in the Workbook_ Open() event. I guess
I'm not really sure..what the compilation sequence is.

Thanks for both responses today. I might become your worst nightmare.

Matt

p.s. Also, I love John Green's book "Excel 2000 VBA". Do you have a book
out there?









*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Preventing compile errors when referencing objects ehanced after 1997
 

Matt,

You don't need Option Compare Text to use conditional compilation
directives. The two are unrelated. You can put conditional
compilation directives in any code in any module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Matthew Somers" wrote in message
...
Tom,

Thanks. I have a couple follow-ups.

1. Is it necessary to include "Option Compare Text" in modules
using
this directive.

2. Can the #if..Then..#Else conditional-compiler directive be
used in
any module or must it be placed in the Workbook_ Open() event.
I guess
I'm not really sure..what the compilation sequence is.

Thanks for both responses today. I might become your worst
nightmare.

Matt

p.s. Also, I love John Green's book "Excel 2000 VBA". Do you
have a book
out there?









*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Preventing compile errors when referencing objects ehanced after 1997
 

I don't have a book, but I was a technical reviewer on the one you cite and
its successor (see my name in the front <g).

You might want to look at the successor - it added some additional
chapters/information.

Looks like Chip policed up your questions.

--
Regards,
Tom Ogilvy

Preventing compile errors when referencing objects ehanced after 1997
 

Tom,

I just opened up my "Excel 2000 VBA" book, opened the front cover and
sure enough there you were under credits. I also recognized the name
Bill Manville, and David Rowlands. Good stuff. I'll have to check out
the new version.

While this email wasn't intended to be a question, your name seems to
prompt my imagination.

During the process of converting my application from Excel 2000 to Excel
1997, I have been forced to preceed most of my String class members with
the library name, VBA (i.e VBA.Trim ). It's a little bit annoying and I
don't really understand the reason why. I'm also receiving error
messages when using the Row member of the Range class, such as:

For Each Row in MyRng.Rows

when replaced with a variant, such as:

For Each Rw in MyRng.Rows

..it works ok

It seems most of my problems occur when referencing the Microsoft Excel
8.0 Object Library and the Visual Basic for Applications Library. I have
done things like deselecting checkboxes from the VBAProject Reference
menu, locating them on my system, then reselecting them, it really is
crazy. Could their be conflicts with multiple references to different
classes with the same member name?

Matt Somers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Preventing compile errors when referencing objects ehanced after 1997
 

Matt,

Because of the fact that certain library components of Office and Windows can
become corrupt over time, it does indeed help to preface built-in function
calls
with a VBA. prefix. To expedite coding, what I do is to introduce the notation
(by dropping vowels) Lft( ), Rght( ), Md( ), Trm( ), etc., and then in the code
module define:

Function Lft(X as String, N as Integer)
Lft = VBA.Left(X, N)
End Function

And similarly for the other built-ins.

-- Dennis Eisen