2 Messages
Digest #3007
Messages
Sun Mar 16, 2014 3:28 pm (PDT) . Posted by:
rajarjrtj
Hi Randy,
To address my issue of very lengthy workbook calculation time, I wrote a little function that toggles off a single worksheet's calculation. It serves the purpose, but isn't particularly eloquent, so if you find it useful please error check and improve upon it if you wish.
In the affected worksheet, put in only RCHGetYahooHistory() arrays, or other data that you want to remain static. Upon opening the workbook, the affected worksheet will load, recalculate fully and populate with the current Yahoo, etc. data, and then the function will run and switch-off the worksheet calculations for the duration.
In cell B2 of the desired worksheet enter the worksheet name that is to be kept static, and in any other cell write the formula =calcoff(B2="Place the worksheet name here in quotes") . That is, enter the worksheet name within quotes that you had entered into cell B2. The formula cell will display "CalcOff" in the syntax is correct and "CalcOn" or "Value!" if there is an error.
Here is the VBA code:
Function CalcOff(Condition)
Worksheet = Range("$b$2").Value
On Error GoTo ErrHandler
If Evaluate(Condition) Then
Worksheets(Worksheet).EnableCalculation = False
CalcOff = "CalcOff"
Exit Function
End If
ErrHandler:
Worksheets(Worksheet).EnableCalculation = True
CalcOff = "CalcOn"
End Function
To address my issue of very lengthy workbook calculation time, I wrote a little function that toggles off a single worksheet'
In the affected worksheet, put in only RCHGetYahooHistory(
In cell B2 of the desired worksheet enter the worksheet name that is to be kept static, and in any other cell write the formula =calcoff(B2=
Here is the VBA code:
Function CalcOff(Condition)
Worksheet = Range("
On Error GoTo ErrHandler
If Evaluate(Condition) Then
Worksheets(Workshee
CalcOff = "CalcOff"
Exit Function
End If
ErrHandler:
Worksheets(Workshee
CalcOff = "CalcOn"
End Function
Sun Mar 16, 2014 6:44 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
What you're doing is beyond me. I didn't even know you could turn off
recalculation on a worksheet by worksheet basis.
But if your problem is being caused by an instruction to "recalculate all",
that might override the worksheet setting.
On Sun, Mar 16, 2014 at 3:28 PM, <rajarjrtj@yahoo.com> wrote:
>
> To address my issue of very lengthy workbook calculation time, I wrote a
> little function that toggles off a single worksheet's calculation. It
> serves the purpose, but isn't particularly eloquent, so if you find it
> useful please error check and improve upon it if you wish.
>
> In the affected worksheet, put in only RCHGetYahooHistory() arrays, or
> other data that you want to remain static. Upon opening the workbook, the
> affected worksheet will load, recalculate fully and populate with the
> current Yahoo, etc. data, and then the function will run and switch-off the
> worksheet calculations for the duration.
>
> In cell B2 of the desired worksheet enter the worksheet name that is to be
> kept static, and in any other cell write the
> formula =calcoff(B2="Place the worksheet name here in quotes") . That
> is, enter the worksheet name within quotes that you had entered into cell
> B2. The formula cell will display "CalcOff" in the syntax is correct and
> "CalcOn" or "Value!" if there is an error.
>
> Here is the VBA code:
>
> Function CalcOff(Condition)
> Worksheet = Range("$b$2").Value
> On Error GoTo ErrHandler
> If Evaluate(Condition) Then
> Worksheets(Worksheet).EnableCalculation = False
> CalcOff = "CalcOff"
> Exit Function
> End If
> ErrHandler:
> Worksheets(Worksheet).EnableCalculation = True
> CalcOff = "CalcOn"
> End Function
>
recalculation on a worksheet by worksheet basis.
But if your problem is being caused by an instruction to "recalculate all",
that might override the worksheet setting.
On Sun, Mar 16, 2014 at 3:28 PM, <rajarjrtj@yahoo.com> wrote:
>
> To address my issue of very lengthy workbook calculation time, I wrote a
> little function that toggles off a single worksheet'
> serves the purpose, but isn't particularly eloquent, so if you find it
> useful please error check and improve upon it if you wish.
>
> In the affected worksheet, put in only RCHGetYahooHistory(
> other data that you want to remain static. Upon opening the workbook, the
> affected worksheet will load, recalculate fully and populate with the
> current Yahoo, etc. data, and then the function will run and switch-off the
> worksheet calculations for the duration.
>
> In cell B2 of the desired worksheet enter the worksheet name that is to be
> kept static, and in any other cell write the
> formula =calcoff(B2=
> is, enter the worksheet name within quotes that you had entered into cell
> B2. The formula cell will display "CalcOff" in the syntax is correct and
> "CalcOn" or "Value!
>
> Here is the VBA code:
>
> Function CalcOff(Condition)
> Worksheet = Range("
> On Error GoTo ErrHandler
> If Evaluate(Condition) Then
> Worksheets(Workshee
> CalcOff = "CalcOff"
> Exit Function
> End If
> ErrHandler:
> Worksheets(Workshee
> CalcOff = "CalcOn"
> End Function
>
Tidak ada komentar:
Posting Komentar