User FAQs

If you have a question, please send it to us.


1) What are the advantages of using named ranges ?
You can insert or delete cells, rows and columns and the cell references remain intact.


2) What is the difference between a Workbook Level named range and a Worksheet Level named range ?
Workbook Level - These can be referenced from any worksheet in the workbook, without prefixing.

=Name_Wbk 

Worksheet Level - These are intended to only be used on one specific worksheet although you can reference them from other worksheets by prefixing the name of the worksheet.


3) What type of named range is created when you use the Name Box ?
Typing into the Name Box creates a named range with workbook level scope.
You can create worksheet level scope if you proceed it with the name of the worksheet and an exclamation mark.

=Sheet1!MyWorksheetLevel 

4) What is a dynamic named range ?
A dynamic named range expands automatically when more data is added to the range.
This type of named range uses a combination of the worksheet functions OFFSET and COUNTA.

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$1:$B$100),1) 

5) Write the code to populate an array with the values in a named range ?
The following lines are all equivalent.

Dim arValues As Variant 
arValues = Range(NamedRange).Value
arValues = Application.Evaluate[NamedRange].Value
arValues = [NamedRange] '// since value is the default


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext