Excel VBA and Reference Styles

I encountered something new with Excel reference styles (i.e., A1 vs. R1C1) the other day.  I've mostly used the reference styles when using the Cells.Formula or Cells.FormulaR1C1 properties...for these, you use A1 for the former and R1C1 for the latter, and most importantly, it doesn't matter what reference style you've actually specified in the workbook - Excel just figures it out.

The other day, I tried to use the Range.Validation.Modify function in VBA for the first time.  As is my custom (for ease of programming), I had set the reference style of the workbook to R1C1 before starting.

  

However, not thinking much about it, in the formula of the Modify command, I used A1 style - mostly because Excel had never cared which way I went with the Formula properties, also because I figure this is the style the end user will have.

BIG problem, evil error...I got a nondescript '400' error message.  Debugging showed that the error description was "Application-defined or object-defined error"...big help there.  Hours and hours of Googling were no help.

 

At long last, I thought to check the reference style.  Voila!  When I changed the reference style to R1C1, the code worked!  So, unlike the Formula/FormulaR1C1 property of Cells, apparently Excel will NOT convert your reference style on the fly for the Formula1 property of the Range.Validation.Modify method.

 

So, this wasn't working:

    Dim userValidation As Validation
    Set userValidation = ActiveSheet.Range("C1").Validation

    userValidation.Modify xlValidateList, xlValidAlertStop, _
        xlBetween, "=Lists!$G$2:$G$20"

 

BUT this does:

    Dim userValidation As Validation
    Set userValidation = ActiveSheet.Range("C1").Validation

    userValidation.Modify xlValidateList, xlValidAlertStop, _
        xlBetween, "=Lists!R2C7:R20C7"

 

(when the workbook is set to R1C1 notation)

 

Of course, not knowing what the end user might have, it's necessary to do a check:

Dim userValidation As Validation
Set userValidation = ActiveSheet.Range("C1").Validation

If Application.ReferenceStyle = xlA1 Then

userValidation.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=Lists!$G$2:$G$20"

Else 'ReferenceStyle = xlR1C1

    userValidation.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=Lists!R2C7:R20C7"

End If

 

I hope this saves some other poor person from hours of Googling!  It is really such a simple thing, but it didn't occur to me for so long!