kittronald expressed precisely :
Garry,
Ok, what I was doing wrong was entering the formula in a cell and naming
it "Fill_Formula" instead of entering the formula in Fill_Formula's Refers
to: field in the Name Manager.
Your fill process works now and is very fast ! 8)
Unfortunately, storing the formula in the Name Manager complicates a
process we touched on in the thread "Iterating through a ComboBox's values".
A macro called Macro_Automate would iterate through a list of ComboBox
values and end when the last value was evaluated.
In each iteration, the Macro_Change_Function would change the function in
the Fill_Formula to the next value in the ComboBox (i.e., from SUM to
PRODUCT).
Before when I was storing the Fill_Formula in a cell, the
Macro_Change_Function was changing the formula's function via:
With Sheets("Data")
x = .Range("First_Data_Cell").Formula
y = Application.Substitute(x,
Sheets("Settings").Range("Current_Function"),
Sheets("Settings").Range("Selected_Function"))
.Range("First_Data_Cell").Formula = y
End With
Now that the Fill_Formula is stored in the Name Manager, I'm not sure how
I can change the Fill_Formula function programmatically.
Is there a VBA method to modify a name's Refers to: formula similar to
the Application.Substitute code above ?
That's not how I'd do it! That said, I propose a different approach:
put your various formulas into string constants (with global scope) in
your VBA project. However you get the next item in the combobox, use a
Select Case construct to modify the RefersTo property of "Fill_Formula"
as follows...
Module level variable declaration:
Dim msNewFormula As String
In the sub to change the formula:
ActiveSheet.Names("Fill_Formula").RefersTo = sNewFormula
...where your Macro_Change_Function (or however you do this) loads the
appropriate string constant into sNewFormula...
However you get the next item in the combobox:
Select Case ComboBox1.Text
Case "Item0": msNewformula = gsFILL_FORMULA_0
Case "Item1": msNewformula = gsFILL_FORMULA_1
'...
End Select
...where the constants prefixed with 'gs' (g=global,s=string) are
declared as follows:
In a standard module:
Public Const gsFILL_FORMULA_0 As String = "=blah0"
Public Const gsFILL_FORMULA_1 As String = "=blah1"
...and so on.
I suggest that you turn automatic calculation off BEFORE updating
RefersTo, then turn it back on when you're done making changes. This
means you won't have to re-place the existing cell formulas as they
will automaticall update when you change RefersTo. However, cells won't
update until you turn automatic calculation back on.
Optionally, you can force recalc by...
Range(<targetcells>).Calculate
...where it suits what you're doing so Excel doen't have to recalc the
entire workbook.<g>
