Values from Range not passed to array in a UDF

Question

The following function I wrote returns "" whenever I attempt to use it on ranges that contain formulas. Oddly, when I test what values are returned for rngPhrase and rngTextBlocks in the immediate window, it returns a correct result.

I can get a correct result for the first range by appending .Text (instead of using the default .value or even .value2) for the first range, but this doesn't work for the Array.

What is happening here and how can I solve it?

    Public Function SelectionReplacer(rngPhrase As Range, rngTextBlocks As Range) As String

    Dim arr
    Dim strTextBlocks As String
    Dim strPhrase As String
    Dim i As Integer, j As Integer

    'set initial phrase (must be a single cell)
    strPhrase = rngPhrase.Text

    'Set text block array
    arr = rngTextBlocks


For i = LBound(arr, 1) To UBound(arr, 1)
    If InStr(1, strPhrase, arr(i, 1)) > 0 Then
        SelectionReplacer = Replace(strPhrase, arr(i, 1), arr(i, 2))
    End If
Next i

    End Function

UPDATE Examples of Input

rngPhrase = Range("D34") with the value in this cell being calculated by a vlookup function that returns a string "[Anrede] [FullName] verfügt über ein äußerst [Art1] und [Art2] Fachwissen, das [Gender1] stets effektiv und erfolgreich [Können1] konnte. [Können2]"

rngTextBlocks has similar functions all of which have similar text elements returned by their respective vlookup functions.

Could you add an example? What is rngPhrase and rngTextBlocks? is is the global value of `arr = rngTextBlocks` that does not works? — Maxime Porté, Feb 01 '17 at 10:24
What do you do with `rngTextBlocks` and can you show in the code where you load the value back into `SelectionReplacer` — CLR, Feb 01 '17 at 10:27
Did you take a look, step by step, of what happen in your `For` loop? If your code don't go Inside of it or if your `If` condition is never valid, your return value will be "" (default string value) — Maxime Porté, Feb 01 '17 at 10:39
It goes through the loop fine. The problem is that there is nothing to process in the loop as the variables are zero strings. To be clear. The values from the ranges do not get tranfered to the variables in the code! — rohrl77, Feb 01 '17 at 11:32

score 2 · Accepted Answer · answered Feb 01 '17 at 10:48

You were replacing only one of the text, so only the last row of the range.

You need to change strPhrase, with Replace and then assign it to SelectionReplacer.
BTW, no need to test before the Replace as it'll only occur if the text is found! ;)

I also added a check to exit properly the function if rngPhrase is more than a single cell :

Public Function SelectionReplacer(rngPhrase As Range, rngTextBlocks As Range) As String
'(must be a single cell)
If rngPhrase.Cells.Count > 1 Then
    SelectionReplacer = vbNullString
    Exit Function
End If

Dim arr
Dim strTextBlocks As String
Dim strPhrase As String
Dim i As Integer, j As Integer

'set initial phrase
strPhrase = rngPhrase.Value

'Set text block array
arr = rngTextBlocks.Value

For i = LBound(arr, 1) To UBound(arr, 1)
    strPhrase = Replace(strPhrase, arr(i, 1), arr(i, 2))
Next i
SelectionReplacer = strPhrase

End Function

Thanks. This worked. Oddly the code is called twice! On the first time it is passed through the variables remain empty. The second time they actually do work as expected. Any idea why this is? — rohrl77, Feb 01 '17 at 11:38
@rohrl77 : Honestly, I don't see why... Does it happen every time? Did you activate the iterative calculation mode? I can't think of anything else because the code is pretty straight forward! — R3uK, Feb 06 '17 at 08:02

score 1 · Answer 2 · answered Feb 01 '17 at 10:41

I think slight change will cause it to behave the way you want:

Public Function SelectionReplacer(rngPhrase As Range, rngTextBlocks As Range) As String

Dim arr
Dim strTextBlocks As String
Dim strPhrase As String
Dim i As Integer, j As Integer

'set initial phrase (must be a single cell)
strPhrase = rngPhrase.Text

'Set text block array
arr = rngTextBlocks
For i = LBound(arr, 1) To UBound(arr, 1)
    If InStr(1, strPhrase, arr(i, 1)) > 0 Then
        strPhrase = Replace(strPhrase, arr(i, 1), arr(i, 2)) ' alter strPhrase for each iteration
    End If
Next i
SelectionReplacer = strPhrase ' load strPhrase back to SelectionReplacer
End Function

Values from Range not passed to array in a UDF

2 Answers2