Avatar of koughdur
 asked on

Excel Name goes away when set to empty string

I have an Excel 2010 workbook with Names.  When I set a Name to the empty string it disappears.  Is this known behavior?  Is there a way to keep a name around with an empty string value or do I have to put some sort of dummy value in there?
Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Can you explain further what you mean when you say you "set a Name to the empty string"?

ThisWorkbook.Names("MyName").Value = ""
Martin Liss

Ok, when a Name has to be attached to something so it makes sense that it would go away when it isn't. I don't know why you'd want to keep it around (can you explain?), but certainly assigning something to it would keep it.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

I want to use Names rather than Globals to save values.  A blank string is a valid value that I would want to save.  Mostly I associate names with cells, but these values are not really associated with any particular worksheet or cell.
Martin Liss

Ok, this is a workaround. Run the FindOut sub and it will tell you if the name exists. If it doesn't you can treat it like it's blank.

Function NameExists(nme As String) As Boolean
Dim n As Name

For Each n In Names
    If nme = n.Name Then
        NameExists = True
        Exit Function
    End If

End Function
Sub FindOut()
If Not NameExists("MyName1") Then
    MsgBox "It's gone"
End If
End Sub

Open in new window


Thanks for the idea, but that will be rather cumbersome.  I'm using the Names to store previous values for a bunch of different cells.

When I was using globals I could just remember the values like this:

PrevValue = Range("MyCell").Value

and reset the cell later with the previous value like so:

Range("MyCell").Value = PrevValue

The problem with globals is that they go away if any of the macros error out.  Which means the user has to close and reopen the workbook in order for the globals to be recreated.  I was hoping that Names would stick around, but evidently Bill Gates is too smart for me and wants to save me space by getting rid of a perfectly good name just because it has an empty string in it.

What I'm going to do is find an obscure location on an obscure worksheet and store my Names associated with those cells then I can do this:

Worksheet("Obscure").Range("PrevValue") = Worksheet("Main").Range("MyCell").Value


Worksheet("Main").Range("MyCell").Value = Worksheet("Obscure").range("PrevValue")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wayne Taylor (webtubbs)

...evidently Bill Gates is too smart for me and wants to save me space by getting rid of a perfectly good name just because it has an empty string in it.

This line of code...

    ThisWorkbook.Names("MyName").Value = "" 

...is not setting the Name value to an empty string. It is in fact the equivalent to the RefersTo property in that it refers to the formula (see here). So, while this...

   ThisWorkbook.Names("MyName").Value = "bob"

...looks like it sets the value to "bob", it actually sets the value to ="bob".

With this in mind, instead of using the Value property, how about using the Comment value? That can actually be an empty string and will be retained.

Martin:  Thanks.  I had forgotten about those.  I will look to see if I can implement, but I may go with Named cell ranges as that is what I am using for saving other values.

Wayne:  I am saving some stuff in comments, but there are issues with those as well.  They are a bit kludgy to get things in and out of and the show up as red triangles in the cells.  I'm actually thinking of moving away from those using Named cell ranges for those as well.
Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy