CODEWITHSUNDEEP
excelvbafunction
David
David

Reputation: 1232

Excel: Custom function that fixes itself?

Long story short, I'm trying to have my custom function augment the formula entry (the one that triggered it!) as part of the function. Here's a distilled example:

Private Function heythere(blah As String)
    extraBit = "title"
    ActiveCell.Formula = Replace(ActiveCell.Formula, ")", "," & """" & extraBit & """" & ")")
End Function

Basically that extraBit is defined by a UserForm ListBox if the user doesn't add it into the formula. There are many options, loaded dynamically into the ListBox based on the first argument of the entry. It's a little tricky to explain, but if the extraBit is provided in the formula then it means the UserForm doesn't appear and the result is given directly. So I want to "repair" the entry after the extraBit is defined. Essentially, in the example above, =heythere(a1) would become =heythere(a1,"title").

Is this possible? I tried creating a separate Private Sub to overwrite the entry, to no avail. Thanks in advance for any suggestions...

Edit: To clarify how it is intended to work, the actual UDF is more like this, with an optional secondary argument:

Private Function heythere2(blah As String, Optional extraBit As String)
    If extraBit = "" Then
        extraBit = "title"
        ActiveCell.Formula = Replace(ActiveCell.Formula, ")", "," & """" & extraBit & """" & ")")
    End If
End Function

Upvotes: 0

Views: 53

Answers (1)

Sorceri
Sorceri

Reputation: 8033

The problem is excel has no clue what you intend to do with the formula and the return value is not valid. This will show you that you are encountering an error. if this is a UDF you are entering an endless loop as each time the cell changes it runs the code again. Rethink your approach.

Public Function heythere(blah As String, Optional extraBit As String = "")
    On Error Resume Next
    Dim formulaText As String
    formulaText = ActiveCell.Formula
    If extraBit = "" Then
        extraBit = "title"
    End If
    formulaText = Replace(formulaText, ")", "," & Chr(34) & extraBit & Chr(34) & ")")
    ActiveCell.Formula = formulaText

    If Err.Number <> 0 Then
        MsgBox Err.Description
    End If
End Function

So lets change your formula bit and use the worksheet change event

'Lets set the text of the cell to the value of the extrabit
Public Function heythere(blah As String, Optional extraBit As String = "") As String
    If extraBit = "" Then
        extraBit = "title"
    End If
    heythere = extraBit
End Function

Now we update the formula using our worksheet changed event

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If InStr(1, Target.Formula, ",") > 0 Then

    Else
        On Error Resume Next
        Dim formulaText As String
        formulaText = Target.Formula

        formulaText = Replace(formulaText, ")", "," & Chr(34) & Target.Value & Chr(34) & ")")
        Target.Formula = formulaText

        If Err.Number <> 0 Then
            MsgBox Err.Description
        End If
    End If
End Sub

Viola, the Formula is updated. IMO this is still a bad approach as then you would need to do some checking for which formula is in the cell before you act on it.

Upvotes: 2

Related Questions