Problems evaluating defined names in VBA

[andersmatsson]

I have a project where I work a lot with dynamic names and ranges. I
have defined names such as the one below

TotalNumberOfRowsData=COUNTA(Rawdata!$B:$B)

I am referring to these ranges from the worksheet as well as from VBA.
Everything has worked like a charm, but now I want to make it more
generic. I have defined some names that are initiated at startup from
constants, which makes them easy to change. The constants refer to
which columns certain data can be found etc. I then use these
initiated names in the other defined names, by using the indirect
function in different ways (not very pretty if I may say so myself,
but the only way that I found to be working). By doing like this, I
get the option to get functions similar to the one below by just
changing two constants (the contstants for the sheetname and for the
column).

TotalNumberOfRowsData=COUNTA(OtherData!$C:$C)

This also works as long as I stay in the worksheet, but when I try to
access these names in VBA with the evaluate function, I get error 2023
(or totally incorrect answers).

The only explanation I can find is that the indirect function does not
work really well with evaluate, but I haven't found out how to change
it. Does anyone know of any way to come around this problem (it does
not need to be with the indirect function, it can be any other way) I
would be very grateful.

I have found a way by inserting an equal sign in front of the name,
but this only works when indirect is in the first position of the
name.

//Anders

 

[Bill Renaud]

Are you using something similar to that below to refer to your named
ranges?

Dim rngTotalNumberOfRowsData as Range
Set rngTotalNumberOfRowsData =
ActiveWorkbook.Names("TotalNumberOfRowsData").RefersToRange

This works as long as your names are "global".

I am a little confused by your 2 examples, first:
TotalNumberOfRowsData=COUNTA(Rawdata!$B:$B)

....then further down:
TotalNumberOfRowsData=COUNTA(OtherData!$C:$C)

Does this mean that you are using sheet-level names? You would have to
include the worksheet name when attempting to get the reference:
Set rngTotalNumberOfRowsData =
ActiveWorkbook.Names("Sheet1!TotalNumberOfRowsData").RefersToRange

....then change the reference when processing the next sheet (?).

 

[andersmatsson]

Thank's for the quick reply!

I am actually not trying to refer to the ranges as such, but to the
evaluated values from them (the ones that I am trying to access from
VBA are only one-dimensional).

Sorry for the confusing examples. The names that I am using are all
global. Let me try to elaborate a little bit more. The name below
would give me the number of non-empty cells in column B on sheet
Rawdata.

TotalNumberOfRowsData=COUNTA(Rawdata!$B:$B)

However, I will come into situations where i need to set up this
workbook where the data that is sought is in another column or
worksheet. Instead of hardcoding everything, I've set the names up
like the one below, where RawDataSheetName and DataColumnsMonth are
other defined names, but where the values are initiated by constants
upon opening the workbook.

TotalNumberOfRowsData = COUNTA(INDIRECT("'" &RawDataSheetName
&"'!"&SUBSTITUTE(ADDRESS(1,DataColumnsMonth1,3,,),
1,"")&":"&SUBSTITUTE(ADDRESS(1,DataColumnsMonth1,3,,),1,""),TRUE))

This all works fine in the worksheet, but when I use the evaluate
function in VBA, it gives me an error and I cannot figure out why.

I am using the statement
Application.Evaluate("TotalNumberOfRowsData")

and am expecting to get the same answer as I get in the worksheet.

I hope this was a little bit clearer, although I know that the defined
names that I have made are a mess and that it it probably possible to
do it easier, but I haven't figured out how.

Happy for all the help!
Regards,
Anders

 

[Bill Renaud]

You have "DataColumnsMonth1" instead of "DataColumnsMonth" in both places
in your formula:

SUBSTITUTE(ADDRESS(1,DataColumnsMonth1,3,,)...

Is this what you want? Is there supposed to be a comma before the "1,2,,"
part?

Also, if you want to get the value of this named range, I would try:

Dim rngTotalNumberOfRowsData as Range
Dim TotalNumberOfRowsData as Long

Set rngTotalNumberOfRowsData =
Names("TotalNumberOfRowsData").RefersToRange

Application.Calculate
TotalNumberOfRowsData = rngTotalNumberOfRowsData.Value

Application.Calculate is used make sure the workbook has been calculated
(it may already be). The last statement should return the value of the
cell, which is what I think you want. I have never used the Evaluate
function when fetching pre-calculated values from worksheet cells. Try
stepping through this example and watch the Locals window.

I think you can also use a shorter version:

Dim TotalNumberOfRowsData as Long

TotalNumberOfRowsData = Range("TotalNumberOfRowsData").Value

....although I prefer the previous, slightly longer version, as it allows me
to see the reference to the cell (rngTotalNumberOfRowsData) in the Locals
window while stepping through the code (makes debugging easier).

HTH

 

[andersmatsson]

Thank's for all the help! I'm amazed that you take the time to help
me! First, it is supposed to be Datacolumnsmonth1 on both occasions (I
have from 1 to 12) and it is equal to 2, so the expression
SUBSTITUTE(ADDRESS(1,DataColumnsMonth1,3,,), 1,"") renders a "$B" and
the whole ugly indirect expression renders "Rawdata!$B:$B". I know it
is a lengthy way to get the B column, but that was the best way that I
could think of.

But then to the problem as such. Unfortunately I cannot use the range
methods, since the names do not refer to ranges as such, but to named
formulas. I have done a workaround, where I defined a UDF that would
copy in the value/function of the name in a cell and then I pick up
the result from that cell. That is of course a little bit slower,
since I have to jump between VBA and a worksheet, but that was the
only way I found to be working. So instead of using

Application.Evaluate("TotalNumberOfRowsData")

I use

EvaluateName("TotalNumberOfRowsData")

where EvaluateName is defined as below.

Function EvaluateName(InputName As String)
With Worksheets(HIDDENRANGESHEET)
.Cells(1, 2) = ActiveWorkbook.Names(InputName).Value
EvaluateName = .Cells(1, 2)
End With
End Function

This works and I get my answers. It still does not explain why I
couldn't use evaluate in the first place, but at least the program
runs.
Btw, what is the "locals window"?

Thanks and regards,
Anders M

 

[Peter T]

Application.Evaluate("TotalNumberOfRowsData")

It still does not explain why I
couldn't use evaluate in the first place

try -
Application.Evaluate(ActiveWorkbook.names("TotalNumberOfRowsData").Refersto)

Btw, what is the "locals window"?

put a break in your code, when it stops do View, Locals Window; or press
Alt-v, s

Regards,
Peter T

 

[Bill Renaud]

Hi Anders M,

Just for extra info:

When I work with dynamic charts, I have found it easier to add a hidden
worksheet named "Scratchpad" to the workbook. I then enter most of my
formulas on this sheet. I can then see them easily and build up formulas
sort of piece-meal and verify them as I continue to build the workbook.

So for example, I have a gas mileage log for my car. The Mileage worksheet
contains all of the data. On the Scratchpad worksheet, I have the following
formulas with names assigned to them:

LastRow =COUNTA(Mileage!A:A)
addrDatabase
=ADDRESS(1,1,1,TRUE,"Mileage")&":"&ADDRESS(LastRow,14,1,TRUE)

addrDatabase currently evaluates to:
Mileage!$A$1:$N$153

....then I assign the name "Database" (in the Define Names dialog box) to
refer to =INDIRECT(addrDatabase).

Notice that addrDatabase above includes the "Mileage" worksheet name in the
formula.

I have other formulas also (for each column of data), which are handled
similarly, and are then used to build charts.

 

[andersmatsson]

Hi,

Thank's for the reply, both of you!

Thank's for the info of the locals window. I will definately use that
a lot for debugging in the future. Regarding using the longer
statement and .refersto, it unfortunately doesn't work. I still think
that there is some bug in evaluating the indirect statement (or that I
am using it in the wrong way).

For this project, I think I will stick with the lengthy way of going
back and forth between the worksheet and VBA by using the function
EvaluateName. The strange thing is that all the names work perfectly
well in the worksheet, but not from VBA, so I guess I'll go with the
hidden worksheet option in the future. I like being able to work
easily with names and was really glad when I found a name manager add-
in, but working with them in a sheet is even easier.

Yet again, thank's for all the help.
Regards, Anders