Programmatically retrieving the cell formula expression in terms of custom cell names

[DonSnow]

Hi,

I am writing the custom cell names and their corresponding formulas to a csv file. In the attached excel sheet, all relevant cells including the formula cells have existing and customized cell names, namely, NewPred_1A, NewPred_1B, NewPred_1C, and NewPred_1D. My goal is to retrieve the expression of the cell location D1(NewPred_1D) in terms of custom names of the other three cells, NewPred_1A, NewPred_1B and NewPred_1C. Currently, the expression for D1(or NewPred_1D) is '=A1+B1+C1' by default even if there are custom names for the cells.

What will I do such that D1 will have an expression of '=NewPred_1A + NewPred_1B + NewPred_1C' without manually updating the expression? How can we assign the formula expression of D1 to a variable? I tried this:

FormulaVar = Worksheets(ActiveSheet.Name).Cells(r, c).Formula but didn't work.

 

[Zot]

If your have FormulaVar as String, then
FormulaVar = Worksheets(ActiveSheet.Name).Cells(r, c).Formula
should work just fine as I tested it. At least on my Excel 2016

 

[DonSnow]

If your have FormulaVar as String, then
FormulaVar = Worksheets(ActiveSheet.Name).Cells(r, c).Formula
should work just fine as I tested it. At least on my Excel 2016

Thanks for the reply. The formula still showed the '=A1+B1+C1'. How can we tweak the code such that the D1 formula will show the custom names of A1, B1 and C1?

Currently:

Goal:

Such that if we print the FormulaVar, it should have '=NewPred_1A+NewPred_1B+NewPred_1C' value

 

[DonSnow]

Thanks for the reply. The formula still showed the '=A1+B1+C1'. How can we tweak the code such that the D1 formula will show the custom names of A1, B1 and C1?

Currently:
View attachment 37722

Goal:
View attachment 37723

Such that if we print the FormulaVar, it should have '=NewPred_1A+NewPred_1B+NewPred_1C' value

Correction: The FormulaVar variable should get the string '=NewPred_1A+NewPred_1B+NewPred_1C' even if the formula box still shows '=A1+B1+C1'

 

[Zot]

Correction: The FormulaVar variable should get the string '=NewPred_1A+NewPred_1B+NewPred_1C' even if the formula box still shows '=A1+B1+C1'

Did you named the ranges A1, B1, C1 as NewPred_1A, NewPred_1B, NewPred_1C respectively? If you do, when you select A1, the Name Box should show NewPred_1A. If not, it will show A1.
You can use Formula>Name Manager to name each cell. Easier way is to just click A1, then Select the name A1 in the Name Box and type NewPred_1A, So, when you select A1, NewPred_1A will be displayed.

 

[DonSnow]

Did you named the ranges A1, B1, C1 as NewPred_1A, NewPred_1B, NewPred_1C respectively? If you do, when you select A1, the Name Box should show NewPred_1A. If not, it will show A1.
You can use Formula>Name Manager to name each cell. Easier way is to just click A1, then Select the name A1 in the Name Box and type NewPred_1A, So, when you select A1, NewPred_1A will be displayed.

Yes I named the ranges A1, B1, C1 as NewPred_1A, NewPred_1B, NewPred_1C respectively. Also the D1 to NewPred_1D. But the formula variable for NewPred_1D still shows

What code tweaking is needed such that the formula variable will be assigned NewPred_1A + NewPred_1B + NewPred_1C and not A1+B1+C1?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Programmatically retrieving the cell formula expression in terms of custom cell names
and How to display the expression in a formula cell by the defined names of reference cells?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

Also here: In Excel formula cell, is there a way to programmatically change the expression from using default variables to customized cell names?
(just added)

 

[Zot]

Yes I named the ranges A1, B1, C1 as NewPred_1A, NewPred_1B, NewPred_1C respectively. Also the D1 to NewPred_1D. But the formula variable for NewPred_1D still shows
View attachment 37730


View attachment 37729

What code tweaking is needed such that the formula variable will be assigned NewPred_1A + NewPred_1B + NewPred_1C and not A1+B1+C1?

Then I have no idea. I have no Excel 365 but 2016