VBA - Illegal Assignment in For Each

Question

Function FunctionName(str)

    Dim resultStr
    Dim wordArr
    wordArr = Array(Split(str, "_"))

    For Each item In wordArr
        resultStr = resultStr & UppercaseFirstLetter(item)
    Next item

    FunctionName = resultStr

End Function 'FunctionName

Consider the above for each loop in VBA. I keep getting the error of Illegal Assignment 'item'

Note: UppercaseFirstLetter is a function that converts the first letter of the given word into uppercase.

I'm new to VBA, any advice is much appreciated!

item is an object, and your function probably requires a string. Try what @GSerg suggested. — Tehscript
– Tehscript, Commented Oct 1, 2017 at 8:19
A function that converts the first letter of the given word into uppercase in VBA is StrConv(text, vbProperCase). As for the error, it is not in the shown code, even though it unnecessarily wraps an array returned by Split into another array created by Array. Given this code, you should be getting error 13 Type mismatch. — GSerg
– GSerg, Commented Oct 1, 2017 at 8:25
Hints to become a better programmer in VBA: Always use Option Explicit first in each file. Always Dim with the variable type. In your case your Dims dimensions the variables to Variants so for readabillity you should type that: Dim resultStr As Variant — user2261597
– user2261597, Commented Oct 1, 2017 at 13:39

mgae2m · Accepted Answer · 2017-10-01 14:31:44Z

1

Here you are.

Option Explicit
Sub test()
    Debug.Print FunctionName("test1_test2")
End Sub 'Test
Function FunctionName(str)

    Dim resultStr As String
    Dim wordArr() As String
    Dim item As Variant
    wordArr = Split(str, "_")

    For Each item In wordArr
        resultStr = resultStr & UppercaseFirstLetter(item)
    Next item

    FunctionName = resultStr

End Function 'FunctionName
Function UppercaseFirstLetter(item As Variant)
    UppercaseFirstLetter = UCase(Mid(item, 1, 1)) & Mid(item, 2)
End Function 'UppercaseFirstLetter

answered Oct 1, 2017 at 14:31

mgae2m

1,1381 gold badge16 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

VBA - Illegal Assignment in For Each

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related