Hi apo,
. I think I have “earned” the privilege” now to use your last code. I think I have some good understanding of it
. Just some feedback, maybe some question but only if and when you have the time. I have rambled a bit.. I have already learnt and benefited a lot from your given code, and the comments were a great help.. I still needed to go through it carefully in detail, but the ‘green comments help getting a good start, and help greatly in knowing in which direction to think once I know your way of thinking in the code. (And additionally, as you noticed yourself, sometimes it helps reflect if any step is necessary or could be done in another way..etc…)
. 1 )
Originally Posted by
apo
……It is my understanding that the z variable is just used to add the key to the Dictionary if it doesn't exist already..
Check out SNB's website for lots of good stuff on Dictionaries and arrays..
….
. I have visited that snb site quite a lot when trying to understand the Dictionary Stuff. It is a very good site. The way things are explained suits my way of thinking often.. As with other problems, though, I find I often have to read that site , then solve the problem myself, then go back and read again and see that the info is there and confirms my findings. So it has helped me a bit to get that “empty” z variable mystery solved in my mind. I believe he may have his explanation slightly in error.. Actually the idea with
z = objDictionary.Item("AKeyX")
.. I think, is that in this method the main feature is to assign the item with key “AKeyX” to the variable. A second feature, as you use in the code is that if the “AkeyX” does not exist it will be made. A very convenient feature to simplify a code only wishing to use the Dictionary in it’s “by – product “ characteristic of only allowing unique keys..
. Where I differ a bit** with snb is
.. a) My experiments suggest that in the case of the key not existing I am returned a “ value “ ., that being an “empty” as I must dimension Z as a variant, or the code crashes giving error of constant type inconsistency.. ** snb is inconsistent and in fact does agree with me later!
. b) I think snb has listed this as 1 of 4 methods to add an item to the dictionary.. well I suppose it sort of does only if my .a) is correct..but then only in the case of the key not existing, otherwise it gives the variable z the item that is there ., so is not “adding” an item……
…. I do accept I am splitting hairs a bit.. you could interpret differently what snb says.
. I think perhaps that snb page needs to emphasize a few points, this is not the first time an important thing I found by experimenting was actually there, sort of , but not immediately obvious or too clear.... but the info is mostly there. …. snb I find an interesting character. He is probably one of the few that could answer that mystery for certain. – if he felt inclined: He often pops in a Thread like you, unexpectedly, with a nice new code alternative. The difference in my experience is that his do not work, at least he does not apply them to the particular problem. Getting them working can be a healthy learning experience, but you need a lot of time. I think you describe him in the Computer trade as a ”Troller” or similar. But I may be in error, certainly no offence intended to him, – I am an old Practical Physicists, no working experience with Computing, and did not spend much time with many of our Programmers back then.. I have learnt a lot from working though some of snb’s codes, but it took the ( long ) time I no longer have!!! - ‘green Comments like yours ( and the code actually working !! ) is a great advantage… !)
(, BTW of course my conclusion ties up with your “ … the z variable is just used to add the key to the Dictionary if it doesn't exist already …. “ )
. 2 )
.
Originally Posted by
apo
……..The method I went for was basically an alternative to auto filter.. and kept it all within arrays which theoretically should be faster (especially with large data sets)…….
… yeh, I should have got that point immediately. – It is a strange world.. when I woke up last wear after my 25 year computer coma , I started looking at Array math’s based on my Mathcad and minimum basic programming memory from back then… Then somehow went over to the Excel VBA Worksheet World, using and therefore interacting with all the “nice” pretty Range .Methods .Properties etc. .. Then recently based on my experience, your recommendations and others, I have gone over to capturing everything in one go into arrays and doing most workings there. Clearly interacting with the Spreadsheet is “unhealthy” in many ways . Speed… of code… and the brain slows down..… your neat idea of getting the grid co-ordinates for the rows for each sheet – just the sort of thing that back in those days for me after a Coffee what I would have come up with.. but I have been infected by the .OOP .Do-It-All-For-me mentality… which at the end of the day does something like your codes do, but just more inefficiently by wasting code making it so All-In-One user friendly!!
. ( I “sliced out” the “column 1” of Array xx() and transposed that rather than transposing that Column(1) from the spreadsheet… Not sure if that is helping again to get away from sheet interaction…. It is not clear to me if by using Application.WorksheetFunction I interact with the spreadsheet or just use some Mathematical function??. Any idea on that??
. 3 )
…. y() Array !!!!! ( Your y Array )
. Clearly you really need to know how VBA sequentially does things, for example to know how to know how your y() array will come out. Index appears to be doing here something different from how I know it until now.. –Here it is Looking for the entire row indices ( Which are in an Array ) one after the other , for each column , - the column given by the second argument indices in that second argument Array of indices. Then it goes on and does the same for the next column. ( I see, you assume “the maximum” and so this is repeated as many times as if the LookUpHeading ( Service area ) was one value) ( the second ( Column array) argument which you have organized to contain long numbers 2,3,4,5 etc. ). I can follow it clearly with putting a watch on the arrays and seeing how they are filled… BUT
. 3 a) is it written anywhere how the Index behaves in such a situation??? Or is it just experience / instinct on your behalf??
. 3 b) - Very minor point: I follow why just before you output your y Array to each sheet you resize to chop off the “Error” rows ( corresponding to columns in the transposed for output.. ) . However, this would I think not be unnecessary if you changes this..
to this
. In this case the y() Array only “extends” as far as necessary so we have no error rows in it…
And then we can replace the output line
with..
… appears to work, but I may have missed a point.
……………………………………………………..
. I include my full worked through or rather “raped “ version of your code .. split into two in the next 2 Posts #13 and #14. It is not a pretty sight. Sorry about that. But miraculously it still seems to work despite my best efforts…
(. The bit to delete sheets at start I do partly to help me when I debug/develop the code so I don’t have to keep deleting manually all sheets if I re-run the code after any modifications. ( OPs if they are using the code in the practice, often say they may have new data to add on the end of rows in existing.. I suggest as an alternative store the current file as an .xlsx or ,xls File as a back up, then reopen the .xlsm file, add the new stuff in sheet 1 ( data ) and then run the code again..) ..
Thanks again, especially for the ’green comments, they’re are very useful, my opinion
Alan
P.s. This shows my computing ignorance: I have no idea how you get the Private Sub CommandButton1_Click() thing up and running in VBA so I just have it a normal Sub. I did do a VB beginners course for fun last year, sounds something along these lines I guess, UserForms and the like.
LinkBack URL
About LinkBacks
Register To Reply
Probably explains one of the OPs points in Post #4 that I did not understand last night :. …
Bookmarks