Compiling Error in Earlier Version of Excel

  • Thread starter Thread starter Tim Childs
  • Start date Start date
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
 
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
Click to expand...

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
 
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
Click to expand...

#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.
 
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 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
Click to expand...

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
 
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!)
 
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
 
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
 
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
Click to expand...

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 -----
 
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>.
 
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
 
Back
Top