User Profile
ecovonrein
Iron Contributor
Joined Dec 01, 2021
User Widgets
Recent Discussions
If you thought Spills killed CSE, read this post
I build financial models. All financial models I have ever seen do the same thing - they show one reporting date per column. Every next reporting period evolves from the previous financial close. That is, the calculation order in such models is strictly A1 to An, B1 to Bn, ... N1 to Nn. There may at times arise the need to perform complex calculations that produce more than one result. Today, we can (program those in Lambdas and) spill the outputs across multiple rows (within the same column). Alas, Excel's performance resizing Spills is absolutely atrocious. Deploy such a formula across the columns of your model and you can literally watch the pixels set. One column at a time. Things settle after the initial run. There is then no need to resize ever again. Just so long as the calculation works out alright. Unfortunately, stuff happenz. And when that calculation errors out, you are back to watching pixels set as Excel replaces your multiple result rows with one row of #VALUE!. Once you fix that error, well, then you are back in for another treat of umpteen resize passes... Such a spreadsheet is frankly unusable. Now, I noticed with some astonishment that there is a simple fix for this nonsense: Just enter your spill formula with Control-Shift-Enter to morph it into an array formula. Sure, we all hate those. But all the hassle around editing is a price worth paying for Excel suddenly attaining Warp speed... Post Scriptum: There is another "solution" to this problem. Instead of performing the calculation once to spill 5 results, perform the same calculation 5 times over and return 1 result each time. If you think this is awful, you are of course correct. But ... to my amazement, doing this easily beats the Spill (no surprise - even using an Abacus probably will) and there was no telling a difference to CSE. I was baffled. Then I realized what Excel got up to. Excel understands that these 5 calculations are independent of one another and will happily parallelize them across multiple CPU cores. So, while this insane solution hammers the CPU, the nature of a financial model is such that it mostly cannot exercise more than one core anyway (it cannot be multi-threaded), such that on the occasion that we are being stupid, there are plenty of cores idling around that can bail us out... :) Post Post Scriptum: If for some reason you do not wish to use CSE, make absolutely sure that when stuff happenz your Spill returns 5 bad results. That avoids Excel having to resize that range when an error occurs.203Views0likes4CommentsDegenerate MAP blows up Excel
Does this formula work for you? =MAP(0,1,LAMBDA(x,y,x+y)) Which Build are you on? I am stuck on 2406 because all Builds since blow up when running my spreadsheets - which, as I type this, JUST MIGHT be down to this nonsense. Not only does this innocent instruction return #VALUE!, it also appears to corrupt Excel beyond recovery. Which is a problem because the MAP(LAMBDA( phrase is a wrap one needs to deploy when a Lambda(a,b... might have to handle vectors for a and b to return a vector of results for a computation that will not support such vector inputs natively. The cure for this nonsense on my Build appears to be =MAP(HSTACK(0),HSTACK(1),LAMBDA(x,y,x+y)) Unfortunately, it is hard to remember this. I might have to scour my Lambdas. The problem only appears to concern MAPs with multiple vectors. =MAP(1,LAMBDA(x,x)) is just fine.134Views0likes1CommentRe: SWITCH and IFS - a word to the wise
HansVogelaarWould upLike your post but that doesn't seem to work today. Your link is a better write-up than mine 🙂 It is outrageous, really, because if Microsoft wanted to be lazy, they would only need to teach the precompiler to translate SWITCH and IFS into IFs in the first pass (a doddle for the precompiler which does not mind counting brackets) and then get the expected functionality for free in the second.499Views0likes0CommentsSWITCH and IFS - a word to the wise
Last night my Excel very nearly died executing something that looked perfectly reasonable: myLambda = LAMBDA(i, SWITCH(i, 1, ..., 15, LET( ..., l, myLambda(1), ...)) There is a recursion but it wasn't supposed to end in disaster because branch 15 would not be invoked second time around. Turns out, it nearly ended in disaster because Excel apparently evaluates ALL the branches of the SWITCH before making a decision. This was totally unexpected and there isn't a word of warning in Microsoft's online documentation. It is also totally contrary to intuition. SWITCH will only ever be deployed in a scenario where there are . a large number of branches which, even if each branch were easy to evaluate, would in their multitude still and always impose a significant computational overhead; and . no more than one condition ever applies (such that n-1 evaluations are assuredly a wasted effort - or worse, as in my example). My next port of refuge was IFS. There is an argument anyway that SWITCH is redundant in the presence of IFS. IFS offers greater flexibility and the overhead of typing out IFS over SWITCH does not really move the needle. Alas, same thing again !! These new functions are incredibly poorly implemented. With regret, I must return to nested IFs. IF only evaluates the applicable branch. Only problem is keeping track of the number of closing brackets....816Views0likes3CommentsRe: Mail merge
mathetesHa ha. I doubt it. I nodded along when you wrote "the really complex stuff was well over 20 years ago". I think mail merge has gone out of fashion. I have not come across anyone using it in this millennium. It is what oldtimers like us still occasionally do 🙂 Probably also the reason it no longer works. Not a focus for MS. Best.507Views0likes1CommentRe: Mail merge
mathetesThe short answer is "No". A simple flat file does nothing for me when our production environment is as heavy as it is. SPO stands for SharePoint Online. If you have to wait 15 mins for something to happen, you'd probably complain too. I was about to throw the laptop at the wall. My personal highlight of that day was ChatGBT helpfully suggesting that I might be better off coding the whole merge in VBA. Don't say that little bot does not have a sense of humour. Anyway, the largest part to the answer I meantime discovered. It turns out that Word *always* latches on to the the most recent Excel session, and NOT the session with the target database. So ... when that most recent Excel session does not contain that database, Word will launch it again. Which is disaster for two reasons: 1 You won't get the data in the active session, obviously. 2 If the most recent session is heavy - and that it was in my case - the act of loading the read-only copy from disk and calculating it ends in a fiasco. Now, 15 mins easily exceeds the time it should take to calculate even the heaviest of my Excels, but Lord knows what other nonsense that process does. I would not be surprised if it ran single-threaded. Just for fun. Anyway, the bottom line is that I can get the link-up time down to 2 mins if I make sure that I open the database in a new Excel session. Those 2 mins are still worse than what it was 5 years ago, but hey - that is the price of progress I suppose. And I could rant about SPO all day long 😉 PS: I also observe that the obscure OLEDB *always* opens a read-only copy of the Excel from disk, so it will never get the latest values from a recalculated template. So the default DDE is the only way for us to go.467Views0likes3CommentsMail merge
Does anyone here have experience with merging Excel data into Word documents? I had one of those days with Office, I would sooner forget. I have an XLM file - not too heavy, but with OnCalculate VBA - which used to supply data into Word without any problems when we were still using Dropbox and pre-365 environment. Since moving to O365 and SPO, the Word will happily take 15 minutes over opening the document. There is some weird trick by which I can force Word to give up on DDE and switch to something called OLEDB. It brings the time down to 1 mins - still pretty rubbish. I talked to my new friend ChatGBT about it and I now know that I am in trouble because, after initially coming up with many helpful suggestions, it in the end referred me to Microsoft Support ... 😞 One of its suggestions was to rebuild the Word doc from scratch. That wasn't a big deal because the doc in question is a one-pager. A bit of CTRL-C/CTRL-V got the job done in no time. It also suggested that I move the Excel off SPO. I saved a copy into Downloads before making a new connection from the new doc. But here is the weirdest thing: when the OLEDB connections eventually resolved - we are talking here no less than 5 mins - the fields in Word did not contain the values from the open Excel. The values that displayed in the Recipient list were values from the file on SPO. Not that Word would actually populate the preview mailings. Does this ring any bells with anyone? Thanks.959Views0likes6CommentsRe: Hardcore users only - help needed to clean up a Workbook
A further update re the work-around. The reason it works is the same reason I am now in a different pickle. The codebase of Excel must be a complete mess after 40+ years of maintenance. It appears that external references in the grid are handled differently from those in .Names. I noticed this because our files are all in SPO. And the issue I had for the longest time with this environment is that when I am on the road and without access to SPO, the environment is too dumb to seamlessly switch to (the copies on) OD. So, we run some VBA code at start-up that automatically aligns the external references with the source of the Workbook. If the latter is opened from SPO, fine, all links are pointed to SPO; when the latter is opened from OD, all links are pointed to OD. This solved the travelling problem just until this work-around. It transpires that Auto_Open does not run before Excel attempts to resolve external grid references with the result that Excel simply hangs itself in the absence of SPO when one of those references points to SPO... 😞 And while on that subject, though somewhat off-topic, the SPO handling by Excel is total rubbish too. When Workbook A links to Workbook B and I save Workbook B to a new filename on OD, then EVERYTHING in Workbook A will look like it now points to the new Workbook B on OD, as it should. However, Excel has surreptitiously ignored my instruction to reference OD and instead inserts references to SPO. You know now how I noticed... 😞547Views0likes2CommentsRe: Hardcore users only - help needed to clean up a Workbook
NikolinoDEWell, I would certainly be curious to learn Excel 2016's response. You do not say. When you run the unhide macro, do all links vanish from your Excel too? My suspicion was that the problem is somehow related to my rich use of Lambdas in M365 - something 2016 never heard of. Then again, I went out of my way to clear any trace of my previous use of LET or LAMBDA... PS: Your macro to create a hidden Worksheet in which to insert grid references to foreign Workbooks occurred to me too. The algorithm would need to be somewhat more complex than you show, and I rejected it for that reason in favour of inserting a few manual links. Thanks. PPS: As I wrote before, I have the exact opposite problem from the references you share. I have real links - no "ghosts" - which Excel loses track of...583Views0likes0CommentsRe: Hardcore users only - help needed to clean up a Workbook
I found a work-around. My Workbooks generally make no grid references to foreign Workbooks. All such references are handled thru the Name Manager via defined Names (just like in the example attached). However, it turns out that explicit references to those foreign Workbooks from within the grid survive the bug discussed here. So I must insert into my spreadsheet somewhere a single explicit reference like "='[foreignWorkbook]someSheet'!A1" FOR EVERY foreign Workbook referenced to preserve thisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks). Stupid but manageable.664Views0likes3CommentsHardcore users only - help needed to clean up a Workbook
I attach a file that drives me mad. I just reported the same to Microsoft. The file is a totally pared back production file. It is substantially empty - I deleted practically all. (The single Worksheet is brand new.) When you pull it up (Enable Content), see Data/Workbook links - you should see two. Now press ALT-F8 and run the only macro in the Workbook ("unhide..."). Check Data/Workbook links again - the links will have disappeared. Look at the trivial macro (ALT-F11) - it does nothing but to iterate over all defined Names. If you want, rebuild the environment in a totally new Workbook. Copy the macro. No problem - the links survive. Sadly, the production sheet is so big that rebuilding it from scratch is not an appealing prospect... 😞 Thanks for your help. PS: The external references are created by 4 defined Names. And you will easily see (by inspecting the Name Manager) that these references remain, even as Excel believes they have vanished. Update #1: The attachment is larger than one would expect (from an empty Workbook). This is owed to production data being cached by the links. I just repointed those links to an empty target. It does reduce the file size to 30kB. The problem remains the same.1KViews0likes8CommentsRe: Hardcore users only - help needed to clean up a Workbook
NikolinoDEThanks for engaging with this issue. I do not believe that STEP -1 necessarily makes your code any more robust than STEP +1. It all depends on the implementation of the underlying Collection. To be truly safe, you would need to take a copy of the Collection. But your code and mine differ in that your deletions of Names actually change the underlying Collection; my mere change of an attribute of a Name does not. My original code used FOR EACH when it ran up against this problem. I was merely clutching at straws when I changed it to FOR i. To no avail. But your post has me confused. Were you able to replicate my issue on your system? If so, why would my environment (Win 10, M365, Build 2406) be of interest? More importantly, did your code solve the issue in your environment? For it sadly does not do so here. Incidentally, your code does not so much chase phantom links as it attempts to work around the strange situation where in this particular Workbook thisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) suddenly collapses to 0 after some macro loops over all Names. That is, the link your code finds is no "phantom"; it is very real. (And I have no interest in deleting it - I need it.) My issue is that Excel loses track of it.664Views0likes2CommentsRe: Linking Active Workbook to Closed Workbook
JKPieterse I agree on the range and had indeed understood that that is how he works. But I think you find that you are wrong. Do not open the foreign Workbook with that range and the referencing Workbook sees #REF!. Which is why I open all linked Workbooks from Auto_Open. Jn12345 You seem to be struggling with succession handling which is never easy. Also a big minefield in TEAMS. A natural Person must be assigned a Role. That Role can then be assigned to Tasks. That way, when the Person leaves the company, a new Person can be assigned to the Role and all Tasks should automatically then show the new Person.709Views1like0CommentsRe: Linking Active Workbook to Closed Workbook
This sounds vaguely familiar. I think that the Worksheet/Workbook concept of Excel is a design accident going back 40+ years. Fundamentally, a Worksheet isn't really different from a Workbook, ie you should not have any hang-ups about disaggregating your Workbook of 8 Worksheets into 8 linked Workbooks (or any combination in between). In practice, Microsoft makes your life hell when you go down that route. One aspect of that hell is that you are fretting about a Workbook - containing your master list - being open. And you fret about this because, out of the box, Excel does not open linked Workbooks. If you want to get to a place where 1x Workbook / 8x Worksheets becomes functionally equivalent to 8x Workbook / 1x Worksheet, you want to create yourself an Auto_Open macro that runs thru all linked Workbooks and opens them. To that end, your template Workbook must be an XLSM. When you get there - ChatGBT will be happy to help - neither you nor your users will care any longer that their Excel project consists of umpteen Workbooks. All dependencies will just pop up. (I must confess that I do not know what "pops up" in the case of a Workbook containing only one hidden Worksheet. Conceivably nothing.) At that point, you will run up against a different problem. Unless you took the greatest care, each user's Dropbox will be in a different Windows file path from every other user. Excel links unfortunately do not recognize this concept at all. You may find - like I did - that you have to patch up file paths as you implement the exercise above.802Views0likes4CommentsRe: SPILLs of external data - word of warning
The OP merits a follow-up. During the summer of 2024, an idea came to me how to fix the breaking spills problem in VBA. I then faced the question whether to continue with the work-around described in the OP or to rephrase my code. I want to share here why that was even a question. Let's start by recalling the pros and cons of spilling into a fixed range: Pros: Everyone knows the conventional fixed range addressing scheme used to access the spill. Unfamiliar readers may be mystified how the fixed range becomes populated out of a single formula in the TLC, but that incomprehension will not prevent them from following the downstream code. It is a no-brainer to replace the spill (of external data) with user data within that fixed span. Just type away... Excel provides STRONG auditing for the downstream code. Press F2 and you will immediately see the slices of data (rows of horizontal spills or columns of vertical spills) extracted from the spill. Any referencing error becomes immediately obvious and you can use your mouse to correct it. If you place a "bookend" token to the right of the TRC (of a horizontal spill), your code is guaranteed to fail with a #SPILL! error when the spill attempts to break out of the range. In this way, you can reliably protect the conventional references from accidentally only capturing parts of a spill. Cons: Your code will error out when the spill hits the bookend. How bad that is depends on the complexity of your downstream code. Our production models are very complex and it transpired that while the #SPILL! error resulted as designed, it quickly morphed into an amorphous #VALUE! in further downstream processing such that the original #SPILL! becomes very hard to trace - in particular by inexperienced Excel users. We also experienced many more breaks in production than I had anticipated; my receiver ranges had been too short. Interestingly, while increasing the size of those receiver ranges reduced the incidence of these errors, the decreasing incidence also reduced familiarity among users with those errors when periodically they did arise and how to resolve them. The downstream code consuming these fixed-size slices of variable length spill data must be programmed to be tolerant of blank inputs. The processing of blank inputs will reduce the computational efficiency of your model. At which point the question pops up "What's the alternative?" And one possible reason for staying with the work-around above is that you might reject them all... How to slice a spill? At the heart of this question is the impossibility in Excel to return a range of Spills. It is simply not possible to split a 6xN spill into 6 spills of 1xN each. It is hence not possible to easily reference the 2nd row of a spill out for D400 as D401#. Put that question to ChatGBT and it will tell you to use INDEX(s#,r,0) to access row r in spill s. This is true enough, so let's review the pros and cons of this approach: Pros: It is computationally efficient. However much INDEX will seem like a function, it isn't. Excel's precompiler will resolve it before the actual computations start. The downstream code will only receive good data (ie does not need to deal with blanks). We should never expect any #SPILL! errors. Cons: INDEX is tedious to write and in its bulk distracts the reader of a downstream formula from the true purpose of that formula. Humans simply aren't good at counting. The row indexing is ok when you want the 2nd row but when you want the 10th - are you quite sure that row isn't #9 or #11? This may be reasonably acceptable to the programmer but to any reader of your code, this is a nightmare. Closely tied into the previous point, Excel's auditing is powerless. All you gonna see is an outline of the spill (which INDEX references) whole. I did not like this answer one little bit and set about writing a Lambda to ease my existence. I was very pleased with my final version, which read =WithRows(s#, RowLabels, LAMBDA(r, FILTER(r("Cash"), r("Dates")< ... All my spill rows are prefixed by user-friendly labels such that RowLabels was simply the column immediately in front of the spill data. Pros: Intuitively readable. Practically eliminates any mis-referencing. While the coding is "wordy", the gumph is all in the preamble, ie the mechanics of addressing the spill do not distract from the algorithm. The downstream code will only receive good data (ie does not need to deal with blanks). We should never expect any #SPILL! errors Cons: Not strictly native code. While it is intuitive to me, others may not follow. Less efficient than INDEX - the MATCHing of the string input is computationally more intensive than the numeric addressing. The coding is "wordy". While reference errors are virtually impossible, there is no auditing thru F2 either - like with INDEX, you only see s#. Requires a map for each spill. I discarded the ideas of INDEX and WithRows when I recalled the arcane Excel intersection operator: It is possible to address D6 by writing "=D:D 6:6." D:D addresses the entire column D, 6:6 the entire row 6. The space in between is the "intersection operator" - Excel will only address those cells which are spanned by the range before it and after. In this instance, that is only D6. In 30 years of programming Excel, I never once had the occasion to use it. I have now embraced it to slice my spills. The reference =$D400# 401:401 will return the 2nd row from the range spilling out of D400. Pros: Totally native reference which gets resolved at the precompilation stage and imposes zero overheads on the actual computation. F2 provides perfectly good auditing. Sure, 401:401 is oversized (relative to D400#) but you see the intersection. If you made a reference error, this is now as obvious as with the fixed range. And you can use your mouse to fix it. It is reasonably concise. For example, =$D400# 401:401 + $D400# 403:403 is a fairly succinct way to sum the 2nd and 4th row of the spill in D400. The downstream code will only receive good data (ie does not need to deal with blanks). We should never expect any #SPILL! errors. It requires no RowLabels. Cons: It may freak out the average reader who hasn't a clue what's going on. If I managed to sustain your attention until this point, then I shall reward you with a further thought. You may recall bullet #2 from the Pros of the work-around: How can we construct a spill from manual inputs such that it becomes possible to feed a downstream calculation that uses spill notation? I wrote a VBA macro to perform this procedure in the spill area itself: Write your input data somewhere, say into B100:AZ105. Put a reference to that fixed range into the TLC of your spill area, ie =B100:AZ105. (The data will spill, obviously.) Now press F2 followed by F9, then Enter. You can now delete B100:AZ105 😉 Happy coding.235Views0likes0CommentsRe: Weird behaviour when replacing a cached Lambda
For the record, after hours of debugging it transpires that little of what I worried about in the OP had anything to do with the problem. Well - besides perhaps somehow triggering a bug in Excel. We will never know. Anyway, the killer was an innocuous assignment inside LET like ..., c, Sheet!$S800. That is, Some_Work should refer to column S in which-ever row it is called from. (Some_Work exists in the scope of a Worksheet to provide local context to the Lambdas Big_Work and Little_Work in the scope of the Workbook.) It failed. Somehow. (I have encountered similar issues in the past with fully relative Names like "=!S5".) The resolution to the problem was my coding LET( ..., colS, $S:$S, c, @colS, ...).340Views0likes0Comments
Recent Blog Articles
No content to show