vba Expected Array

Question

Anybody have a good solution for recursive replace? For example, you still end up with commas in this string returned by MsgBox:

Dim s As String
s = "32,,,,,,,,,,,,,,,,23"

MsgBox Replace(s, ",,", ",")

I only want one comma.

Here is code that I developed, but it doesn't compile:

Function RecursiveReplace(ByVal StartString As String, ByVal Find As String, ByVal Replace As String) As String

    Dim s As String

    s = Replace(StartString, Find, Replace)
    t = StartString

    Do While s <> t
        t = s
        s = Replace(StartString, Find, Replace)
    Loop

RecursiveReplace = s
End Function

The compiler complains about the second line in the function:

s = Replace(StartString, Find, Replace)

It says Expected Array.

???

Can you confirm that the error reappears after you save your work, close everything including the host application (Excel or whatever you work with) and reopen again? — Alex Bausk
– Alex Bausk, Commented Jan 23, 2018 at 17:19
Replace is a function name. Maybe VBA thinks you are trying to call the function as the argument. Try with a different argument name? (Don't use Find/Replace as variable names is what I'm getting at) — Jacob H
– Jacob H, Commented Jan 23, 2018 at 17:20
@JacobH is right, overlooked that one. You cannot have a variable named Replace. — Alex Bausk
– Alex Bausk, Commented Jan 23, 2018 at 17:21
yes! thanks. there was also a mistake in my function, but that is the reason for the compile error.... — Heap of Pinto Beans
– Heap of Pinto Beans, Commented Jan 23, 2018 at 17:28

John Coleman · Accepted Answer · 2018-01-23 17:31:58Z

2

You can use a regular expression. This shows the basic idea:

Function CondenseCommas(s As String) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Pattern = ",+"
    CondenseCommas = RegEx.Replace(s, ",")
End Function

Tested like:

Sub test()
    Dim s As String
    s = "32,,,,,,,,,,,,,,,,23"

    MsgBox CondenseCommas(s)
End Sub

answered Jan 23, 2018 at 17:31

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

vba Expected Array

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