Test when value of an array is filled

[silentwolf]

Hello guys,

now I would need some helping hands with following UDF.

Function ReNrAendern(ByVal strText As String) As String
    Dim strOld(0 To 11) As Variant
    Dim strNew As String
    
    Dim i As Integer
    
    strOld(0) = "RE "
    strOld(1) = "RE. "
    strOld(2) = "RE: "
    strOld(3) = "Re "
    strOld(4) = "Re. "
    strOld(5) = "Re: "
    strOld(6) = "Rg "
    strOld(7) = "Rg. "
    strOld(8) = "Rg: "
    strOld(9) = "RG "
    strOld(10) = "RG. "
    strOld(11) = "RG: "

    strNew = "RNR. "
    
    For i = LBound(strOld) To UBound(strOld)
        strText = Replace(strText, strOld(i), strNew)
    Next i
    
    ReNrAendern = strText
End Function

So how can I test this code, what strOld array is used when I step through the code using F8

The reason behind this is that mostly the function replace how it intend to but with one case it replace the strText not the way I want it.
So I was trying to step through the code but not really find what array is used if the strText contains one of the strOld within the strText.

Hope I did explain this good enough and someone can give me a hint on that.

I guess it should be a watch added or? But with the watch I still was not sure how to find when it was driggert.
Or how to look for it correctly.

Many thanks

 

[James006]

Hello,

Just copy your UDF into into a Test standard Sub ...for all your tests ...

Once the Sub operates as needed, transform it into a UDF ...

Hope this will help

 

[FormR]

Hi, add a breakpoint on the very first line of the function and then execute the function from the immediate window with, for example:

? ReNrAendern("RE report")

 

[silentwolf]

Hi James,
I guess there is a missunderstanding.. )
The UDF is working fine no problem.
But what I need to find out is what array it uses.

Function ReNrAendern(ByVal strText As String) As String
    Dim strOld(0 To 71) As Variant
    Dim strNew As String
    
    Dim i As Integer
    
    strOld(0) = "RE "
    strOld(1) = "RE. "
    strOld(2) = "RE: "
    strOld(3) = "Re "
    strOld(4) = "Re. "
    strOld(5) = "Re: "
    strOld(6) = "Rg "
    strOld(7) = "Rg. "
    strOld(8) = "Rg: "
    strOld(9) = "RG "
    strOld(10) = "RG. "
    strOld(11) = "RG: "
    
    strOld(12) = "RE NR "
    strOld(13) = "RE NR. "
    strOld(14) = "RE NR: "
    strOld(15) = "RE Nr "
    strOld(16) = "RE Nr. "
    strOld(17) = "RE Nr: "
    
    strOld(18) = "Re Nr "
    strOld(19) = "Re Nr. "
    strOld(20) = "Re Nr: "
    
    strOld(21) = "RN "
    strOld(22) = "RN. "
    strOld(23) = "RN: "
    strOld(24) = "R.-NR "
    strOld(25) = "R.-NR. "
    strOld(26) = "R.-NR: "
    strOld(27) = "Rg Nr "
    strOld(28) = "Rg Nr. "
    strOld(29) = "Rg Nr: "
    strOld(30) = "Rg. Nr "
    strOld(31) = "Rg. Nr. "
    strOld(32) = "Rg. Nr: "
    strOld(33) = "RgNr "
    strOld(34) = "RgNr. "
    strOld(35) = "RgNr: "
    strOld(36) = "Rechnungs Nr "
    strOld(37) = "Rechnungs Nr. "
    strOld(38) = "Rechnungs Nr: "
    strOld(39) = "Beleg Nr "
    strOld(40) = "Beleg Nr. "
    strOld(41) = "Beleg Nr: "
    strOld(42) = "Beleg. Nr "
    strOld(43) = "Beleg. Nr. "
    strOld(44) = "Beleg. Nr: "
    strOld(45) = "RNR. "
    strOld(46) = "RNR: "
    strOld(47) = "Rechnungs- Nr "
    strOld(48) = "Rechnungs- Nr. "
    strOld(49) = "Rechnungs- Nr: "
'    strOld(50) = "R#### "
    
    strOld(51) = "Re.Nr. : "
    strOld(52) = "Re.Nr. "
    
    strOld(53) = "Beleg.Nr.: "
    
'    strOld(54) = "Re Nr."
    
    strOld(55) = "Belegnummer: "
    strOld(56) = "RENR "
    strOld(57) = "A1072/"
    strOld(58) = "Rechnungsnr. "
    strOld(59) = "Rechnr. "
    strOld(60) = "Belegnummer "
    strOld(61) = "Belegnr. "
    strOld(62) = "belegnr. "
    strOld(63) = "Beleg nr. "
    strOld(64) = "Belegnummer."
    strOld(65) = "Rechnungs-Nr.: "
    strOld(66) = "RE####"
    strOld(67) = "Re####"
    strOld(68) = "RechnungsNr:"
    strOld(69) = "Rechnung "
    strOld(70) = "RNR "
'    strOld(71) = "R####,"
    
    strNew = "RNR. "
    
    For i = LBound(strOld) To UBound(strOld)
        strText = Replace(strText, strOld(i), strNew)
    Next i
    
    ReNrAendern = strText
End Function

so this is my complete UDF...
However if I type in the Immidiate window following
?ReNrAendern("With some text and Re Nr. 2036")

it gives me the wrong outcome...
So I like to see where the code is going wrong ..
I like to find what strOld it uses but can't work it out ..

You could test the code if you like .. the outcome of this line is ("With some text and RNR. Nr. 2036")

Which is wrong it should be ("With some text and RNR. 2036")

So I like to test where this code fails.. so I need to find in what array(strOld) it goes to.

Is that a little more clear now?

 

[silentwolf]

Hi FormR,

thanks also for your reply.
That is what I did.. but how can I see where it goes into the array?
I can not seam to find it.

 

[FormR]

How about adding this line in the loop to help debug:

    For i = LBound(strOld) To UBound(strOld)
        If InStr(1, strText, strOld(i)) > 0 Then Debug.Print strOld(i)
        strText = Replace(strText, strOld(i), strNew)
    Next i

 

[FormR]

?ReNrAendern("With some text and Re Nr. 2036")

The problem you have is that you replace "Re " with "RNR." before you attempt to replace "Re Nr." with "RNR."

 

[silentwolf]

Hi FormR,
thanks for your input.
Yes I believe it shows me now what it does.
it goes into strOld(3) which is strOld(3)="Re "

and that is what I suspected but did not know from debuging ...

so there is no chance the function is going to strOld(19)= "Re Nr. "

or is there a way to change that so it does?

Many thanks

 

[FormR]

or is there a way to change that so it does?

Maybe change the order of the strold() array elements so the longest strings are first and exit the loop after doing the first replacement.

 

[silentwolf]

Hi,
well the function gets used witin another sub

Sub RechnungsNummerUpdate()
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = Range("J2", Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = ReNrAendern(Buchung(i, 1))
    Next i

    Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung
End Sub

So I guess exit sub is not working or am I wrong?

Another option I was thinking could be a select case with if statements I done something similar with a different function but then it is alot of code writing...