Trying to understand a line of code re: Dynamic Named Rangesfrom Mr Excel's textbook

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62
This line of code is in Mr Excel's text VBA and Macro's Excel 2010 page 151.

It is in reference to storing a formula in a Name, and allows for a dynamic named column.

Code:
Names.Add Name:="ProductList", RefersTo:="=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A))"
I am trying to understand what precisely this line of code is doing.
From what I can glean on the Net, the OFFSET function can take 3, 4 or 5 variables, depending on what the user wants.
The last 2 optional variables define how many rows and columns of data the user wants the function to return. I assume this would be the equivalent to a Resize function.

I also understand from the Net that the COUNTA function returns the amount of cells in a column that contain data.
Am I correct on both assumptions?

If I am does that mean the line of code above uses 4 variables being:
a. The starting cell is Sheet2, cell A2
b. The function moves moves 0 rows
c. The function moves 0 columns
d. The function returns the amount of rows defined by the amount of cells in Sheet2, Column A?

And then the code refers all this back to a Name called ProductList?

Is that all accurate?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
You're correct that the Offset worksheet formula is much like the combination of VBA's Offset(Rows,Cols).Resize(Rows,Cols).

Your description is close; however it might be easier to think of it like this:

The RefersTo:= Formula will return a reference to a range.
1. That range's TopLeft Cell is Sheet2, Cell A2 (which is A2 Offset 0 Rows, 0 Columns)
2. The Height of the Range in Rows is the current number of Non-Blank cells in Column A.
3. Since there is no Width specified, the range is 1 Column wide since Cell A2 is 1 column wide.
 

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62
Thanks JS.
Good to know the default value for the last 2 variables is 1.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Thanks JS.
Good to know the default value for the last 2 variables is 1.

It's not exactly. I thought it was before I responded to your post.
Looking at Excel Help, I learned if the Height or Width parameters are missing, it uses the Height or Width of the range reference.

So in your example if you had....

"=OFFSET(Sheet2!$A$2:$C$3,0,0,COUNTA...

The default would be 2 Rows High by 3 Columns Wide.

Glad that you asked the question, because I learned from trying to answer it. :)
 

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62

ADVERTISEMENT

I thought I had understood the syntax of the OFFSET command, and clearly I do not. The syntax I extracted from Mr Excel's text VBA and Macro's Excel 2010, page 151.

A sheet called SourceData consists of 5 cells in column A.
A1 is the header Departments
A2 is Marketing
A3 is IT
A4 is HR
A5 is Sales

There is no data in any other cells on the sheet.

My original line of code that uses a Named Range Formula is in the first post in this thread, but I will put here again.
Code:
Names.Add Name:="DeptNameList", RefersTo:="=OFFSET(SourceData!$A$2,0,0,COUNTA(SourceData!$A:$A))"
What I expect this code to do is capture 4 cells of data, starting with cell A2. I would also expect the size of the data to be captured to be 4 rows in 1 column only.

But when I dump the data into an array, using this line of code:

Code:
DeptNameArray = Range("DeptNameList")
it appears I am capturing 5 rows of data across 2 columns.

I know this 2 ways:

1. When I do any work using the array, if I do not define it as a 2 dimensional array I get errors.
2. When I use the line of code below, the DEbugger shows the counter DeptArrayRowCounter goes up to 5, when I expect only 4.

Code:
For DeptArrayRowCounter = LBound(DeptNameArray) To UBound(DeptNameArray)
Any ideas on what is wrong with my code or concepts of the OFFSET command?

Complete code for the subroutine below. It has proper syntax, and the sub runs, but the output is garbage.

Code:
Sub GetExpensesByDept()
'
' GetExpensesByDept Macro

'
' Keyboard Shortcut: Ctrl+g
'

Dim DeptNameArray As Variant
Dim ExpensesandDeptArray As Variant
Dim DeptArrayRowCounter As Integer
Dim ExpenseArrayRowCounter As Integer
Dim TotalforSpecificEXpense As Long


'Create a Name DeptNameListthat grabs the possible values for Dept Names from Column A of the sheet SourceData'
'The formula associated with this name accommodates a dynamic length of rows in Column A. Code from text, page 151'
Names.Add Name:="DeptNameList", RefersTo:="=OFFSET(SourceData!$A$2,0,0,COUNTA(SourceData!$A:$A))"
'Create a Name that grabs the possible values for Dept Names from Column A of the sheet SourceData'
'The formula associated with this name accommodates a dynamic length of rows in Column A. Code from text, page 151'
Names.Add Name:="RowsofDeptExpenses", RefersTo:="=OFFSET(Expenses!$C$2,0,0,COUNTA(Expenses!$C:$C),2)"


DeptNameArray = Range("DeptNameList")
ExpensesandDeptArray = Range("RowsofDeptExpenses")

For DeptArrayRowCounter = LBound(DeptNameArray) To UBound(DeptNameArray)
    Worksheets("Expenses").Cells(DeptArrayRowCounter + 13, 1).Value = DeptNameArray(DeptArrayRowCounter, 1)
    TotalforSpecificEXpense = 0
    For ExpenseArrayRowCounter = LBound(ExpensesandDeptArray) To UBound(ExpensesandDeptArray)
        If DeptNameArray(DeptArrayRowCounter, 1) = ExpensesandDeptArray(ExpenseArrayRowCounter, 1) Then
            TotalforSpecificEXpense = TotalforSpecificEXpense = ExpensesandDeptArray(ExpenseArrayRowCounter, 2)
        End If
    Next ExpenseArrayRowCounter
    Worksheets("Expenses").Cells(ArrayRowCounter + 13, 2).Value = TotalforSpecificEXpense
Next DeptArrayRowCounter


End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need to deduct 1 from the count:

=OFFSET(SourceData!$A$2,0,0,COUNTA(SourceData!$A:$A)-1)

The formula is counting the whole of column A so you need to adjust for the heading.
 

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62

ADVERTISEMENT

You need to deduct 1 from the count:

=OFFSET(SourceData!$A$2,0,0,COUNTA(SourceData!$A:$A)-1)

The formula is counting the whole of column A so you need to adjust for the heading.


Thanks. I will test that.

But I still don't understand why my first line of OFFSET code is producing a 2 dimensional array. The 2nd line of OFFSET code I can see producing a 2 dimensional array since I am explicitly calling for 2 columns of data to be captured with the 5th variable. But in the first OFFSET command, I do not declare the 5th variable, which I thought would default to one column of data.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
When you assign a range to a variant variable, the variable is always a 2D array even if the range was a single column of data.
 

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62
When you assign a range to a variant variable, the variable is always a 2D array even if the range was a single column of data.

Ah...another critical thing I did not know.

However, this is still not working.

I have re-declared DeptNameArray as a Dynamic Array by:
Code:
Dim DeptNameArray()
but now getting a subscript out of range error with the following altered line of code:
Code:
Worksheets("Expenses").Cells(DeptArrayRowCounter + 13, 1).Value = DeptNameArray(DeptArrayRowCounter)
I am clearly missing something very fundamental in my understanding of arrays, but should this not create a one-dimensional array?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In your original code I get a compile error on this line:

Rich (BB code):
Worksheets("Expenses").Cells(ArrayRowCounter + 13, 2).Value = TotalforSpecificEXpense

I use Option Explicit and I recommend that you do too.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,519
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top