1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What is "#" represnt in Excel Or what & how # use in excel, How Excel interpret this symbol?

Discussion in 'VBA Macros' started by Chirag R Raval, Dec 4, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Dear All,

    I have a confusion that what is the use of Symbol # in excel? or how excel interpret
    this "#"symbol?

    reference is below thread of this site .

    (1) https://chandoo.org/forum/threads/h...-to-n-a-in-columns-e-and-f.40201/#post-240467

    in below thread ,post no 2 is unanswered.

    (2) https://chandoo.org/forum/threads/e...ing-to-eliminate-ascii-160.39672/#post-237450.

    (3) I also try here

    (4) https://www.quora.com/What-does-the-symbol-do-in-Excel-It-keeps-showing-up-as-why-is-this-happening
    Just for clarification of knowledge what is "#" is in excel/ Excel VBA?

    Please guide.


    Chirag Raval
  2. Debaser

    Debaser Active Member

    It's just a placeholder; it has no special meaning in that code.
  3. Chirag R Raval

    Chirag R Raval Member

    Dear Sir,

    Sorry Sir, i can not understand.

    can you spread some more focus on it?

    What type of place holder it is?
    Is it place holder for Character?String?Date?Object?
    How to precisely use it in excel vba? what is the scope of it in
    VBA & formulas?

    It can be use in loop as " for each #" in range ??

    Can you give some example using it?.

    I search on net but not found anything that use it .
    also please share some links if you found as of use # in excel VBA.

    it is only 1 option as place holder? or there are more options there?


    Chirag Raval
    Last edited: Dec 4, 2018
  4. Debaser

    Debaser Active Member

    It's just text that gets replaced in the formula string (with the address of a range). The fact that it is # in the example is just chance. It could equally be | or @ or any other text that doesn't appear in the formula. The use of # has no special significance at all.

    The formula string starts out as:

    and then the Replace function just replaces "#" with the address of the specified range:

    Code (vb):
    Replace("IF(#>"""",UPPER(#),"""")", "#", .Address)
    so the formula becomes for example:

  5. Chihiro

    Chihiro Excel Ninja

    How, Excel interprets # depends on context.

    As Debaser explained, in code examples you have in link, it's just place holder, which is then replaced with range.address.

    There are other uses for # sign.

    1. When used after a variable. It declares variable as double type. You might have seen Marc L or Jindon use this type of declaration in their code in this forum.
    Code (vb):
    a# = 1.1
    You can read more about this in link below.

    2. When used in front of variable. It assigns it to OS/Processor variable.
    Commonly used when working with FreeFile() to export & import text files.
    See shrivallabha's post for brief explanation.

    3. When used in front of If statements. It's used as conditional compilation.
    Often used to check OS or other built-in conditional compilation constant.

    Ex: making api code compatible between 32 & 64 bit.
    Code (vb):
    #if Win64 then
      Declare PtrSafe Function MyMathFunc Lib "User32" (ByVal N As LongLong) As LongLong
      Declare Function MyMathFunc Lib "User32" (ByVal N As Long) As Long
    #end if
  6. Khalid NGO

    Khalid NGO Excel Ninja

    Other than VBA....!

    New Dynamic Array Formulas in Office 365 Version.

    # is added for New Array Reference Notation, e.g. B2# refers to full range B2:B10

Share This Page