User Profile
WHBurling
Copper Contributor
Joined Aug 20, 2022
User Widgets
Recent Discussions
vba - choosing Workbook object vs Range object in accessing named values
I need help in understanding why one might use a workbook object vs a range object in the accessing of a named value in a workbook An example of the workbook object approach is: Dim DataPtr as integer DataPtr = ThisWorkbook.Names("DoorWidth_Pointer").RefersToRange.Value My understanding of the above line of code is: ThisWorkbook is the object from which data or a method is to be found. Names is an object of ThisWorkbook. Names has a property that holds a list of sets of data. A set of data in that list consists of names cells to which each name points. Names has methods to access the above data. “DoorWidth_Point” is a text string which will be matched by a method in the Names object RefersToRange is the phrase that tells the method of the Names object to go to the address of the cell or cells for which the text string is associated with value tells the Names method to extract the value(s) of the cells identified above To me the value of using the workbook object is that it is ultra clear. One reading the code knows exactly which workbook contains the named variable. It knows the name of the named variable. It states to go to a specific address and retrieve a value. an example of the range object approach is: Dim DataPtr as integer DataPtr = Range("DoorWidth_Pointer").Value My understanding of the above is: The reader of the code is forced to identify the implied active workbook. Implied is that the Range object knows about named ranges for the active workbook. It appears that given the text string, the “range” object accesses the active workbook’s object Names and hides the method that extracts the cell address and just outputs the value in the cells To me the value of using the range object is it’s simplicity. I have little experience, perhaps knowing which is the active workbook is not a big deal. I do not know of another reason for using the Range object vs the Workbook object. On a separate issue While I can get the workbook approach to work with a named range, I can not get it to work With a named cell. I have tried the following without success: Dim DataPtr as integer DataPtr = ThisWorkbook.Names("DoorWidth_Pointer").RefersTo.Value I used RefersTo as it was my understanding that RefersToRange is used with ranges and RefersTo is used wWith single cells. Perhaps I have misunderstood RefersTo usage.829Views0likes1Commentvlookup is selecting the correct range and row but does not return correct value
i am using named variables and ranges in my workbook. I want to use them in vlookup. if i do not use named parameters, Vlookup works. If i use named parameters, the function does not return the correct value. troubleshooting the case in which i use named parameters, i clicked on the cell with the vlookup function followed by clicking on the formula task bar. it reveals a range that correctly includes the data portion only, but not the first column which is used to find a match. However, the correct row in the range IS selected. That means excel actually sees the first invisible column AND correctly matches the first parameter in Vlookup with the unique value in the first column. What appears to be failing is the selection process which uses the third Vlookup parameter. No matter what I put into Vlookup for that third parameter, I can't seem to get Vlookup to correctly respond. to experience what I am going through, see the attached workbook. select the worksheet named "Dimensions - Windows and doors" the green highlight reveals my use of named variables and ranges with the excel Index function. it works. The cells highlighted in purple are the cells in which i am attempting to use vlookup. it might look like it is working, but try changing the matching variable. Ie: Door15 or Door01 and you will see nothing happens. The cells highlighted in red use vlookup with no named variables or ranges. it works. in addition there are a few other odd behaviors the non working vlookup returns a duplicate value as you can see the value to the right of the cell in which vlookup is located. sometimes it seems the vlookup works with named variables as i experiment with the use of @ to change data type from value to referenced data. Then the formula appears to not respond to a change. i do have automatic formula update turned on. Any help that can be provided will be appreciated. Alternative approaches are welcomed, but i need to understand what i am doing wrong with named variables and this case is an opportunity to increase my understanding. By the way, I can not post my excel workbook. your program highlights the work book and issues a failure message. the workbook is stored in OneDrive !!! In addition, I tried saving a copy to my desktop and it too becomes highlighted and this posting will not take place. Also....a message is issued that suggests i save my message on this forum. How? I don't see a "save" button anywhere. my excel workbook is automatically saved. Hence i am posting this plea for help without the workbook.2.8KViews0likes3CommentsRe: #spill error may be linked to named references and formatting
Thank you Detlef! I tried dragging my file into the full text editor. The editor refused to accept the file type of XLSX. I did copy the cells from the worksheet but the copied result does not allow you to see the formulas so YOU guys can test out the formulas. It appears to be just a static snapshot of my worksheet range of interest1.6KViews0likes0CommentsRe: #spill error may be linked to named references and formatting
Thank you for responding. I don't think you are understanding my dellima The calculations work with R1C1 references. The calculations do NOT work with Named References. The error, #spill!, occurs only when Named References are used when at least one of the cell values is referenced by names. The error seems unpredictable. The article you suggested I read does not appear to even hint of the above condition as being a cause of the error. The subtypes of #spill! error ARE indicated. 1) "A cell we need to spill data into is not blank."...which is not my case. 2) "We can't spill into a merged cell". That IS the case but only if I use named references. for the exact same merged cells, i don't get the error, "we can't spill into a merged cell" if i use R1C1 referencing. That is why I posted the question. This issue does not appear to be addressed anywhere. It would be nice ifi this forum allowed one at attach a worksheet or segment of a worksheet.1.6KViews0likes3Comments#spill error may be linked to named references and formatting
Excel is issuing #spill! Error messages in one of my worksheets. Most of the articles I read suggested that this error often originates From the use of cell merges, which I use extensively. My merged cells Are all merged vertically. Using “center selected cells” as an alternative To merging does not work for me as that formatting feature only works for horizontal ranges, not vertical ranges. In my case, the error does not appear to be related to merged cells. In experimenting with this issue, I made two very different discoveries. If I use R1C1 references, I do not ever get a #spill! Error. If I use named References, sometimes I get an error and other times not(unpredictably). I would very much like to use named references. This suggests to me that the error has something to do with the way named references refer to the cell value. Indeed if I evaluate a formula which references to sets of merged cells, I can see a difference. If I use R1C1 references, just before I execute, I can see integers for both numerator and denominator and the division executes correctly. But if I use named references, instead of an integer I get the integer followed by zeros. Ie: integer;0;0;0. Executing with that format induces the #spill! Error. If I copy the range of cells that are giving me the error into a new worksheet In a new workbook, the copied range of cells does not generate an error even when all references are named references. Since the values in the selected range are not dependent on any other values In the entire worksheet or workbook, I expected the calculations to be executed In exactly the same way, otherwise excel would not scale. This appears to not be The case. Formatting, which can create a dependency across a range of cells appears To impact how a cell value is processed. In the old workbook I must have a worksheet wide format set differently than the format in the new workbook. Any help with either of the above issues will be appreciated. bil1.9KViews0likes5Comments
Recent Blog Articles
No content to show