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

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q108517

SYMPTOMS

In Microsoft Excel, you may receive one or more of the following error messages when you use the Range(Name).Value method in Microsoft Visual Basic for Applications to get a value based on a defined name.

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

-and-

Run-time error '13':
Type mismatch

Back to the top

CAUSE

This error message occurs if the defined name in question refers to a constant value or a formula rather than a range.

Back to the top

RESOLUTION

To prevent the error message from appearing, use the Evaluate method to determine the value of the defined name. Please see the "More Information" section of this article for an example that demonstrates this issue.

Back to the top

MORE INFORMATION

If a particular defined name refers 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

and you use the Range(Name).Value and .Evaluate(Name) methods to get the values of the names, you receive the following results in your macro:

   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]

NOTE: Error messages 1 and 2 are as follows:

Error Message 1

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

Error Message 2

Run-time error '13':
Type mismatch

Back to the top

Sample Visual Basic Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. To check the value of a name, you can use the following subroutine:

   '----------------------------------------------------------------------
   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
   '----------------------------------------------------------------------

In order to prevent an error when the name refers to an error value (in this case, if the name is Golf), use the Names(Name).RefersTo method to check the name before getting its value. For example, you could use:

   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

If the name refers to an error value, the IsError test is true and the error message box is displayed. Otherwise, the OK message box is displayed. For example, if the name is Golf, which refers to =#N/A, the error message box is displayed. Using any of the other example names results in the OK message box.

Back to the top

REFERENCES

For more information about the Evaluate method, from the Visual Basic Editor, click the Office Assistant, type evaluate, click Search, and then click to view "Evaluate Method."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please click the article number below to view the article in the Microsoft Knowledge Base:

176476 (http://support.microsoft.com/kb/176476/EN-US/ ) OFF: Office Assistant Not Answering Visual Basic Questions

For additional information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:

163435 (http://support.microsoft.com/kb/163435/EN-US/ ) VBA: Programming Resources for Visual Basic for Applications

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 5.0c
Microsoft Excel 5.0 for Macintosh
Microsoft Excel 95 Standard Edition

Back to the top

kbdtacode kbprb kbprogramming KB108517

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top