VBA Determining if a cell is Relative or Absolute

[Festus Hagen]

Hi all,

Excel 97, VBA ...

Is there a property or ??? to determine if a formula is absolute or relative??

ie: $A$1 or A1

Or does one have to look for the '$' ...

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~

 

[Leith Ross]

Hello Festus,

I am not really sure what you want to do but perhaps this help. Cell referencing is discretionary. It is the programmer's choice when writing formulas or code as to how a cell will be referenced.

In Excel, there are two referencing styles: A1 and R1C1. In A1 style, the dollar sign indicates if the column or row is absolute. In R1C1 style, a number follows either the R or C to indicate an absolute.

Sincerely,
Leith Ross

 

[Festus Hagen]

Hmmm ... Not sure I can explain it more clearly ...

Try this.
In VBA, I need to determine if an A1 notation formula in a cell is xlAbsolute or xlRelative!
Meaning does it look to a human like "$A$1" or "A1".

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~

 

[Leith Ross]

Hello Festus,

Can you give me an example?

Sincerely,
Leith Ross

 

[Festus Hagen]

Hello Festus,

I am not really sure what you want to do but perhaps this help. Cell referencing is discretionary. It is the programmer's choice when writing formulas or code as to how a cell will be referenced.

In Excel, there are two referencing styles: A1 and R1C1.In A1 style, the dollar sign indicates if the column or row is absolute. In R1C1 style, a number follows either the R or C to indicate an absolute.

Sincerely,
Leith Ross

Leith ... In your words above you state

In A1 style, the dollar sign indicates if the column or row is absolute

That is what I want to determine .... They are already in A1 notation (style) and I need to know if they are Absolute (have a dollar sign) or Relative (Do not have a dollar sign)

pseudo code ...
if Range.Formula = xlAbsolute Then
MsgBox "Yea Absolute"
Else
MsgBox "Wrong, It's Relative"
End if

-Enjoy
fh : )_~

 

[Leith Ross]

Hello Festus,

A formula can contain both types of references and there is no property or method to inform you if the formula is absolute, relative, or mixed. The best you could do is create a VBA macro to examine the formula for "$" characters. Still this won't be 100% accurate as both types may still be present in the formula.

Function IsAbsPresent(ByVal xlFormula As String) As Boolean
  Dim I as Long
    I = InStr(xlFormula, "$")
    If I > 0 Then IsAbsPresent = True
End Function

Sincerely,
Leith Ross

 

[Scott Huish]

You should probably make that Volatile and the rest can be reduced to 1 line. I would pull it from a formula other than a string though.

Function IsAbsPresent(r As Range) As Boolean
Application.Volatile
If r.HasFormula Then IsAbsPresent = InStr(r.Formula, "$")
End Function

 

[Oaktree]

I'm not sure I like the approach of looking for the $ since a cell containing the string "I have $10" would imply that there's an absolute reference in that cell [edit: or, perhaps more relevant, =TEXT(A1,"$0.00"), which has a relative reference that would show as absolute].

I suppose you could compare:

YourRange.Formula

against

Application.ConvertFormula (Formula:=YourRange.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

Application.ConvertFormula (Formula:=YourRange.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Application.ConvertFormula (Formula:=YourRange.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

Application.ConvertFormula (Formula:=YourRange.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

And figure out which one it matches?

 

[Festus Hagen]

Thank you!

That's not the answer I was hoping for, I was hoping for a better solution!

For my case, that's all I'm concerned about and they are always the same ... A1 notation and either Absolute or Relative ... ie: have Dollars signs or not!

I'm already using:

if instr(ActiveCell.Formula, "$")  Then
  'It's Absolute ...
Else
  It's Relative ...
end if

NOTE:
Oaktree, You must have posted while I was posting this, I have not looked at your solution yet.

-Enjoy
fh : )_~