VBA Function Optional parameters

Change your sub and add ByVal

Public Sub CalculateMe(Optional ByVal strA As String, Optional ByVal strB As String)

Public Sub CalculateMe(Optional varA As Variant, Optional varB as Variant)

Excerpts from Chip Pearson's excellent explanation:

Rules governing the use of optional parameters:

• The Optional keyword must be present to make a parameter optional.
• The data type should be (but need not be, see below) a Variant datatype.
• The optional parameter(s) must be at the end of the parameterlist.
• The IsMissing function will work only with parameters declaredas Variant. It will return False when used with any other data type.
• User defined types (UTDs) cannot be optional parameters.

Example

Function Test(L1 As Long, L2 As Long, _    Optional P1 As Variant, Optional P2 As Variant) As String    Dim S As String    If IsMissing(P1) = True Then        S = "P1 Is Missing."    Else        S = "P1 Is Present (P1 = " & CStr(P1) & ")"    End If    If IsMissing(P2) = True Then        S = S & "  " & "P2 Is Missing"    Else        S = S & "  " & "P2 Is Present (P2 = " & CStr(P2) & ")"    End If    Test = SEnd Function

Here, both L1 and L2 are required but P1 and P2 are optional. Since both are Variant types, we can use IsMissing to determine whether the parameter was passed in. IsMissing returns True if the Variant parameter is omitted, or False if the Variant parameter is included. If the data type of the optional parameter is any data type other than Variant, IsMissing will return False.

Instead of CalculateMe(,strB) you can use

dblA = CalculateMe strB:="B"