Test when value of an array is filled

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,166
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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,689
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,166
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,166
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,689
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,689
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,166
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,689
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,166
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top