See if this will work for you. Onenote: I renamed your variable "Worksheet" to WorksheetNameParameter in order to avoid any possible glitches.
Function CalcOff(Condition, Optional WorksheetNameParameter As Worksheet)
If WorksheetNameParameter = "" Then
WorksheetNameParameter = ActiveSheet.Name
Else
'WorksheetNameParameter was valued, so check that it is a valid name
Dim ws As Worksheets
Dim WorksheetFound As Boolean: WorksheetFound = False
For Each ws In Worksheets
If WorksheetNameParameter = ws.Name Then
WorksheetFound = True
Exit For
End If
Next ws
If WorksheetFound = False Then
MsgBox "The worksheet name is not valid." & vbCrLf & _
"Enter a valid worksheet name or remove the parameter to use the current worksheet."
End If
End If
' old line Worksheet = Range("$Z$38").Value
On Error GoTo ErrHandler
If Evaluate(Condition) Then
' old line Worksheets(Worksheet).EnableCalculation = False
Worksheets(WorksheetNameParameter).EnableCalculation = False
CalcOff = "CalcOff"
Exit Function
End If
ErrHandler:
' old line Worksheets(Worksheet).EnableCalculation = True
Worksheets(WorksheetNameParameter).EnableCalculation = True
CalcOff = "CalcOn"
End Function
-----Original Message-----
From: rajarjrtj@yahoo.com
Sent: Saturday, March 22, 2014 9:24am
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] VBA EnableCalculation Clean Up
Hi All,
I needed a way to turn-off calculation on a single worksheet within a large workbook which is connected to an API, and cobbled together the below VBA code, which works, but is ineloquent and also references directly a cell, which I know is bad practice for a UDF. I know just enough about VBA to be dangerous.
What I'm trying to accomplish, but haven't had success at yet, is to modify the code so that the worksheet name can be directly entered into the function formula instead of entering the worksheet's name into cell $Z$38, for example =CalcOff("Sheet1"), and also have "CalcOff & (the affected worksheet's name)" for example "CalcOff Sheet1", as the non-error output in the cell that has the function formula input?
Any guidance would be greatly appreciated.
Thank You
<
Function CalcOff(Condition)
Worksheet = Range("$Z$38").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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar