Replacing Repeated Formula within Code. How??

[monirg]

Hello;
I've a reference-contained formula repeated exactly in numerous VBA macros in the same code:

Sub Test_1()
' ...... code 1
    Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1)
    myFormula.Formula = "=C21*$D$18^D21*$E$18^E21*$F$18^0.35*$G$18^G21/$H$18^H21*$I$18^I21"
' ...... code 11
End Sub

Sub Test_2()
' ...... code 2
    Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1)
    myFormula.Formula = "=C21*$D$18^D21*$E$18^E21*$F$18^0.35*$G$18^G21/$H$18^H21*$I$18^I21"
' ...... code 22
End Sub
'etc

To change or modify the above formula, the code is searched and the same changes are made to the same formula everywhere!!
That's clearly not an efficient way, besides it does not preclude the possibility of missing some!
Can I place the formula statement, and only the formula statement:

myFormula.Formula: = "=C21*$D$18^D21*$E$18^E21*$F$18^0.35*$G$18^G21/$H$18^H21*$I$18^I21"

in a separate macro, and make reference to the macro in the corresponding procedures: Sub Test_1(), Sub Test_2(), ..., etc. ???
How ???
This way, changes/modifications to the formula could be made in one location only.

Thank you kindly for your help.

 

[Lewiy]

Perhaps you could declare a global string variable and allocate your formula to it within the Workbook_Open event. You can then refer to this variable everywhere you want to place the formula and it will only exist in the Workbook_Open event to be modified.

 

[pgc01]

Hi Monir

Another way is to define a Sub where you make the assignment, like:

Sub AssignFormula(rFormula As Range)
    rFormula.Formula = "=C21*$D$18^D21*$E$18^E21*$F$18^0.35*$G$18^G21/$H$18^H21*$I$18^I21"
End Sub

This way you just call it from anywhere:

Sub Test_1()
' ...... code 1
    Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1)
    Call AssignFormula(myFormula)
' ...... code 11
End Sub

Sub Test_2()
' ...... code 2
    Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1)
    Call AssignFormula(myFormula)
' ...... code 22
End Sub

Hope this helps
PGC

 

[Norie]

Monir

Why do you have 20 different subs with only a couple of lines of code?

 

[monirg]

Hi;

Lewiy: For now, I'm trying to stay away from changing/modifying the existing Workbook_Open event. It was quite difficult to get working correctly!!

pgc01: I've just tried your suggestion by defining the formula in a Sub. It didn't work, producing a combination of #DIV/0!, 1.e+14, 0.00, #NUM!, and wrong results!
Shouldn't there be (in addition) some kind of a string declaration to the result of rFormula.Formula, in line with Lewiy's suggestion (but w/o the w/b open event ??

Norie: In all likelyhood, more code would've confused the issue, beside there're only 12 different Subs using the posted formula. (Did I mention 20 Subs earlier by mistake ?? My apologies)

Regards.

 

[Norie]

Monir

You didn't mention 20 subs, but in my old age my eyes multiply things by random numbers.

But they seem to still let me see that you appear to repeating code with only minor changes, which might not be needed.

 

[monirg]

pgc01: You're correct and thank you for your suggestion.

Define a Sub where you make the assignment, like:

Sub AssignFormula(rFormula As Range) 
    rFormula.Formula = "=C21*$D$18^D21*$E$18^E21*$F$18^0.35*$G$18^G21/$H$18^H21*$I$18^I21" 
End Sub

This way you just call it from anywhere:

Sub Test_1() 
' ...... code 1 
    Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1) 
    Call AssignFormula(myFormula) 
' ...... code 11 
End Sub 

Sub Test_2() 
' ...... code 2 
    Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1) 
    Call AssignFormula(myFormula) 
' ...... code 22 
End Sub

It works fine, and it was my mistake! The problem was that one of the critical dollar sign ($) in the formula had (mysteriously!) been left out in the process of copy/paste to the separate Sub. Because that part of the formula was not properly anchored, hence the erroneous results!

Norie: Yes, I agree! There's certainly some repetiton in the code, and I'll try later (hopefully with your help!) to minimize such repetition once I've the entire program working as desired.

Thank you all for your contributions in resolving the issue.