I would like all to test this code on their machine and give us the results...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...
I would like all to test this code on their machine and give us the results...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...
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
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!
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
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
Good Research !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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Indeed nice...thnx!Originally Posted by firefytr
_________
Groetjes,
Joost Verdaasdonk
M.O.S. Master
Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
(I don't answer questions asked through E-mail or PM's)
Never mind.Originally Posted by TonyJollans
Great ! Love to read additional information...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) 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
"It's not just the due date that's important, it's also the do date" [MWE]
When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.
Thanks for the time, MWE. What I notice that you are using a pretty good computer. Haha! I wonder how can I have such a good hardware nowaday where the oil prices are spiking!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
Very interesting post 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 , 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 ....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:
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Thanks, Joseph. Nice graph there!