Basic VBA quick question

[superskid]

I have really only used macros via recording them, or recording them and editing the code.

I have a workbook that has one sheet that takes forever to calculate, and I only need to calculate it when I want it to. Problem is when I first open the workbook I can get stuck waiting for that sheet to calculate.

I want to enter the code:

sheet3.EnableCalculation=False

3 questions:

1.) Where do I enter it? Do I choose VBA then this workbook and then just type it there?

2.) If the sheet is named will sheet3 still work as the name for it?

3.) Any other suggestions?
</pre>

 

[superskid]

Before someone answers, is there a way to not use sheet3, but the sheet name instead? In case I move the tabs around I don't want the sheets to get all messed up and have this set for the wrong tab,

 

[mrmmickle1]

You can reference sheets either like this:

Sheets("Your Sheet Name")

or like this:

Sheets(3)

You will want to click the module in the Project Explorer on the left of the VBE (visual Basic Editor) YourWorksheetName(Sheet3).

You can enter the code in the VBE Module for Sheet3 like this:

[COLOR=#0000ff]Sub[/COLOR] DisableCalc()
EnableCalculation = [COLOR=#0000ff]False[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Just remember if you do not re-enable calculation it will stay off.

If you want an easier way to do this you can go to the Formulas Tab and Click on Calculation Options. Change to Manual. (To Update Sheet calculations hit F9)

 

[superskid]

The reason I don't want to have it calculate on sheet 3 is that I have an excel add on that links to my accounting software. It gives me custom formulas that pull data from my software and it is VERY slow. However, if auto calculate is turned off I need to click on the addon bar at the top, choose my accounting software and then choose there fast recalculate sheet button.

I tried to record a macro of me doing this but it doesn't work. How can I create a macro to run this?

 

[mrmmickle1]

ADVERTISEMENT

Try running this sub procedure in the Sheet 3 module (By clicking the "Play" button or hitting F5 while your cursor is in the sub procedure):

[COLOR=#0000ff]Sub[/COLOR] DisableCalc()
EnableCalculation = [COLOR=#0000ff]False
[/COLOR][COLOR=#0000ff]End Sub[/COLOR]

Then do whatever you need to do with your accounting add-in


Then Run this sub procedure in the Sheet 3 module (By clicking the "Play" button or hitting F5 while your cursor is in the sub procedure):

[COLOR=#333333][COLOR=#0000ff]Sub[/COLOR] DisableCalc()
EnableCalculation = [/COLOR][COLOR=#0000ff]True
[/COLOR][COLOR=#0000ff]End Sub[/COLOR]

If this doesn't work then the Add-in may be changing auto-calculate on while you are using it. If this is the case you may not be able to get around Sheet 3 being on auto-calculate.

 

[superskid]

Is there anyway for me to check if it is the add-on that is turning on auto-calc because when I open the sheet currently sheet 3 still starts updating; however, if I have my excel options set to manual calculation it doesn't? Doesn't make sense?

 

[mrmmickle1]

Maybe you are looking for this setting instead?:

Application.Calculation = xlCalculationManual

Try using a combination of the following procedures:

1.Turn Calculation off, or to Manual or whatever your needs are
2. Test that it is indeed turned off.
3. Run Add-in
4. Test Calculation Values afterward (Then you know if the add-in turned it on).

[COLOR=#0000ff]Sub[/COLOR] SetManCalc()
    Application.Calculation = xlCalculationManual
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] SetAutoCalc()
    Application.Calculation = xlCalculationAutomatic
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] SetSemiAutoCalc()
    Application.Calculation = xlCalculationSemiautomatic
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub [/COLOR]TestAppCalculatuon()

[COLOR=#0000ff]     If [/COLOR]Application.Calculation = xlCalculationManual [COLOR=#0000ff]Then[/COLOR]
         MsgBox "Manual Calculation is Enabled"
[COLOR=#0000ff]     ElseIf [/COLOR]Application.Calculation = xlCalculationAutomatic[COLOR=#0000ff] Then[/COLOR]
         MsgBox "Automatic Calculation is Enabled"
[COLOR=#0000ff]     ElseIf [/COLOR]Application.Calculation = xlCalculationSemiautomatic [COLOR=#0000ff]Then[/COLOR]
         MsgBox "SemiAutomatic Calculation is Enabled"
[COLOR=#0000ff]     End If[/COLOR][COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] SetEnabCalcTrue()
    EnableCalculation = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub [/COLOR]SetEnabCalcFalse()
    EnableCalculation = [COLOR=#0000ff]False[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] TestEnCalc()

   [COLOR=#0000ff] If [/COLOR]EnableCalculation = [COLOR=#0000ff]False Then[/COLOR]
        MsgBox "Calculation is set to False"
    [COLOR=#0000ff]ElseIf [/COLOR]EnableCalculation = [COLOR=#0000ff]True Then[/COLOR]
        MsgBox "Calculation is set to True"
[COLOR=#0000ff]    End If[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]