The problem with this code is that the variable xReference is being tested
to see if it is xlA1 and also tested later to see if it is xlR1C1. However,
it is never set to either of those when it is created, so neither of the IF
statements will be true.
There are two ways to deal with this:
Firstly, change the
> End If
> If xReference = xlR1C1 Then
pair of lines to a single Else. This will ensure that one part of the code
always gets executed.
Alternatively (or additionally), remove xReference completely and simply do
the IF test(s) against Application.ReferenceStyle.
Note also that the "Exit Sub" statements are not needed.
Code to try (but I haven't tested this)
Sub Switch_Reference_Style()
'shortcut CTRL w
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlA1
End If
End Sub
Regards, Dave S
----- Original Message -----
From: "garilou" <garilou@cgocable.ca>
To: <ExcelVBA@yahoogroups.com>
Sent: Monday, January 02, 2012 7:13 AM
Subject: [ExcelVBA] Macro to change reference style
> Hi group!
> First Happy new Year to all!
>
> Long time you did not read from me.
> I try a long time (and Google a lot) before I turn to you, but this time I
> do not find much.
>
> I am trying to quickly change the Excel formula style.
>
> Here is one of the many codes that I have tried.
> This one is the longest: I am pretty sure there are lots of unnecessary
> code in it.
>
> Public xReference As XlReferenceStyle
> Sub Switch_Reference_Style()
> 'shortcut CTRL w
>
>
> If xReference = xlA1 Then
> xReference = xlR1C1
> Application.ReferenceStyle = xlR1C1
>
> Exit Sub
> End If
>
> If xReference = xlR1C1 Then
> xReference = xlA1
> Application.ReferenceStyle = xlA1
> Exit Sub
> End If
> End Sub
>
> Each of those lines, tried separately, work.
> Application.ReferenceStyle = xlR1C1
>
> or
>
> Application.ReferenceStyle = xlA1
>
>
> But the quick switch does not.
>
> Thanks for you help
>
> Louise
>
>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1416 / Virus Database: 2109/4115 - Release Date: 12/31/11
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar