Compiling Error in Earlier Version of Excel

T

Tim Childs

Hi

This piece of code
ThisWorkbook.ForceFullCalculation = True
will work in Excel 2007 and above

Is there any way to mark the code so that it will avoid the COMPILING error
in earlier versions where this functionality was not available? (I use the
file in different versions of Excel)

Many thanks

Tim
 
M

MerseyBeat

Tim Childs said:
Hi

This piece of code
ThisWorkbook.ForceFullCalculation = True
will work in Excel 2007 and above

Is there any way to mark the code so that it will avoid the COMPILING
error in earlier versions where this functionality was not available? (I
use the file in different versions of Excel)

Many thanks

Tim

Tim,

I believe Excel 2007 is version 12.0. If I am wrong I presume others will
weigh in and correct me.

Try the following:

If Application.version >= 12 then
ThisWorkbook.ForceFullCalculation = True
End if

MB
 
J

joeu2004

Tim Childs said:
This piece of code
ThisWorkbook.ForceFullCalculation = True
will work in Excel 2007 and above

Is there any way to mark the code so that it
will avoid the COMPILING error in earlier versions

#If VBA7 Then
ThisWorkbook.ForceFullCalculation = True
#End If

VBA7 is false (undefined) in Excel 2003 and true in Excel 2010. I don't
know about Excel 2007.
 
T

Tim Childs

Hi MB

Thanks or response. In fact, I have tried this already but the compiler
"tests" all the code before runtime so this has not got round the compiling
problem

Best wishes

Tim
 
M

MerseyBeat

Tim Childs said:
Hi MB

Thanks or response. In fact, I have tried this already but the compiler
"tests" all the code before runtime so this has not got round the
compiling problem

Best wishes

Tim

Tim,

My apologies for misinterpreting your post. Unfortunately, I do not have
any further recommendations. Hopefully others more knowledgeable than I
will weigh in.

Cheers,

MB
 
D

Dave Peterson

You could put the sub/function in its own module and call it only when you're
running the version of excel that supports it (using the if statement in other
posts).

But it'll be up to you to not try to compile the code manually, too!

Another way is to use something like:

Dim VarThisWorkbook as Object 'not usually a nice declaration!

set Varthisworkbook = thisworkbook

if runningthecorrectversion then
varthisworkbook.forcefullcalculation = true
end if

(All untested, uncompiled -- watch for typos!)
 
T

Tim Childs

Hi

thanks for that

it is Excel 2007 where I'll want it to work unfortunately: that's what we
have at work. will try it next week

Tim
 
T

Tim Childs

Hi Dave

thanks for those options - so the compiler only compiles as it goes, on a
"need be" basis i.e. compiling the next used sub or function etc.?

Thx for warning about manual compiling - as I like to do that :)

bw

Tim
 
J

joeu2004

Tim Childs said:
it is Excel 2007 where I'll want it to work unfortunately:
that's what we have at work. will try it next week

Don't bother. I think Dave's suggestion is superior. It can be simplified
as follows:

Dim ws As Workbook
If Application.Version >= 12 Then ' XL2007 or later
Set ws = ThisWorkbook
ws.forcefullcalculation = true
End If

If "ws" were replaced with ThisWorkbook, a compile-time error would result.

But with "ws", apparently VBA does late-binding even though "ws" is typed as
Workbook (!).

It might be more reliable to declare "ws" as Variant, just to be sure that
VBA cannot use "early" binding in some later version.

-----

Regarding my suggestion....

It bothers me that I cannot find any Microsoft documentation about VBA
compile-time constants like VBA6 and VBA7.

It also bothers me that in Excel 2010, VBA 7.0 has a copyright date of only
2010. If VBA 7.x had been implemented for Excel 2007, I would expect a list
of copyright dates that includes 2007 or earlier. That is needed according
to US copyright law in order to protect code (i.e. by seeking monetary legal
remedies) written before Excel 2010.

The point is: I suspect that VBA7 is False in Excel 2007. I cannot confirm
that now, however.


----- original message -----
 
D

Dave Peterson

Not on a sub/function level, but on the module level. So if you keep your
version dependent stuff in a separate module, you should be close to ok <vbg>.
 
T

Tim Childs

Hi

thx for that. Have tested the code (ws.ForceFullCalculation = True) and it
works really well in Excel 2003, when "protected" by an application version
check.

Many thanks to you and Joe

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top