Using Excel Defined Names in VBA RRS feed

  • Question

  • Hi,

    In VBA, I can successfully use an Excel defined name which refers to a cell.  For instance, if I define a name "ValueInA1", which refers to cell A1, the following VBA code will display the contents of cell A1 in a message box.

    Variable1 = Range("ValueInA1")

    MsgBox = Variable1

    However if I create a defined name containing a constant or formula, and try to use it in VBA as above, it generates a Run-time error '1004', with the comment "Method 'Range' of object '_Global' failed.  Can you tell me how to use defined names referring to constants or formulas, in VBA?

    Saturday, February 27, 2016 4:14 AM

All replies

  • Hi,

    "MsgBox" is not a variable, don't use equal(=) after MsgBox.
    ' -- sample 1 (redundant a little) ---
    Dim Variable1 As String
    Variable1 = Range("ValueInA1")
    MsgBox Variable1
    ' -- sample 2 (simplest) ---
    MsgBox Range("ValueInA1")

    Saturday, February 27, 2016 7:48 AM
  • I put formula but it works despite I declare string or integer.

    Sub GetC3()
      Dim int1 As Integer
      int1 = Range("ValueC3")
      Dim str As String
      str = Range("ValueC3")
    End Sub


    Sunday, February 28, 2016 10:15 PM
  • dim Variable1 as variant


    MsgBox Variable1 & " is in cell A1", vbOKOnly, "Console"

    • Edited by Soliddrew Monday, February 29, 2016 2:20 PM
    • Proposed as answer by Soliddrew Monday, February 29, 2016 2:20 PM
    Monday, February 29, 2016 2:19 PM