Can a function behave differently based on the calling method?

[AndyTampa]

I'm working on this function to validate dates. I've written the function to be called from certain CommandButtons and from another function. I want this function to ask for a proper FTD if it was called from the CommandButtons and ask for a proper PTD if it was called from the MTBalPTD function.

Answers I've found are beyond my understanding. I've tried ChatGPT and it keeps giving answers that don't work. Then it keeps trying workarounds that get exponentially longer the more it tries.

I'm trying to use Application.Caller. I've used it in If statements. I've used it in Case Select statements. I'm getting Type Mismatch errors, Error 2023, and Run-time error '424' Object Required all depending on the different variations I've tried.

This is the last variation I tried before my eyes shut down for the night.

    Dim callerName As String        ' What called this function?
    
    callerName = Application.Caller
    
    Select Case True
        Case callerName Like "*MTBalPTD*"
            promptType = "PTD"
        Case callerName Like "CommandButton*"
            promptType = "FTD"
        Case Else
            promptType = "date"
    End Select

This function already has so many variables, I was hoping to prevent adding one and if possible, getting rid of the one I added here. Can someone make this mud a little clearer?

 

[Rick Rothstein]

I'm not sure if it will make a difference, but where are you CommandButtons located... on a worksheet or in a UserForm? If on a worksheet, is it a Form's button or ActiveX button?

 

[AndyTampa]

I'm not sure if it will make a difference, but where are you CommandButtons located... on a worksheet or in a UserForm? If on a worksheet, is it a Form's button or ActiveX button?

I don't have the skillset yet to make userforms. These CommandButtons are just ActiveX buttons placed on a spreadsheet. They are specifically used to fill in a particular template cell with sentences that are continuously repeated. I'm making the spreadsheet a little sexier by asking for certain variable information if I have not already provided it. This has presented new challenges. One of which is getting a function that can be used by more than one button.

So...both the function MTBalPTD and a few CommandButtons will call this function and pass a variable "chkDate" and then receive a validated or corrected chkDate back for the calling source to use.

 

[rlv01]

Instead of trying to get application.caller to behave, why not add a 'CallerType' parameter to your function and code your CommandButton(s) and MTBalPTD functions to identify themselves to your chkDate function?

Conceptually:

Function MyDateCheckFunction(chkDate As Date, Optional CallerType) As String
    Dim PromptType As String

    If IsMissing(CallerType) Then
        CallerType = ""
    End If

    Select Case CallerType
    Case "PTD"
        PromptType = "PTD"
    Case "CMD"
        PromptType = "FTD"
    Case Else
        PromptType = "Date"
    End Select

    MsgBox "Prompt Type: " & PromptType

    MyDateCheckFunction = Date + 1
End Function

Private Sub CommandButton1_Click()
    Dim MyDate As Date

    MyDate = MyDateCheckFunction(Date, "CMD")
End Sub

Function MTBalPTD() As Date
    Dim MyDate As Date

    MTBalPTD = MyDateCheckFunction(Date, "PTD")
End Function

 

[AndyTampa]

You're suggesting to update all the relevant buttons and the MTBalPTD function to pass another variable for the promptType. This way the date function doesn't even need to know the caller and I won't even need the Case command. It's interesting, but I was hoping to do it all in the one function.

What is the difficulty getting Application.Caller to work? I mean besides my inability to get many things to work.

 

[RoryA]

Application.Caller will not work with the methods you are using to call the function.

 

[AndyTampa]

Application.Caller will not work with the methods you are using to call the function.

Could you clarify that? What methods are wrong, what would be right, etc?

I tried what I think riv01 was talking about and hit yet another snag.

This is the end of the MTBalDue function:

    If ValidDate(chkDate, promptType) Then
        Range("HICSPTD") = chkDate
Debug.Print "MTBalDue chkDate:", chkDate
    Else
        Range("HICSPTD").ClearContents
    End If
     
MsgBox "This is the end."

This is the beginning of the ValidDate function

Function ValidDate(ByVal chkDate As Variant, ByVal promptType As String) As Boolean

And this is the end of ValidDate after it makes sure the year is 4 digits

    If ValidDate Then
        ' Update chkDate with a 4-digit year if it's represented with 2 digits
        If Len(dateParts(2)) = 2 Then

            ' Convert years within the next five years to the current century
            If yearValue >= 0 And yearValue <= (year(Now) Mod 100) + 5 Then
                dateParts(2) = yearValue + (Int(year(Now) / 100) * 100)

            Else
            ' Convert years within the next five years to the current century
                dateParts(2) = yearValue + (Int(year(Now) / 100) * 100) - 100

            End If
            chkDate = Join(dateParts, "/")

        End If
Debug.Print "dateParts(2):", dateParts(2)
Debug.Print "Final ValidDate chkDate:", chkDate

    End If
    
End Function

BTW, chkDate is defined As Variant in MTBalDue.

At the end of ValidDate, chkDate is correct. In MTBalDue, it's blank.

 

[AndyTampa]

I figured that part out and removed ByVal from both variables.

I'd still like to know what you found wrong when I used Application.Caller.

 

[rlv01]

I tried what I think riv01 was talking about and hit yet another snag.

I don't understand what you mean by a 'snag'. Can you post your entire Function ValidDate instead of just code snippets?

 

[RoryA]

Application.caller will only work if your function is called from a cell or from a button/shape where it was assigned as a macro.