cell reference vba Excel - Why does it reference the sheet as well?

[vityata]

Hello great, friendly & helping people!
I have the following question with VBA:

I am using cell references, which I usually refer like this:

[irr_cf_at].FormulaR1C1 = "=ma_irr_cf_at"

However, due to some strange reason, there are a few, which do not want to be referred that way!
They want to be referred as following:

[irr_cf_at].FormulaR1C1 = "=master!ma_irr_cf_at"

Why should I add the sheet as well? It seems like the cell reference is declared somehow locally, but how did this happened?
I do not like it, because it makes my code inconsistent. Any ideas?

 

[pgc01]

Hi

Check if the name ma_irr_cf_at is a worksheet name or a workbook name.

(You can check it in Ribbon->Formulas->Name Manager->Scope)

If it's a worksheet name and you want to refer to it from another sheet you need to qualify it.

 

[vityata]

@pgc01, you are right.

Here comes the following interesting issue - as far as I am playing with my worksheet quite a lot, deleteing and clearing plenty of ranges, I am defining these variables with VBA with the following code:

tbl_master.Names.Add Name:="ma_irr_cf_at", RefersTo:="=master!$" & letter_col(3) & "$" & irr_calculation_start_row + 12 & ""

because that is the code that I managed to translate from the VBA recorder. Is there a way to record these references as worksheet references?
Because in the code, it mentions "master"?

Long story short - my question is - is there a way to define a named range as a workbook named range through vba?

 

[pgc01]

Hi

You just have to qualify Names correctly.
See if this test helps. See how Sheet2!A1:A2 refer to different ranges.

Sub Test()

ActiveWorkbook.Names.Add Name:="TestR", RefersTo:=Worksheets("Sheet3").Range("A1")
Worksheets("Sheet1").Names.Add Name:="TestR", RefersTo:=Worksheets("Sheet1").Range("A1")

Worksheets("Sheet1").Range("A1").Value = "Value in Sheet1"
Worksheets("Sheet3").Range("A1").Value = "Value in Sheet3"

With Worksheets("Sheet2")
    .Range("A1").Formula = "=TestR"
    .Range("A2").Formula = "=Sheet1!TestR"
End With

End Sub

 

[vityata]

Actually, my macro just exploded a few times, so I will keep it with the lousy code But you really helped me!

(I always try to avoid Active things in my VBA, thus, the old incosistent way should stay)

 

[pgc01]

I'm glad it helped. I hope you find the bug.