Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 37

Thread: Optimize String Handling in Visual Basic

  1. #1
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location

    Lightbulb Optimize String Handling in Visual Basic

    Hi all,
    I just want to share some info here. Do I post at the right place? 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.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi,

    Always nice to read a good article...thnx!
    _________
    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)

  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    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

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi Tony,

    The vbNullString part was a chocker to me as well!
    I new it was faster...but I doubt if it cuts processing time in half..

    I use it anyway as it is good practice.
    _________
    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)

  5. #5
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by MOS MASTER
    ...but I doubt if it cuts processing time in half..
    For that line, maybe, but not the whole procedure! LOL!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  6. #6
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by kpuls
    For that line, maybe, but not the whole procedure! LOL!
    I kind a thought the same thing!
    _________
    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)

  7. #7
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Just to share the knowledge wit everyone...

  8. #8
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    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

  10. #10
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    I always thought that "" should be used over vbNullString. So which is really better? We can easily put it to the test.
    [vba]
    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(D221)"
    Range("B").EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub
    [/vba]

    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).

    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.

  11. #11
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by DRJ
    I always thought that "" should be used over vbNullString. So which is really better? We can easily put it to the test.
    [vba]
    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(D221)"
    Range("B").EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub
    [/vba]

    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).

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

    Well, maybe in a larger scale of code, it might have a diffrence...
    Who knows?

  12. #12
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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:

    [vba]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[/vba]

    For me, after 50 loops, vbnullstring = .8225, "" = 1.51838

    The weird part is that while vbnullstring starts and stays fast, "" seems to slow down after a few iterations.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #13
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by kpuls
    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:

    [vba]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[/vba]

    For me, after 50 loops, vbnullstring = .8225, "" = 1.51838

    The weird part is that while vbnullstring starts and stays fast, "" seems to slow down after a few iterations.
    Hmm....That's a very good research on what u have done...Keep it up ....
    I sure there is a reason for what is happening...

  14. #14
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Ok, when I run Ken's code I get this.

    "" = .9912
    vbNullString = 1.01062

    About a 2 percent difference (on 500,000 iterations).

  15. #15
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Okay, with more runs, the time differences for me seem to be coming in +- 10% either way. ie, no major performance gain stands out over the other.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  16. #16
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    So what happens if we increase the iterations. Let's try 1 million iterations each.

    [vba]
    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" & UpperLimit & ")"
    Range("B").EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub
    [/vba]

    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.

    I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.

    So basically, choose your method of choice.

  17. #17
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  18. #18
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by kpuls
    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...


  19. #19
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by sheeeng
    Hi all,
    I just want to share some info here. Do I post at the right place? 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.
    I think I need to rephrase my statement here....
    vbNullString WILL NOT cut execution time down by half and more!

  20. #20
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by DRJ
    So what happens if we increase the iterations. Let's try 1 million iterations each.

    [vba]
    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" & UpperLimit & ")"
    Range("B").EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub
    [/vba]

    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.

    I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.

    So basically, choose you method of choice.
    I tried your code already...

    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..... About 4 minutes of execution time...

    "" = 0.525535

    vbNullString =0.453905

    Len = 0.4604

    Initial testing = vbNullString faster

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •