Results 1 to 13 of 13
  1. #1
    kid_flow
    Guest

    How do I have a fn write a blank cell (not a null) (Excel 97)

    I'm hoping this is a simple question. I've created a spreadsheet and am using a function that will do a logical test on a cell and return the value of that cell if the cell contains data or return a null ("") if the cell is blank.

    Unfortunately the null string that I am using is not acceptable as when I convert my file to comma delimited (.csv) form, the null's are read as data and themselves delimited. Using a space (ie " ") was suggested but again the space get delimited when converted as well.

    Does anyone know of a parameter/variable/function I can use in excel that will return a blank into a cell?

    Thanx Much in advance.

    K-Flow.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    When you export to .csv, the .csv filter is going to consider each cell to be field to be exported. As far as I know, there is nothing that you can put in a cell that will prevent a value from being exported to the .csv file. The only alternatives I can think are:

    1- Delete all empty cells that have non empty cells to their right. That would put all of the exported "" at the end of each record.

    2- Write your own VBA code to output the data the way you want it.
    Legare Coleman

  3. #3
    kid_flow
    Guest

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Thanx Legare for your response...

    FYI: I've previously used the "ISBLANK" function as a logical test so I know Excel can distinguish between null and a true blank. It would amaze me if there does not exist a function "MAKEBLANK" (or something analogous) then again... it is MS. ;-)

    I have considered writing a macro that will do this but I C no need for me to have to do that.

    Finding this function or parameter is all that's holding me back. I eagerly await a solution.

    Thanx again for any input anyone can give.

    Cheers.

    K-Flow

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    What is your definition of "blank/"

    If your definition is that the IsBlank function returns True, then what you want is the delete key. Selecting a cell and pressing delete will cause IsBlank to return True for that cell. From VBA, either of the following will also accomplish that:

    <pre> ActiveSheet.Range("A1") = ""
    ActiveSheet.Range("A1").Clear
    </pre>


    However, none of those will keep the "" from being exported for the cell to a .csv file.

    So, you already have your "MakeBlank" in several different ways. However, the .csv filter is still going to output a "" for a blank cell.
    Legare Coleman

  5. #5
    kid_flow
    Guest

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Legare,

    Your second suggestion (.clear) looks quite promising. I'll need a day or two to implement it but I certainly will post my results regarless of the outcome.

    Thank you for all your efforts.

    Take good care.

    K-Flow

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    LegareColeman, I also could use a "ReturnBlank" from a _function_ . This way my XY diagrams are not fouled up and it would look much better than with my current method, i.e. returning 'CVErr(xlErrNA)'.

    Either I misuderstood the thread altogether or how do you apply '.Clear' from within a _function_ ? You can put this statement into a sub of course, but the moment you call this auxiliary sub from your function it will fail to execute.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    You can not use .Clear from a function. Functions can not modify a worksheet, they can only return a value to the cell which invoked the function.

    If the function is defined as returning either a String or Variant, then you can simply return a null string like this:

    <pre>Public Function ReturnNull() As Variant
    ReturnNull = ""
    End Function
    </pre>

    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Thank you for your answer, problem is that "" looks emtpy but is considered as 'IsBlank=False' by the functions. And a XY Chart will be metamorphosed into a Line diagram if it contains a "" 'value'. Arghhh....

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Sorry, I misunderstood what you were trying to do. If you put a formula into the cell, then IsBlank is going to return False, no matter what value the formula returns. IsBlank will only return True if the cell is completely empty. As you know, a function can not modify a cell any other way than returning a value value, therefore, a function can not clear a formula from a cell.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    The more I know about Excel the more it irritates me.
    value of vbEmpty=0
    value of vbNull=1
    'value' of "" is a text
    Why the hell can 'they' not define a return a value which is considered as empty, void, zilch, niente, nada by the other functions ? And all these automatisms like metamorphosing a diagram without asking back.

    I better stop now. Thank you for your help.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Well, actually they have. "" is empty after it is placed in a cell. The problem is that some functions look at the cell contents and not its value and if the contents contain a formula those functions do not consider the cell empty, even if the value returned by the formula is "". That is why ISBLANK returns True for a cell that contains "" but False for cell that contains =ReturnBlank() even if the returned value is "".
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    Strange, I experience other behaviour (at least for Office 97 SR-2)

    a) "" is considered as text by the IsText function, it only 'looks' empty.

    [img]/forums/images/smilies/cool.gif[/img] A cell entry of ="" is considered as False by the IsBlank function, same as with a function return equal to ""

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I have a fn write a blank cell (not a null) (Excel 97)

    None of those are different from what I get. If you type "" into a cell, it will display and it is considered text. If you use a statement in VBA like the following:

    <pre> ActiveCell.Value = ""
    </pre>


    That will put a null string into the active cell, and ISBLANK and ISNULL will return TRUE.

    If you put ="" or a formula with a function that returns a null string into a cell, then both of those will display an empty cell. However, since the cell contains a formula, ISBLANK and ISNULL will return FALSE.
    Legare Coleman

Posting Permissions

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