Re: [ExcelVBA] Re: VBA EnableCalculation Clean Up

 

Dear rajarjrt

Probably VB does not like Dim ws As Worksheets in the plural.

Here is how you tell if a worksheet exists :-

Public Function WorksheetExists(sSheetName As String) As Boolean

    On Error Resume Next

    WorksheetExists = Not (ActiveWorkbook.Worksheets(sSheetName) Is Nothing)

    On Error GoTo 0

End Function

In your code you do not want to test for the active worksheet because I think you are wanting to turn off calculations there which means that you will also turn off UDFs including the one you are writing.

The Optional WorksheetNameParameter As String is pointless because you always want to write the cell formula in the same way.

 Here is some more code :-

Public Function CalcOff(Condition as     variant     ,  sWorksheetName As String) As Variant

Dim bEvaluation As Boolean, bWorksheetExists As Boolean

    bEvaluation = True '   Evaluate(Condition) ''' FUDGE

    bWorksheetExists = WorksheetExists(sWorksheetName)

    Select Case True

        Case bWorksheetExists And bEvaluation:         CalcOff = "CalcOff"

        Case bWorksheetExists And Not bEvaluation:     CalcOff = "CalcOn"

        Case Not bWorksheetExists:                     CalcOff = "No Sheet"

    End Select

End Function

I have no idea what your Evaluate does and what Condition is so you will have to take out my fudge.

I will leave it to you to put in the code to enable or disable calculations. Probably something like this:-

Worksheets(sSheetName).enabled = CalcOff = "CalcOff"

Last, in your code you call the Msgbox function. Don't do that in a UDF.

Regards

Derek Turner

England

+++

---

From: "rajarjrtj@yahoo.com" <rajarjrtj@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Friday, 25 April 2014, 19:58
Subject: [ExcelVBA] Re: VBA EnableCalculation Clean Up

 

I'm at an impasse having tried every machination of code that I could think of to make the UDF work, even after incorporating Tim's mods. 

 

I would greatly appreciate any guidance in debugging the below code.

 

<<<<

Function CalcOff(Condition, Optional WorksheetNameParameter As String)
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 Worksheets(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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (8)

----------------------------------
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

----------------------------------

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___