none
Bug? WorksheetFunction.Trim Leaves One Whitespace RRS feed

  • Question

  • Hi

    Passing a string-array to WorksheetFunction.Trim Leaves One Whitespace. How to fix?

    VBA:

    ?s
    {"         a", "          b              ", "c          ", "               x"}

    t = Application.WorksheetFunction.Trim(s)
    ?t
    {" a", " b ", "c ", " x"}

    You can see some, but not all, whitespace was trimmed. 


    On worksheet, this works as expected (same data in cells a1:a4 as above)

    array-function in a cell:

    =TRIM(A1:A4)

    result:

    {"a";"b";"c";"x"}


    Also works:

    Function in a cell:

    =TRIM({"    a","     b   ","c     ","          x"})

    Result:

    {"a","b","c","x"}

    - Thx


    • Edited by johny w Thursday, May 19, 2016 1:45 AM
    Thursday, May 19, 2016 1:37 AM

Answers

  • The solution is to use the INDEX trick, to retrieve the array from the function:

    Dim aOutput

    aOutput = ActiveSheet.Evaluate("INDEX(TRIM({""   a   "", "" b   "", ""c   "", ""   d""}), 0)")
    ?aOutput(1)
    a

    ? len(aOutput(1))
     1 


    ---------

    Explanation:

    This whole method is based on the method of converting a VBA array into a worksheet-style array.

    Explanation:

    WorksheetFunction.Trim does not accept arrays:

    aArray = array("    a", "b    ", "    c    ")
    aOutput = WorksheetFunction.Trim (aArray)     ' <-- "Type mismatch" error


    So, instead of WorksheetFunction, we use Application.Evaluate, which gives you the same results as putting the formula into a worksheet cell. 

    But, you cannot pass a vba array to Evaluate(). 

    i = array ("    a", "b    ", "    c    ")
    o = Application.Evaluate("TRIM(i)")
    ?o
    Error 2029


    You must pass the array as a STRING. How do you pass an array as a string? As a worksheet-style array (which is a "serialized" array. This is exactly what you see when you highlight an array-formula in the Excel formula-bar, and press the F9 key. 

    {"        a   ", " b        ", "c        ", "        d"}

    ' VBA example:

    Dim sArray As String

    sArray = "{""        a   "", "" b        "", ""c   "", ""        d""}"

    ?sArray
    {"        a   ", " b        ", "c   ", "        d"}

    That can now be passed into Application.Evaluate (or ActiveSheet.Evaluate), as shown at the beginning of this post. 

    Cheers!



    • Marked as answer by johny w Monday, May 30, 2016 7:50 PM
    • Edited by johny w Thursday, June 2, 2016 1:50 AM
    Monday, May 30, 2016 7:50 PM

All replies

  • Hi johny w,

    When we use the function from the VBA code at that time Function does not understands the "     a"  as single string and its understands it as different words and because of this reason it leaves the 1 single space.

    so you will notice that when you have so many space the function understands that the space is between two words so it leave 1 space.

    please refer the link below.

    WorksheetFunction.Trim Method (Excel)

    it has clearly mention this issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 20, 2016 2:27 AM
    Moderator
  • hi Deepak

    Thx for reply. Your reference says:

    "In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity, &nbsp;. By itself, the TRIM function does not remove this nonbreaking space character."

    This would be the answer to my OP, if my strings contained any non-breaking space characters, and if the other examples i gave did not work as expected. 

    But, my strings do not contain any non-breaking space characters. And my other examples do work as expected, with the same strings. It's all just normal spacebar spaces. 

    Also, if we check the ascii code of the leftover space:

    ?t
    {" a", " b ", "c ", " x"}

    ?Asc(Mid(t, 3, 1))
    32

    That's ascii 32 (normal space), not ascii 160. So, i don't think that's the answer. 
    thx!


    • Edited by johny w Friday, May 20, 2016 6:01 PM
    Friday, May 20, 2016 5:44 PM
  • Hi johny w,

    Try to trim only 1 array element at one time. with this way Trim function working correctly.

    sub demo()
    Dim myarray As Variant
    myarray = Array(" a", " b    ", "c ", " x")
    Debug.Print Trim(myarray(1))
    Debug.Print myarray(1)
    End Sub

    you can also use loop to trim all the elements.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 24, 2016 7:13 AM
    Moderator
  • hi Deepak

    Yes, i know i can loop. That's exactly what i'm trying to avoid. 

    Thx for offering an alternative method, but i'm seeking an explanation for this bug. 

    I believe this is a bug. 

    thx

    Wednesday, May 25, 2016 8:05 PM
  • Hi johny w,

    I again make a test. see the results below.

    I just use trim() on the sheet. it also leaves one space.

    then I try to make a test with VBA.

    Following code I use in VBA.

    Function FnTrim()
    
       Dim strMainString
    
       strMainString = "   my   ,   demo  ,  string  "
    
       Debug.Print "Hello" & Application.WorksheetFunction.Trim(strMainString) & "Trimmed both sides."
    
    End Function

    The following result I get.

    and this time both the results are looks like same. also there is another thing that Excel Trim() and VBA Trim() are both different functions but here it would not apply in this case because we use Excel Trim() in both ways.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 26, 2016 12:59 AM
    Moderator
  • Hi johny w,

    I also find that ,(Coma) and "" are also played roles here.

    because when there is only , between the characters it will leave 1 space but when we place a character within "" and put , between 2 "" then it will remove all the spaces.

    same result I find with VBA but if you pass an array you have to put characters within "".

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 26, 2016 1:28 AM
    Moderator
  • Hi johny w,

    I also find that ,(Coma) and "" are also played roles here.

    because when there is only , between the characters it will leave 1 space but when we place a character within "" and put , between 2 "" then it will remove all the spaces.

    same result I find with VBA but if you pass an array you have to put characters within "".

    Your experience sounds a bit different than mine. Can you post these examples?

    thx

    Thursday, May 26, 2016 7:48 AM
  • Hi johny w,

    in my reply above I already post the Examples and Results of them with pictures. please check them.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 26, 2016 8:56 AM
    Moderator
  • Thx for offering an alternative method, but i'm seeking an explanation for this bug. 

    100% No.

    The documentation says: Removes all spaces from text except for single spaces between words.

    The emphasis in this sentence is on words!

    In English language the comma is not a word, but the Trim function just looks at the blanks, means the comma is a word!

    That's not a bug, you have misunderstood and interpreted the documentation from your human view.

    Read also the Remarks section of the documentation, that makes it clear.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim s As String
      s = "   x  ,  y   a b   c"
      Debug.Print "Before: ", s
      Debug.Print "Trim:", WorksheetFunction.Trim(s)
      
      'WorksheetFunction.Trim does this:
      s = Trim(s)
      Do While InStr(s, "  ") > 0
        s = Replace(s, "  ", " ")
      Loop
      Debug.Print "Manual:", s
    End Sub
    

    Thursday, May 26, 2016 2:24 PM
  • Hi johny w,

    That's all I want to say that it is not a bug. Function works in this way.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 27, 2016 7:09 AM
    Moderator

  • in my reply above I already post the Examples and Results of them 

    Deepak

    Hi Deepak

    I am trying to trim an array in VBA. The VBA example you showed is not an array. You are trimming a single string:

    strMainString = "   my   ,   demo  ,  string  "
    Debug.Print "Hello" & Application.WorksheetFunction.Trim(strMainString) & "Trimmed both sides."


    Monday, May 30, 2016 7:07 PM
  • hi Andreas

    You have misunderstood my question. I'm trying to trim each element in an array, not a single string. 

    thx

    Monday, May 30, 2016 7:08 PM
  • The solution is to use the INDEX trick, to retrieve the array from the function:

    Dim aOutput

    aOutput = ActiveSheet.Evaluate("INDEX(TRIM({""   a   "", "" b   "", ""c   "", ""   d""}), 0)")
    ?aOutput(1)
    a

    ? len(aOutput(1))
     1 


    ---------

    Explanation:

    This whole method is based on the method of converting a VBA array into a worksheet-style array.

    Explanation:

    WorksheetFunction.Trim does not accept arrays:

    aArray = array("    a", "b    ", "    c    ")
    aOutput = WorksheetFunction.Trim (aArray)     ' <-- "Type mismatch" error


    So, instead of WorksheetFunction, we use Application.Evaluate, which gives you the same results as putting the formula into a worksheet cell. 

    But, you cannot pass a vba array to Evaluate(). 

    i = array ("    a", "b    ", "    c    ")
    o = Application.Evaluate("TRIM(i)")
    ?o
    Error 2029


    You must pass the array as a STRING. How do you pass an array as a string? As a worksheet-style array (which is a "serialized" array. This is exactly what you see when you highlight an array-formula in the Excel formula-bar, and press the F9 key. 

    {"        a   ", " b        ", "c        ", "        d"}

    ' VBA example:

    Dim sArray As String

    sArray = "{""        a   "", "" b        "", ""c   "", ""        d""}"

    ?sArray
    {"        a   ", " b        ", "c   ", "        d"}

    That can now be passed into Application.Evaluate (or ActiveSheet.Evaluate), as shown at the beginning of this post. 

    Cheers!



    • Marked as answer by johny w Monday, May 30, 2016 7:50 PM
    • Edited by johny w Thursday, June 2, 2016 1:50 AM
    Monday, May 30, 2016 7:50 PM
  • You have misunderstood my question. I'm trying to trim each element in an array, not a single string. 

    It is 100% clear what you want to accomplish and you've spend a lot of time to find the workaround, but what is the result?

    Sure, now you have one or two lines of code. Instead of 10 or 20 lines of code.

    What you completely forgotten is the execution speed, your solution shorter in code, but much slower in execution.
    IMHO
    it is advisable to note that for followers.

    Andreas.


    Tuesday, May 31, 2016 7:14 AM
  • Sure, now you have one or two lines of code. Instead of 10 or 20 lines of code.

    What you completely forgotten is the execution speed, your solution shorter in code, but much slower in execution.
    IMHO
    it is advisable to note that for followers.

    Andreas.

    i have not "forgotten" about speed-- speed is simply not a factor in this thread. i did not ask for "the fastest way to trim all elements of an array". I did not ask for the pros and cons of using a worksheet function instead of looping.

    I asked for a solution to using the worksheet TRIM function in VBA. The post here marked as 'Answer' is the correct answer to that. 

    But, speed is actually one of the reasons i use worksheet functions when possible. They are implemented in compiled C code (or maybe even assembler in some cases). My reasonable expectation is that's going to run faster than an interpreted VBA loop.

    "use Excel's standard worksheet functions in your VBA code, rather than writing the functions in VBA.  Since these are fully executable instructions in native code, rather than interpreted VBA code, they run much faster."
    -- http://www.cpearson.com/excel/optimize.htm

    and:

    "By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm."
    -- http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html

    If you have test results that show otherwise, plz share. 

    Since worksheet fx also require less VBA, that is also an advantage. i prefer less typing. 

    thx



    • Edited by johny w Thursday, June 2, 2016 7:21 PM
    Wednesday, June 1, 2016 8:55 PM