Test when value of an array is filled

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
Hello guys,

now I would need some helping hands with following UDF.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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.

Code:
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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
How about adding this line in the loop to help debug:

Rich (BB code):
    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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

?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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
Hi,
well the function gets used witin another sub

Code:
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top