I am wondering, though, whether the following modification to this code might not be faster (it removes one of the repeated +1 from inside the loop)?
Code:
Function AddChars(ByVal S As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
Dim X As Long, Position As Long
AddChars = String(Len(S) + Int((Len(S) + (Len(S) > 0)) / Spacing), Chr(1))
Position = 1
For X = 1 To Len(S) Step Spacing
Mid(AddChars, Position, Spacing) = Mid(S, X, Spacing)
Position = Position + Spacing + 1
Next
AddChars = Replace(AddChars, Chr(1), InsertText)
End Function
By my testing, see below, this is about 7% slower than the code in post #22.
Just out of curiosity, how are you performing your timing tests for your "each timed 6 times over 10,000 rows" test... how do you measure the speed of a function in a cell?
I hope that I am applying it correctly. I'm using the codes provided just below where
this link lands you.
For this thread I've generated 10,000 rows (started much smaller of course) of random data strings of 50 characters or less with characters made up roughly two thirds [a-zA-Z], one sixth [0-9] and one sixth at random from a few special characters " !#$%&()*+". I can provide the code producing the data if you want. I manually made sure I had a blank and an exact multiple of 'Spacing' near the top of my data as they were likely candidates to fail.
I then populated adjacent columns with functions I was comparing, selected either B1:B10000 or C1:C10000 and ran the 'RangeTimer' macro from the above link. I usually ran the timer a couple of times then Saved the file before running the timer again. Sometimes I completely removed the function I wasn't timing to ensure it wasn't actually having an impact, but it seemed to make no difference - as it shouldn't given the timing code.
While I do not doubt your results, I am a little surprised by them. I deliberately avoided doing the repeated concatenations you used because concatenations are a slower process than performing the, what I call, string-stuffing method that I used (where Mid is used on the left side of the assignment operation). True, I add an additional line of code to the loop, but it is a simple addition statement which is a fairly fast operation in VB, so I was not expecting my code to be slower than your latest code. Perhaps it hinges on my use of the single Replace function call at the end of the procedure (Replace is not one of VB's fastest functions, but it is not repeated inside the loop, so I didn't think it would produce that much of a drag on the execution speed).
I am not able to add anything as to the reason.
Anyway, I note you used the IIf function in your code... as it turns out, that is one of VB's slowest functions. So, at the risk of extending the "Death by Tweakage" stage a little more, see if this modification to your code (in which I replaced the IIf function call with a logical expression) speeds things up of not...
Code:
Function AddCharsNew(ByVal S As String, Optional Spacing As Long = 1, Optional InsertText As String = " ") As String
Dim i As Long, startpos As Long
startpos = Len(S) - (Len(S) Mod Spacing) + Spacing * (Len(S) Mod Spacing = 0)
For i = startpos To Spacing Step -Spacing
S = Left(S, i) & InsertText & Mid(S, i + 1)
Next i
AddCharsNew = S
End Function
This and the post #22 code are so close (within 0.05%) that I wouldn't say there was any difference at all.
However, I'm glad you suggested that new startpos line. I was originall trying write something like that but couldn't get my head around the True condition for (Len(S) Mod Spacing = 0) returning -1 in vba as opposed to +1 in a worksheet formula - so I resorted to the IIf.
Edit: An afterthought is that I guess it is possible that using different machines could make a difference to relative speeds.
This post has links to a couple of other threads where another poster and I have compared speeds (macro rather than calculation but it's possible the same thing happens with calculations, particularly ones that use vba), with completely opposite results.
