Printable View
Hi all, :hi:
I just want to share some info here. Do I post at the right place? :think: If not, pls forgive me.
Below is the link to an article about string in VB. Only after reading, I know vbNullString is better than "". After I implemented it in one of my macro, vbNullString cut execution time down by half and more!
Optimize string handling in Visual Basic
http://www.aivosto.com/vbtips/stringopt.html
Thanks.
Hi, :hi:
Always nice to read a good article...thnx!
Hi sheeeng,
An interesting article. One I would recommend serious VBA-ers to read, but ...
FWIW, my take on this is that one deliberately sacrifices performance for other gains when using a language such as VBA. If performance really matters I would use another language (although it might be cheaper to buy a faster chip and stick with VBA).
Some of the tricks in the article seem to sacrifice readability and maintainability in particular - if you are comfortable with the code and no-one else is going to have to work on it then fine, but in a corporate environment the overhead of someone unfamiliar with some of the techniques having to understand the code is quite possibly more expensive than the performance saving.
A couple of (to me) interesting points:
I can see that changing "" to vbNullString might give some benefit, but cutting execution time by half. WOW !
vbNewLine faster than vbCr? I would have expected the opposite as vbNewLine has more processing to do - the article doesn't say so but one should always use vNewLine anyway as it's platform-independent.
Hi Tony, :yes
The vbNullString part was a chocker to me as well! :eek:
I new it was faster...but I doubt if it cuts processing time in half..
I use it anyway as it is good practice.
For that line, maybe, but not the whole procedure! LOL!Quote:
Originally Posted by MOS MASTER
I kind a thought the same thing! :devil: :rofl:Quote:
Originally Posted by kpuls
Just to share the knowledge wit everyone...
Hey Sheeng,
Not dissing the article at all, just putting forward our opinions on it. I actually came across it a couple of years ago, and I also have changed my coding to use vbNullString over "". ;)
Great Avatar, btw!
Please carry on sharing, sheeeng.
I found the article, and others on the site, interesting and, as I said, I recommend others to read it. The pointer to it is a valuable addition to VBAX.
I would guess that the majority of members and visitors here don't even know there is a difference between a null string and an empty string, never mind what the difference is, or how to distinguish between the two if it matters.
I always thought that "" should be used over vbNullString. So which is really better? We can easily put it to the test.
I ran this code 6 times now, and vbNullString was never the fastest for me. It went back and forth from "" and Len. Though all three were very close together (within less than .1% of the min).Code:Option Explicit
Sub StringTest()
Dim MyTimer As Double
Dim i As Long
Dim j As Long
Application.ScreenUpdating = False
For j = 1 To 20
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 500
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B22").Value = "=Average(B2:B21)"
Range("C22").Value = "=Average(C2:C21)"
Range("D22").Value = "=Average(D2:D21)"
Range("B:D").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
So which is fastest? I can't say with 100% certainty, though I can say that none of the methods are 50% faster than the other.
And given the speed of today's PCs, a small performance gain is usually not worth much. So just code the way you are comfortable.
I would agree on this...small performance gain is not significantly affecting our life nowadays....:yes
Well, maybe in a larger scale of code, it might have a difference...
Who knows? :dunno
Was chatting with Jake on YIM here, and I ran up a longer test than he did, and came up with some different results. Ain't as perty and you have to add your own average formula right now, but:
For me, after 50 loops, vbnullstring = .8225, "" = 1.51838Code:Sub b()
Dim MyTimer As Double
Dim i As Long
Dim j As Long
Application.ScreenUpdating = False
Range("C1").Value = "vbnullstring"
Range("D1").Value = "2 quotes"
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
End Sub
The weird part is that while vbnullstring starts and stays fast, "" seems to slow down after a few iterations. :dunno
Hmm....That's a very good research on what u have done...Keep it up :clap: ....
I sure there is a reason for what is happening...
Ok, when I run Ken's code I get this.
"" = .9912
vbNullString = 1.01062
About a 2 percent difference (on 500,000 iterations).
Okay, with more runs, the time differences for me seem to be coming in +- 10% either way. :dunno ie, no major performance gain stands out over the other.
So what happens if we increase the iterations. Let's try 1 million iterations each.
In my test with this code, Len came out on top with an average of .50704. The other two methods were less than 1% off.Code:Option Explicit
Sub StringTest()
Dim MyTimer As Double
Dim i As Long
Dim j As Long
Dim UpperLimit As Long
Application.ScreenUpdating = False
UpperLimit = 200
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 1: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 2: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 5000
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 3: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B" & UpperLimit + 2).Value = "=Average(B2:B" & UpperLimit & ")"
Range("C" & UpperLimit + 2).Value = "=Average(C2:C" & UpperLimit & ")"
Range("D" & UpperLimit + 2).Value = "=Average(D2:D" & UpperLimit & ")"
Range("B:D").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.
So basically, choose your method of choice.
Okay, using Jake's last example using XP Pro, Excel 2003, 3.06 Ghz with 512 MB Ram:
Double Quotes = 0.80844
vbNullstring = 1.21585
Len = 1.1453
The procedure takes a while to run though, so I'm not testing it again tonight! LOL!
I don't think that vbNullstring offers any significant performance advantage here at all.
I will try the code on my PC tomolo...Seem wreid here....Hmm...
vbNullString exists for a purpose....
I supposed...
:friends:
I think I need to rephrase my statement here....Quote:
Originally Posted by sheeeng
vbNullString WILL NOT cut execution time down by half and more! :(
I tried your code already...:eek:
I am running this on Excel 2003 with Windows XP Pro 2.8GHz CPU with 504 MB Ram.
It took me so long to get results.....:doh: About 4 minutes of execution time...:old:
"" = 0.525535
vbNullString =0.453905
Len = 0.4604
Initial testing = vbNullString faster
I would like all to test this code on their machine and give us the results...Quote:
Originally Posted by DRJ
The more the better....
Can anyone help me to make a poll on this test results?
I don't know how to make a poll on this...:dunno
My guess is, there are just too many factors beside the code fragment that influence the necessary time.
Take Windows memory management for instance. How much is swapped, when will be the next swapping, what other processes are running etc.
Maybe to really test this, you'll need a clean environment (so just Windos + Office, no Anti-Virus software, no indexing or other processes, reboot after each test, run each test many times (because of different conditions after each boot).
Gentlemen!, Gentlemen!, What are you doing?
vbNullString is a code construct. This has nothing to do with Excel. The gains, if there are any, are to be made when you have String variables set to, and compared to, vbNullString.
I don't know how values are held in Excel cells, but I am not aware of an Excel equivalent to vbNullString and I would not expect any significant performance advantage when one half of your expression is a cell value. Also, I rather suspect that writing to the StatusBar rather distorts the measurements.
I have just run a brief test and not seen any great difference but will do a proper one later.
Dear,
Great posting on faster execution.
A coder should know these things.
Thnks for posting
....
I think I was slightly hasty in my last response, sorry! :blush
What I should have said was that the Excel aspects of the test code seemed likely to take the lion's share of the execution time. Apart from anything else, Excel cell values are variants which adds extra complication to the picture.
I have run some more tests (see below) which suggest that vbNullString is actually slower than anything else - and checking for a length of zero is the fastest. I can only guess at the reason for this but I'll try ..
Nulls do not really exist in Excel at all, and they are not native to VBA. As I understand it, support for them (apart from in Access) is provided mainly for compatibility with other applications which may require them. My suspicion is that, on finding a null string when doing a string operation, the first thing that VBA probably does is to create an equivalent non-null (empty) string. I imagine, in this context, that determining the length of a null string is not considered an operation requiring strings, and that a little optimization is done.
The code I ran was:
[VBA]Option Explicit
Sub StringTest()
Dim MyTimer As Double
Dim i As Long
Dim j As Long
Dim UpperLimit As Long
Dim LoopTimes As Long
Dim MyString As String
Dim ResultColumn As Long
Application.ScreenUpdating = False
UpperLimit = 101
LoopTimes = 10000000
ResultColumn = 0
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Empty v. Empty"
For j = 2 To UpperLimit
MyString = ""
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString = "" Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Empty v. Len(0)"
For j = 2 To UpperLimit
MyString = ""
i = 0
MyTimer = Timer
While i < LoopTimes
If Len(MyString) = 0 Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Empty v. Null"
For j = 2 To UpperLimit
MyString = ""
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString = vbNullString Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Null v. Empty"
For j = 2 To UpperLimit
MyString = vbNullString
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString = "" Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Null v. Len(0)"
For j = 2 To UpperLimit
MyString = vbNullString
i = 0
MyTimer = Timer
While i < LoopTimes
If Len(MyString) = 0 Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Null v. Null"
For j = 2 To UpperLimit
MyString = vbNullString
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString = vbNullString Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Full v. Empty"
For j = 2 To UpperLimit
MyString = "Any Old Data"
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString <> "" Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Full v. Len(0)"
For j = 2 To UpperLimit
MyString = "Any Old Data"
i = 0
MyTimer = Timer
While i < LoopTimes
If Len(MyString) <> 0 Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
ResultColumn = ResultColumn + 1
Cells(1, ResultColumn).Value = "Full v. Null"
For j = 2 To UpperLimit
MyString = "Any Old Data"
i = 0
MyTimer = Timer
While i < LoopTimes
If MyString <> vbNullString Then
i = i + 1
End If
Wend
Cells(j, ResultColumn).Value = Timer - MyTimer
Next j
For j = 1 To ResultColumn
Cells(UpperLimit + 2, j).Value = "=Average(R2C:R[-2]C)"
Next
Range("1:" & ResultColumn).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub[/VBA]
and the results were:
Empty v. Empty 3.35204 secs (average)
Empty v. Len(0) 3.07079 secs
Empty v. Null 3.53828 secs
Null v. Empty 3.38719 secs
Null v. Len(0) 3.10751 secs
Null v. Null 3.51327 secs
Full v. Empty 3.34125 secs
Full v. Len(0) 3.08453 secs
Full v. Null 3.49797 secs
Good Research ! :thumbQuote:
Originally Posted by TonyJollans
But does anyone from Microsoft had to say on this?
How does the stat above showed the faster one?
eg.
Empty v. Len(0) 3.07079 secs - Who is Faster?
Hi sheeeng,
Sorry - my wording is not clear. What that means is that the figure of 3.07079 seconds is the average time taken (out of 100 runs) for 1 million comparisons of the length of an empty string against 0.
Hello all, nice stuff. :yes
Here are some good reads:
Passing Other Types of Information to a DLL Procedure
Using VbConstants in Text Strings in MS Excel 97
How to use VbConstants in text strings in Excel for Mac
XL2000: How to Use Visual Basic Constants in Text Strings
Indeed nice...thnx! :yesQuote:
Originally Posted by firefytr
Never mind. :friends:Quote:
Originally Posted by TonyJollans
Great ! :thumb Love to read additional information...:friends:Quote:
Originally Posted by firefytr
On my Pc (Excel 2002, 512 MBRam,WinXp Pro 2.8 GHz) time is the same (0.51243 vs 0.498412)
I just stumbled across this and decided to compare Jake's "million" example (after fixing a few code typos:devil:) with "quick and dirty" stuff I have run (and found virtually no difference among the methods).
On a Dell9100 running 3GHz dual core and 2GzRAM (+ ~ 50 background processes):
Double Quotes: 0.327
vbNullString: 0.327
Len: 0.329
not much difference :dunno
Thanks for the time, MWE. What I notice that you are using a pretty good computer. :eek: Haha! I wonder how can I have such a good hardware nowaday where the oil prices are spiking! :think:Quote:
Originally Posted by MWE
Looking at the article there is also a section on 'initializing' your strings with vbnullstring rather than "".
On it's own, it does look like a good idea... vbnullstring was about 400% faster! BUT [and a big one], If you go and then reassign that string, the one initialized with "" did the reassign much faster. Also- a string that was alreay assigned a value, setting it to "" was also faster than setting it to vbnullstring.
Bottom line, initializing the string to "" or vbnullstring and then assigning a value turn out to be just about identical, so it only makes sense to change code if you have alot of strings that you want to be null or empty and don't plan on assigning it a value... :)
--
I guess you have to be very careful who you trust when lookign for optimization advice :)
http://vbaexpress.com/forum/images/smilies/023.gif Very interesting post http://vbaexpress.com/forum/images/smilies/023.gif Two thumbs up!
I used DRJ's code (with 1 million iterations) for my test. I tried Tony's and I'm not sure if Excel crashed on me or the code takes longer than 15 mins to run http://vbaexpress.com/forum/images/smilies/102.gif , but I wasn't about to try that again.
Intel Pentium 4
2.80 GHz Processor
778 MB RAM
(Avg Secs)
Double Quotes: 0.400507538
vbNullString: 0.400899497
Len: 0.404778894
1/10,000 of a difference between Double Quotes and vbNullString...hmmh
Made an interesting graph too. I thought I could make a mathematical analysis of the behavior, but I really can't. It looks very close to a Sine/Cosine wave (except Len, which pretty much stayed consistent)...I was hoping to find a logarithmic function http://vbaexpress.com/forum/images/smilies/090.gif ....this could have made a decent conclusion to what's the fastest (which would have been finding out what has the lowest limit).
Check it out:
http://img196.imageshack.us/img196/8010/chart9mm.th.png
Thanks, Joseph. Nice graph there!