User Defined Function Do Not Update

Question

I use Excel 2007 and have created the function below to eliminate some characters from a string.

Public Function CleanString(sUser As String) As String
Dim lStringSize As Long, lCounter As Long
Dim iCode As Integer
Dim sChar As String

Application.Volatile

On Error GoTo ErrorHandler
lStringSize = Len(sUser)
For lCounter = 1 To lStringSize
    sChar = Mid(sUser, lCounter, 1)
    iCode = Asc(sChar)
    If ((iCode < 65) Or (iCode > 90 And iCode < 97) Or (iCode > 122)) And (iCode <> 45) Then
        sUser = Replace(sUser, sChar, Chr(32))
    End If
Next lCounter



CleanString = sUser
    Exit Function

ErrorHandler:
    CleanString = ""
    Exit Function

End Function

When I use this function as follows in my spreadsheet B1 contains the string "Samyn; Filiep" and the result in C1 = CleanString(B1) generates the expected result "Samyn Filiep" The problem is that after saving and reopening the worksheet C1 contains "Samyn; Filiep" i.e. the B1 without any function applied similar as if C1 would contain the formula = B1. I have tried application.calculate, application.calculatefull, and selection.calculate (where selection is C1) but the result does not update. The only way to have the results back is to edit C1 and after enter the result is correct. Nevertheless upon reopening the results are gone again. I have no other code besides the listed function. Excel is set for auto recalculation. My first question is why do the results of the executed function disappear when saving. My second question is why does the cell using the function not update when doing a full recalculation.

put the following formula in **B1**: `=CHAR(RANDBETWEEN(1,227)) & CHAR(RANDBETWEEN(1,227)) & CHAR(RANDBETWEEN(1,227)) & CHAR(RANDBETWEEN(1,227)) & CHAR(RANDBETWEEN(1,227))` If you touch *F9* does **C1** update?? — Gary's Student, Feb 24 '18 at 16:09
From Formulas tab >> Calculation Options >> Make sure it's "Automatic" — YasserKhalil, Feb 24 '18 at 16:24
Yes the function is in a standard module and yes the calculation is set to automatic. I have meanwhile found the solution to the problem although I have no idea why it works. — Filiep Samyn, Feb 25 '18 at 17:36

score 0 · Answer 1 · answered Feb 24 '18 at 22:15

You don't need Application.Volatile as you are using the required Range (B1) in the UDF call. Your current set up means the UDF fires on every change in the worksheet, not just the required cell - removing this means that it will only fire when B1 is changed.

Your taking string sUser as input, hopefully changing it, then returning sUser as the result. For safety, I always manipulate a temporary string and then return that. In your case, if the loop or replacements do not work as intended, you will return the original string. However, I don't think this is the source of the issue in this case.

Your use of Replace is inefficient, you loop through the sting character by character, and then call Replace which works on the full string. In the same vein, your condition is also confusing, Goodcode[Boolean] = (icode > 64 And icode < 91) or (icode > 96 and icode < 123) or icode = 45 or icode = 32) is more descriptive of what you really want. You can then use If Not GoodCode Then etc. However, the use of Regex would be more efficient and you wouldn't need the loop or the If.

Also consider using Trim (...) as this will remove excess spaces after the change.

However, the two main areas to check are: Application.Volatile and that the UDF is defined in a standard module.

I agree with your comments and improvement suggestions. I have the trim function implemented in the worksheet. I am not familiar with Regex and it does not appear as a VBA or Excel. Are you sure this command is available (used it in Python but not VBA). I looked at similar code on https://stackoverflow.com/questions/36983532/replace-multiple-characters-in-a-string-variable-vba. It appears that the authors also used Replace. If you have suggestions to improve code efficiency then I would like to hear from you.I have found a solution but have no idea why it works, see my posting. — Filiep Samyn, Feb 25 '18 at 17:52
under VBA, Regex is another library you can add (under `References ...`). I am not familiar with Python so can't directly compare the two, but if you understand Regex in Python you will certainly understand it in VBA as well.. — AJD, Feb 25 '18 at 18:40

score 0 · Answer 2 · edited Feb 25 '18 at 19:37

Here is what I did and ends up working but I am clueless why. I replaced the code above with the code below

Public Function CLSTR(sUser As String) As String
Dim lStringSize As Long, lCounter As Long
Dim iCode As Integer
Dim sChar As String

On Error GoTo ErrorHandler

    lStringSize = Len(sUser)
    For lCounter = 1 To lStringSize
        sChar = Mid(sUser, lCounter, 1)
        iCode = Asc(sChar)
        If ((iCode < 65) Or (iCode > 90 And iCode < 97) Or (iCode > 122)) And (iCode <> 45) Then
            sUser = Replace(sUser, sChar, Chr(32))
        End If
    Next lCounter

CLSTR = sUser
    Exit Function

ErrorHandler:
    CLSTR = ""
    Exit Function

End Function

As you will notice there is no change in the code besides that I have removed application.volatile; although leaving or removing it in the original code had no effect.

The only difference that the name of the function is in all caps and is now called CLSTR instead of ClearString.

This works as intended.

User Defined Function Do Not Update

2 Answers2