Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
Are you using something similar to that below to refer to your named
ranges? Dim rngTotalNumberOfRowsData as Range Set rngTotalNumberOfRowsData = ActiveWorkbook.Names("TotalNumberOfRowsData").Refe rsToRange 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 (?). -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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 -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
Application.Evaluate("TotalNumberOfRowsData")
It still does not explain why I couldn't use evaluate in the first place try - Application.Evaluate(ActiveWorkbook.names("TotalNu mberOfRowsData").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 wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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. -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems evaluating defined names in VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defined names | Excel Worksheet Functions | |||
Defined names | Excel Worksheet Functions | |||
Evaluating names range from two different workbooks. | Excel Programming | |||
NAMES DEFINED | Excel Discussion (Misc queries) | |||
Defined Names | Excel Programming |