Page 1 of 2 12 LastLast
Results 1 to 10 of 14

vbNullString

This is a discussion on vbNullString within the Excel Questions forums, part of the Question Forums category; Hello, What's the difference when you use vbNullString or "". For example: TextBox1.Text = " " TextBox1.Text = "vbNullString" Are ...

  1. #1
    Board Regular
    Join Date
    Apr 2007
    Location
    Sofia
    Posts
    350

    Wink vbNullString

    Hello,

    What's the difference when you use vbNullString or "". For example:

    TextBox1.Text = " "
    TextBox1.Text = "vbNullString"

    Are you saving memory or anything else?
    We are the champions, my friend!

  2. #2
    Board Regular
    Join Date
    Jan 2003
    Location
    CA, Bay Area
    Posts
    2,062

    Default Re: vbNullString

    As near as I can tell, you have a space between the quote marks in your first example. If you had put just two quote marks side-by-side without a space in between, then that would be similar to vbNullString.

    try this and you will note that putting a space between the quote marks results in a LEN of 1 for cell A1. (By the way, you would not use quote marks around vbNullString as you did in your example)...:

    Sub testme()
    Range("a1") = " "
    Range("a2") = vbNullString
    Range("b1") = "=len(a1)"
    Range("b2") = "=len(a2)"

    End Sub
    Give a man a fish & you've fed him once. Teach a man to fish & you feed him for a lifetime. Give a man a fish a day & he becomes a spoiled adult still living with his parents at 35.

  3. #3
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: vbNullString

    Hi

    Your example should have been this:

    Code:
    TextBox1.Text = ""  'note no space between quotes!
    TextBox1.Text = vbNullString
    Close to no difference between them I believe (in the Immediate Window of the VBE try:

    ?vbNullString=""

    wich will return True. The only difference I can find is if you use the StrPtr function eg in the Immediate Window:

    ?StrPtr(vbNullString)

    and

    ?StrPtr("")

    return 2 different values. Does it matter? No idea...
    Richard Schollar

    Using xl2013

  4. #4
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,402

    Default Re: vbNullString

    There's a difference in terms of overhead, "" uses 6 bytes.

    Also, the quickest way to check for an empty string is to use the LenB function.

    http://www.aivosto.com/vbtips/stringopt.html

  5. #5
    Board Regular
    Join Date
    Apr 2007
    Location
    Sofia
    Posts
    350

    Default Re: vbNullString

    Yes Colin, this link seems to be very useful on the topic.

    As I still cannot call myself a programmer, can you tell me if VBA and VB6 are covering 1:1? I mean everything as an example from VB6 can i transform to VBA for Excel, Outlook etc.?
    We are the champions, my friend!

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: vbNullString

    VB6 is VBA but with Windows Forms (more or less I believe) - so VBA is a subset of VB6. With the exception of the Application object models, they should be interchangeable.
    Richard Schollar

    Using xl2013

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,839

    Default Re: vbNullString

    They are not identical but VBA and VB6 are very similar (though you don't have the Clipboard, Printer or Screen objects, or resource files). Their implementation of Forms are different though.

  8. #8
    Board Regular
    Join Date
    Apr 2007
    Location
    Sofia
    Posts
    350

    Default Re: vbNullString

    Yes, I see that Windows Forms are different from VB Forms..for example ListBox control. Do you know the answer to this topic:

    VBA ListBox MultiColumn change Font?
    VBA ListBox MultiColumn change Font?
    We are the champions, my friend!

  9. #9
    Board Regular
    Join Date
    Apr 2007
    Location
    Sofia
    Posts
    350

    Default Re: vbNullString

    Thanks for all your input guys
    We are the champions, my friend!

  10. #10
    New Member
    Join Date
    Feb 2014
    Posts
    4

    Default Re: vbNullString

    I recall running across a thread where a null value in an excel cell is treated differently than "" and there were functions where a null value was ok while "" would produce errors. The issue was that there was no way to reset a null value via Excel formula and I'm wondering if vbNullString could provide a solution.

    Perhaps not, since it's likely to be the case that the "" value was a result from an Excel formula and it seems to me that you still can't change it to a null value without deleting the Excel formula.

    I guess my question is, assuming vbNullString is a trully null value, i.e. same as the value of an empty cell on a new worksheet, can I use this to create a null function for Excel Formulas to use instead of ""?

    Putting = vbNullString directly into a cell isn't recognized (#Name? error). I've never created a function before so I'm only asking (instead of trying it myself) in case I'd be learning how to create a function only to pursue a dead end.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com