Subroutine sets range.formula to call a function

i have the following problem:
1. I have a subroutine that populates cell formulas (recreating a manual model i built)

    workingColumn.Columns(wc).FormulaR1C1 = "=numSlots(RC[" & toEight & "], RC[" & selectedNumPorts & "], """ & myNodeName & """)"

2. this will place a formula in the cell that calls "=numSlots(H2,K2,M1)"

3. upon debugging, i find that the subroutine, right after it sets the formula for that cell, breaks
4. and excel begins to run the formula which was just set on the cell.
5. this formula then breaks because the cells it needs as arguments don't have values yet!

Question:

when I set the formula for a cell, Can I do something prior to that so that the worksheet doesn't try to calculate the formula?

i'm looking, and have been looking, for something along the lines of "Range("x").calculations = FALSE"

I tried setting my workbook to do manual calculations but it just keeps on calculating the formula in that cell automatically!


any help is appreciated!

--------
excel 2010 Mac

Try this before your macro starts:

Application.Calculation = xlManual

The don't forget to turn it back on:

Application.Calculation = xlAutomatic

You can then force a calculation with:

Activesheet.calcualte

Thanks! i just tried it in 2 ways. I created a parent Subroutine "control" that calls my subroutine "defineRanges" that populates the formula that calls the function

Sub control()
    Application.Calculation = xlManual
    Call DefineRanges
    Application.Calculation = xlAutomatic
    Worksheets("2011").Calculate
End Sub

then I also tried, just within "defineRanges" to make the Application.Calculation changes at the very beginning and at the very end.
Didn't work in either case.

then I looked into populating the value instead of the formula but from the other forums, it seems like that will just yield the same result.
i just need a way to finish running the subroutine before any of these formulas calculate.

thanks again.

Originally Posted by weirdaze

Thanks! i just tried it in 2 ways. I created a parent Subroutine "control" that calls my subroutine "defineRanges" that populates the formula that calls the function

Sub control()
    Application.Calculation = xlManual
    Call DefineRanges
    Application.Calculation = xlAutomatic
    Worksheets("2011").Calculate
End Sub

then I also tried, just within "defineRanges" to make the Application.Calculation changes at the very beginning and at the very end.
Didn't work in either case.

then I looked into populating the value instead of the formula but from the other forums, it seems like that will just yield the same result.
i just need a way to finish running the subroutine before any of these formulas calculate.

thanks again.

I Just noticed that this actually works earlier in the code when I refer to a cell that is already populated.
the problem above occurs because i'm referring to a cell that has a validation that has not been activated (drop down)

workingColumn.Columns(9).FormulaR1C1 = "=numPortsForty(RC[-4],RC[-1])"
            workingColumn.Columns(10).FormulaR1C1 = "=numPortsTen(RC[-5],RC[-2])"
            workingColumn.Columns(11).FormulaR1C1 = "=numPortsHundred(RC[-6],RC[-3])"

when I try to run he subroutine, the reason it breaks is because one of my operations has an error from referring to the empty cell.

I will try to get the validation to populate a default value so I can move forward.