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" <(E-Mail Removed)> wrote in message
news:jbv9tu$ft8$(E-Mail Removed)...
> 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

"Tim Childs" <(E-Mail Removed)> wrote:
> 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.

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

"MerseyBeat" <(E-Mail Removed)> wrote in message
news:jbvjsm$r33$(E-Mail Removed)...
> "Tim Childs" <(E-Mail Removed)> wrote in message
> news:jbv9tu$ft8$(E-Mail Removed)...
>> 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

"Tim Childs" <(E-Mail Removed)> wrote in message
news:jc0756$3k2$(E-Mail Removed)...
> 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
>
> "MerseyBeat" <(E-Mail Removed)> wrote in message
> news:jbvjsm$r33$(E-Mail Removed)...
>> "Tim Childs" <(E-Mail Removed)> wrote in message
>> news:jbv9tu$ft8$(E-Mail Removed)...
>>> 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
>

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!)

On 12/10/2011 03:48, Tim Childs wrote:
> 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
>
>

--
Dave Peterson

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

"joeu2004" <(E-Mail Removed)> wrote in message
news:jc0728$34d$(E-Mail Removed)...
> "Tim Childs" <(E-Mail Removed)> wrote:
>> 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.

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



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:jc26f1$bjb$(E-Mail Removed)...
> 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!)
>
> On 12/10/2011 03:48, Tim Childs wrote:
>> 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
>>
>>
>
> --
> Dave Peterson

"Tim Childs" <(E-Mail Removed)> wrote:
> 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 -----

"Tim Childs" <(E-Mail Removed)> wrote in message
news:jc2qme$v2n$(E-Mail Removed)...
> 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
>
> "joeu2004" <(E-Mail Removed)> wrote in message
> news:jc0728$34d$(E-Mail Removed)...
>> "Tim Childs" <(E-Mail Removed)> wrote:
>>> 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.

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

On 12/11/2011 11:55, Tim Childs wrote:
> 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
>
>
>
> "Dave Peterson"<(E-Mail Removed)> wrote in message
> news:jc26f1$bjb$(E-Mail Removed)...
>> 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!)
>>
>> On 12/10/2011 03:48, Tim Childs wrote:
>>> 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
>>>
>>>
>>
>> --
>> Dave Peterson
>
>

--
Dave Peterson