Using Excel Names Collection to store a number as text


  • I often use the Names Collection to dynamically store constants.

    I need to add a numeric text string to the Names Collection, but when I do, Excel converts the string to a numeric value.

    For example, this code snippet

    thisID = "0011223"
    ThisWorkbook.Names.Add Name:="myID", Refersto:=thisID

    stores 11223 as the value for "myID" in Names, not "0011223"

    I'd like to retain the string represtation for this number.

    I've tried using Refersto:=CStr(thisID), but that does not help.

    Any suggestions would be appreciated!

    Wednesday, August 28, 2013 3:37 PM


  • Are your IDs always 7 characters long? If so you can ignore the fact that Excel converts to a numeric and reformat when you retrieve the value [for example, thisID.tostring("0000000")]
    Wednesday, August 28, 2013 4:43 PM