The following method returns a Range object referneced by a NamedRange in an Excel worksheet. Motivation for this article is the fact that VBA does not handle possible errors in a NamedRange. If a referenced range contains errors even in formulas, the simple calll to RefersToRange of a NamedRange will fail.
Source Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
' @Author - Alexander Bolte' @CreationDate - 29.03.2014' @ChangeDate - 29.03.2014' @Description - Encapsulates the Excel.Name.RefersToRange Property as Excel VBA is not providing proper error handling.' If the property is refferring to an invalid Range object, Nothing is returned instead of getting a runtime error.' @Param aName - an Excel.Name object referencing a vlaid Range.' @Returns - Nothing, if getting the Range object fails, else an initialized Range object referencing the Range of provided Name object.FunctiongetRangeFromName(ByRefaNameAsName)AsRangeDimretAsRangeOnErrorGoToerrHandle:Setret=aName.RefersToRangeerrHandle:IfErr.Number<>0ThenErr.ClearSetret=NothingEndIfSetgetRangeFromName=retEndFunction