Results 1 to 19 of 19

Thread: what's the difference between null and ""?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    what's the difference between null and ""?

    I write code to do calculation, use the code to read the excel file and then go to several if conditons, in if, may need use conditions like" if para1="" ", para1 is a parameter and its value is from excel. There are some value in excel is nothing(no value in cells), so I hope use the if conditions to classify them, it seems when run the code, "" is not equal to null(or nothing), so how to figure out this difference and make the if work well?

  2. #2
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: what's the difference between null and ""?

    Hi,

    The NULL value is a special character which represents an Empty field or Object. That is a field or object which contains No value as opposed to a field which may contain a string value of Zero length which can be tested for using something similar to:-

    vb.net Code:
    1. If someValue = "" then...

    To deal with Null Values and Zero Length or Empty Strings then there are two main options available to you. The first is to test for each value individually and combine them using the AndAlso conditional operator and the second option is to use the IsNullOrEmpty Method of the String Class. i.e:-

    vb.net Code:
    1. If Not SomeValue Is Nothing AndAlso Not someValue = String.Empty Then...
    2. or
    3. If Not String.IsNullOrEmpty(someValue) Then...

    Hope that helps.

    Cheers,

    Ian

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: what's the difference between null and ""?

    I'm going to be pedantic for a moment and point out that there is a difference between NULL, Nothing and empty string.

    Empty string is just that - an empty string.
    Nothing means that the object is nothing. It doesn't exist. This is why for objects you use the NEW keyword to create instances with them.
    Null is different from Nothing in that the item DOES exist... it simply has no value. Think of a database table, or as in the case, an Excel sheet... the cells themselves exist... so they are not nothing... but they are empty and contain no data, they are NULL.

    Use the IsNull function to test for them.

    Code:
    If Para1 IsNot Nothing AndAlso Not IsNull(Para1) AndAlso Para1 <> String.Empty Then
    ' ... there's something there
    There are other ways to test for NULL value besides IsNull, but it depends on the data structures being used (example DataTables has it's own means for testing for a null, but if you're not using a DT, then it's useless.)

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by techgnome View Post
    I'm going to be pedantic for a moment and point out that there is a difference between NULL, Nothing and empty string.

    Empty string is just that - an empty string.
    Nothing means that the object is nothing. It doesn't exist. This is why for objects you use the NEW keyword to create instances with them.
    Null is different from Nothing in that the item DOES exist... it simply has no value. Think of a database table, or as in the case, an Excel sheet... the cells themselves exist... so they are not nothing... but they are empty and contain no data, they are NULL.

    Use the IsNull function to test for them.

    Code:
    If Para1 IsNot Nothing AndAlso Not IsNull(Para1) AndAlso Para1 <> String.Empty Then
    ' ... there's something there
    There are other ways to test for NULL value besides IsNull, but it depends on the data structures being used (example DataTables has it's own means for testing for a null, but if you're not using a DT, then it's useless.)

    -tg
    OK, thank you very much

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by techgnome View Post
    I'm going to be pedantic for a moment and point out that there is a difference between NULL, Nothing and empty string.

    Empty string is just that - an empty string.
    Nothing means that the object is nothing. It doesn't exist. This is why for objects you use the NEW keyword to create instances with them.
    Null is different from Nothing in that the item DOES exist... it simply has no value. Think of a database table, or as in the case, an Excel sheet... the cells themselves exist... so they are not nothing... but they are empty and contain no data, they are NULL.

    Use the IsNull function to test for them.

    Code:
    If Para1 IsNot Nothing AndAlso Not IsNull(Para1) AndAlso Para1 <> String.Empty Then
    ' ... there's something there
    There are other ways to test for NULL value besides IsNull, but it depends on the data structures being used (example DataTables has it's own means for testing for a null, but if you're not using a DT, then it's useless.)

    -tg
    OK< thank you very much, also how to do it in VBA? I also need use VBA to do the sam function

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by techgnome View Post
    I'm going to be pedantic for a moment and point out that there is a difference between NULL, Nothing and empty string.

    Empty string is just that - an empty string.
    Nothing means that the object is nothing. It doesn't exist. This is why for objects you use the NEW keyword to create instances with them.
    Null is different from Nothing in that the item DOES exist... it simply has no value. Think of a database table, or as in the case, an Excel sheet... the cells themselves exist... so they are not nothing... but they are empty and contain no data, they are NULL.

    Use the IsNull function to test for them.

    Code:
    If Para1 IsNot Nothing AndAlso Not IsNull(Para1) AndAlso Para1 <> String.Empty Then
    ' ... there's something there
    There are other ways to test for NULL value besides IsNull, but it depends on the data structures being used (example DataTables has it's own means for testing for a null, but if you're not using a DT, then it's useless.)

    -tg
    Also how to do it in VBA? I also need use VBA to do it.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: what's the difference between null and ""?

    VBA is a little different as it doesn't have the AndAlso ... so usually the best thing to do is first check to see if it is nothing ... then if it isn't... then check for the null (using IsNull) and empty string (using "")

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by techgnome View Post
    VBA is a little different as it doesn't have the AndAlso ... so usually the best thing to do is first check to see if it is nothing ... then if it isn't... then check for the null (using IsNull) and empty string (using "")

    -tg
    thank you

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: what's the difference between null and ""?

    I think that in VBA you have to check for Null before checking for an empty string, but I'm pretty sure that you don't ever have to check for nothing. I don't believe that concept really existed in VBA.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by techgnome View Post
    VBA is a little different as it doesn't have the AndAlso ... so usually the best thing to do is first check to see if it is nothing ... then if it isn't... then check for the null (using IsNull) and empty string (using "")

    -tg
    If several value in cell are like "HE", "OK", "Fine", "Nice"(in cell it has no "), other is empty, so when I use if, should I use IsNull(para) or IsEmpty(para) or isNothing(para)?

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    I use if para <>"" as the condition, but when the para is not null, like it has value as Fine, this will be "true", but if when para is null , when debug it, it shows (para=""), so the <>"" seems not work, so how to do with this?

  12. #12
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: what's the difference between null and ""?

    Is this a VBA question or is this a VB.Net question? Because we have a nice office development forum that VBA questions belong in.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by dday9 View Post
    Is this a VBA question or is this a VB.Net question? Because we have a nice office development forum that VBA questions belong in.
    sorry, its a VBA question..

  14. #14
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: what's the difference between null and ""?

    Ok, it has been moved to the office development forum. For future VBA question please ask them here: http://www.vbforums.com/forumdisplay...ce-Development
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by dday9 View Post
    Ok, it has been moved to the office development forum. For future VBA question please ask them here: http://www.vbforums.com/forumdisplay...ce-Development
    Thank you very much.

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: what's the difference between null and ""?

    in excel there is an isempty(cel) which can be used to check for empty cells
    the value of empty cells in excel is never none or nothing

    nothing can be used to check for empty objects, like ranges, worksheets or workbooks
    example
    if you exit a loop iterating cells, the cell variable will contain a range object, but if the loop has finished, by iterating to the end, then the cell variable will be nothing

    often cells in excel may contain some value, eg. a space, even when they look empty
    you can try
    Code:
    if trim(para) <>""
    trim will remove leading and trailing spaces, otherwise do some testing to find exact content of para
    "" = vbnullstring [null is different and can only be tested using isnull()]
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    258

    Re: what's the difference between null and ""?

    Quote Originally Posted by westconn1 View Post
    in excel there is an isempty(cel) which can be used to check for empty cells
    the value of empty cells in excel is never none or nothing

    nothing can be used to check for empty objects, like ranges, worksheets or workbooks
    example
    if you exit a loop iterating cells, the cell variable will contain a range object, but if the loop has finished, by iterating to the end, then the cell variable will be nothing

    often cells in excel may contain some value, eg. a space, even when they look empty
    you can try
    Code:
    if trim(para) <>""
    trim will remove leading and trailing spaces, otherwise do some testing to find exact content of para
    "" = vbnullstring [null is different and can only be tested using isnull()]
    It works well, thank you so much.

  18. #18
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: what's the difference between null and ""?

    Code:
        if x & "" = "" then ....
    where x is a variable



    If you are in Access, you can use the NZ function to convert to a default value to compare (if it's null).


    Rest depends on your situation and whether you need to know if its null, empty etc...

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  19. #19
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: what's the difference between null and ""?

    we used to use x & vbNullString = "" .... but that only works if you treat nulls and empty strings the same... and sometimes there are differences (like between unknown - NULL - or empty such as no response - empty string)

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width