0

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.

Improve this question
4
  • Is your UDF() in a standard module ?? Commented Feb 24, 2018 at 16:00
  • 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?? Commented Feb 24, 2018 at 16:09
  • From Formulas tab >> Calculation Options >> Make sure it's "Automatic" Commented Feb 24, 2018 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. Commented Feb 25, 2018 at 17:36

2 Answers 2

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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 stackoverflow.com/questions/36983532/…. 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.
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..
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.