VBA function fails when inserting values into Array

Question

I'm totally stumped on why this isn't returning a value. This set of functions is supposed to return the SUM of all the ASCII values in a String. The String and Number arrays build properly and the SUM function works on the Array, however when it tries to assign that summed value to the return variable, it fails.

By "it fails" I mean that the code does not step to the sum line and the function returns #VALUE! to the worksheet. Everything else checks out in the immediate window and steps through properly until the last line. Any suggestions are appreciated.

Private Function StringToCharArray(ByRef sIn As String) As String()

    StringToCharArray = Split(StrConv(sIn, vbUnicode), Chr(0))
    'provided by Fencliff at http://www.mrexcel.com/forum/excel-questions/342725-split-string-into-array.html

End Function

Function StringToNumber(MyString As String) As Integer

    Dim StringArray() As String
    Dim NumberArray() As Long

    StringArray() = StringToCharArray(MyString)

    ReDim NumberArray(UBound(StringArray))

    For i = LBound(StringArray) To UBound(StringArray)
        NumberArray(i) = Asc(StringArray(i))
    Next i

    StringToNumber = Application.WorksheetFunction.Sum(NumberArray)

End Function

Whoever came up with the StringToCharArray function should read The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!). — GSerg
– GSerg, Commented May 19, 2014 at 17:24

Andy G · Accepted Answer · 2014-05-19 17:23:07Z

4

Using an example of "abc" the Split obtains an array "a", "b", "c", "". That is, with an empty string element at the end. This cannot be converted to an ascii number so causes the error.

A simplistic solution is to skip this last element:

For i = LBound(StringArray) To UBound(StringArray) - 1

I suspect the problem (the extra element) is related to the use of Unicode but I haven't investigated this.

answered May 19, 2014 at 17:23

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

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

2 Comments

JMT2080AD Over a year ago

That's it! If I had checked the array length I might have caught the extra character. Thanks!

Geoff Over a year ago

If MyString contains a terminating separator (Chr(0) in this case) then Split will put an empty entry on the end. You could Trim(MyString) if there's no chance you're going to destroy other whitespace you care about.

Collectives™ on Stack Overflow

VBA function fails when inserting values into Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related