VBA Compatibility Problem 2003 to 2016

[billpq]

I have a macro that I’ve been using in Excel 2003 for years that works flawlessly. It creates a formula using R1C1 references and copies it then pastes it to other cells, etc.

On a new computer with Excel 2016 the formula comes out wrong as it doesn’t seem to interpret the R1C1 the same way as 2003.

Any suggestions? I went into Options and enabled R1C1 but that didn’t fix it.

Is there a VBA setting I’m missing?
Is there a better way to convert the script from 2003 to 2016 than just opening it, running the compatibility mode feature and then re-opening it as a .xlsm?
Are there other compatibilty issues between 2003 & 2016 that I can find a list of for other files I use frequently?

Thanks for any help!

 

[Zubair Muhammad]

Could you provide Code Snippets that are causing problem?

Is the Excel 2016, 32 bit or 64 bit?

 

[billpq]

Zubair, thank you!

It is 32bit.

Here's the part that is being interpreted differently between 2003 & 2016:

"=UPPER(IF(VLOOKUP(R" & intOrderDataRow1 & "C" & intIndexColNum & "," & wbExternal.Name & "!OrdersData,1)=R" _
& intOrderDataRow1 & "C" & intIndexColNum & ",VLOOKUP(R" & intOrderDataRow1 & "C" & intIndexColNum _
& "," & wbExternal.Name & "!OrdersData," & intExternalColNum & "),""""))"

 

[Zubair Muhammad]

Hi Bill,

The Excel formula setting itself ..... Is it R1C1 reference style......... Goto File>>>Options>>>Formulas>>>Working with Formulas. See if the RICI reference style is checked or not.

 

[billpq]

Zubair,

As I mentioned in my initial post, I had already tried switching to R1C1 in Options, but that still gave me incorrect results. Below you can see how it is interpreted with that box checked and unchecked (both in Excel 2016) and also the way it has always worked using Excel 2003 (this is what I want!).

I added a few Msgbox lines to see what the values are of some of the variables before the formula is created. I added the results in brackets so you can see the results:

MsgBox intIndexColNum [1]
MsgBox wsPrevious.Name [Previous]
MsgBox intUsedColumns [43]
MsgBox rngOrdersColumn.Column [17]

rngOrdersColumn.Offset(1, 0).Value = "=IF(RC" & intIndexColNum & _
"="""","""",IF(NOT(ISNA(VLOOKUP(RC" & intIndexColNum & "," & wsPrevious.Name & _
"!C1:C" & intUsedColumns & "," & intIndexColNum & ",FALSE))),IF(VLOOKUP(RC" & intIndexColNum & "," & wsPrevious.Name & "!C1:C" & intUsedColumns & "," & _
rngOrdersColumn.Column & ",FALSE)=0,"""",VLOOKUP(RC" & intIndexColNum & "," & _
wsPrevious.Name & "!C1:C" & intUsedColumns & "," & _
rngOrdersColumn.Column & ",FALSE)),""""))"

With R1C1 unchecked, this is the formula being entered into cell Q11:

=IF(RC1="","",IF(NOT(ISNA(VLOOKUP(RC1,Previous!C1:C43,1,FALSE))),
IF(VLOOKUP(RC1,Previous!C1:C43,17,FALSE)=0,"", VLOOKUP(RC1,Previous!C1:C43,17,FALSE)),""))

With R1C1 Checked, this is the formula being entered into cell Q11:

=IF(R[-10]C[454]="","",IF(NOT(ISNA(VLOOKUP(R[-10]C[454],
Previous!R[-10]C[-14]:R[32]C[-14],1,FALSE))),
IF(VLOOKUP(R[-10]C[454],Previous!R[-10]C[-14]:R[32]C[-14],17,FALSE)=0,"",
VLOOKUP(R[-10]C[454],Previous!R[-10]C[-14]:R[32]C[-14],17,FALSE)),""))

And this is the result I want, and the way it is entered into cell Q11 using the exact same macro in Excel 2003:

=IF($A11="","",IF(NOT(ISNA(VLOOKUP($A11,Previous!$A:$AQ,1,FALSE))),
IF(VLOOKUP($A11,Previous!$A:$AQ,17,FALSE)=0,"",
VLOOKUP($A11,Previous!$A:$AQ,17,FALSE)),""))

Again, I thank you (and anyone else who might help) for your time and suggestions!

 

[RoryA]

Hi,

Can you post the part that actually assigns the formula to the cell, please? Are you using the FormulaR1C1 property? (you should be)

 

[billpq]

Hi Rory, I believe the part you are looking for are the last couple lines where it copies it to the rest of the column, and then converts to values.

Thanks!



rngOrdersColumn.Offset(1, 0).Value = "=IF(RC" & intIndexColNum & _
"="""","""",IF(NOT(ISNA(VLOOKUP(RC" & intIndexColNum & "," & wsPrevious.Name & _
"!C1:C" & intUsedColumns & "," & intIndexColNum & ",FALSE))),IF(VLOOKUP(RC" & intIndexColNum & "," & wsPrevious.Name & "!C1:C" & intUsedColumns & "," & _
rngOrdersColumn.Column & ",FALSE)=0,"""",VLOOKUP(RC" & intIndexColNum & "," & _
wsPrevious.Name & "!C1:C" & intUsedColumns & "," & _
rngOrdersColumn.Column & ",FALSE)),""""))"
rngOrdersColumn.Offset(1, 0).Copy
Range(rngOrdersColumn.Offset(1, 0), Cells(lastRowStatic, rngOrdersColumn.Column)).PasteSpecial Paste:=xlPasteFormulas
Range(rngOrdersColumn.Offset(1, 0), Cells(lastRowStatic, rngOrdersColumn.Column)).Copy
Range(rngOrdersColumn.Offset(1, 0), Cells(lastRowStatic, rngOrdersColumn.Column)).PasteSpecial Paste:=xlPasteValues

 

[RoryA]

Change the first part to:

rngOrdersColumn.Offset(1, 0).FormulaR1C1 =

 

[billpq]

That did it! (once I unchecked R1C1 in options).

I have several other Excel 2003 files I haven't gotten to yet that I will soon be needing to open in 2016. Are there other compatibility issues such as adding the .FormulaR1C1 that I can see listed somewhere? Again, it runs fine in 2003 without needing .FormulaR1C1 so this is certainly a change to 2016 and if there are others, I'd love to learn about them.

Thank you for your help (Zubair too)!

 

[RoryA]

The problem is caused by the fact that there are more columns in a worksheet now, so a reference like RC13 is a valid A1 style address, as well as a valid R1C1 address. In all honesty, the code should either always have used R1C1, or used the full form of the relative reference R[0]C13 which cannot be misconstrued.

There are a few features that have been deprecated since 2003, like Application.FileSearch, but no definitive list that I am aware of. For the most part, (well written) code should just work in both versions.