XL: Error Getting the Value of a Name in Visual Basic

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel you may receive an error message when you use the Visual Basic Range(Name).Value method to get a value based on a defined name. This error message will occur if the defined name in question refers to a constant value or a formula rather than a range.

MORE INFORMATION

If a particular name will refer to a constant value or a formula, you can use the .Evaluate(Name) method to get the value of the name. The .Evaluate(Name) method can evaluate formulas and constant values even if they don't refer to an actual range.

However, note that the .Evaluate(Name) method may return an error message if the name refers to an error value, such as #REF! or #N/A. In these cases, you may need to employ error checking (such as the On Error Resume Next function) or use an alternative method, such as the Names(Name).RefersTo method, to detect or handle an error value.

For example, if you have a sheet named Sheet1 in a workbook that contains the following global names

   This
   Name     Refers To               Cell Information
   ---------------------------------------------------------------
   Alpha    =Sheet1!$B$1            Cell B1 contains the number 1
   Bravo    =2
   Charlie  =SUM(Sheet1!$B$3:$C$3)  Cell B3 contains the number 3,
                                 cell C3 contains the number 4
   Delta    =Alpha
   Echo     =Charlie
   Foxtrot  =Alpha+Charlie
   Golf     =#N/A

   Name     Range(Name).Value  Sheets("Sheet1").Evaluate(Name)
   -----------------------------------------------------------
   Alpha    1                  1
   Bravo    [Error message 1]  2
   Charlie  [Error message 1]  7
   Delta    1                  1
   Echo     [Error message 1]  7
   Foxtrot  [Error message 1]  8
   Golf     [Error message 1]  [Error message 2]

Error message 1

   Run-time error '1004':
   Range method of Application class failed

Error message 2

   Run-time error '13':
   Type mismatch

Sample Visual Basic Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   '----------------------------------------------------------------------
   Option Explicit

   Sub CheckNameValue()
      Dim Test As Variant
      Test = Range("Alpha").Value

      'To check the value by using .Evaluate(Name), use
      '
      '   Test = Sheets("Sheet1").Evaluate("Alpha")
      '
      'in place of the previous test line.

      MsgBox Test
   End Sub
   '----------------------------------------------------------------------

   Sub CheckForError()

      'If the name Golf refers to an error value, such as #REF! or #N/A,
      If IsError(Evaluate(Names("Golf").RefersTo)) Then
         'then show an error message to that effect,
         MsgBox "Golf is an error name!"
      Else
         'otherwise state that the name refers to a good reference.
         MsgBox "Golf is OK!"
      End If
   End Sub

REFERENCES

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications