Listing Retrieving Values Stored as a Workbook Name Using the Evaluate Method

Sub TestWorkbookNameValue() Dim vValue As Variant vValue = Application.NamesO'SalesTaxRate'O.RefersTo Debug.Print "Value retrieved using RefersTo: " & vValue vValue = Application.NamesO'SalesTaxRate'O.Value Debug.Print "Value retrieved using Value: " & vValue

' this next line doesn't work because the name ' doesn't refer to a range. Intentionally commented out. ' vValue = Application.Names("SalesTaxRate").RefersToRange vValue = Application.Evaluate("SalesTaxRate") Debug.Print "Value retrieved using Evaluate: " & vValue End Sub

This listing produces the following output.

Value retrieved using RefersTo: =0.06 Value retrieved using Value: =0.06 Value retrieved using Evaluate: 0.06

Notice that if you do not use Evaluate, the value retrieved from the Name will include an equals sign (=). If the value is text data, you'll also get the value enclosed in quotes.

One of the primary benefits of this method is that you can be consistent in how you use literal values within the workbook and your VBA code. For example, the SalesTaxRate name in Listing 13.2 could also be used in any worksheet formulas that need to reference the sales tax rate.

0 0

Post a comment