How does Excel calculate named formulas and materialized named ranges?
I have been creating utility formulas with AFE to make my lambdas more readable and maintainable, for example: ManualAllocRng =OFFSET(ManualAllocCorner,1,0,URows,EntityCount); How does Excel calculate these results? Are they calculated every time I use them, or is there an internal type of materialization that happens once and then is re-referenced on each usage? In particular, I am wondering: (1) If I have a lambda that references my ManualAllocRng twice, does it matter if I write it as thing = LAMBDA(x,LET( rng, ManualAllocRng, a, something(rng), b, otherthing(rng), res, combine(a,b,x), res )); or as thing = LAMBDA(x,LET( a, something(ManualAllocRng), b, otherthing(ManualAllocRng), res, combine(a,b,x), res )); Does version 2 calculate the result twice? Does version 1? (2) Should I instead be materializing the value in a Calcs sheet, naming the corner ManualAllocResMaterialized, and using ManualAllocResMaterialized# throughout the workbook instead of referencing ManualAllocRes? Does every reference to the named formula calculate its result again? Does every reference to a spill recalculate the result, or does it "look" at the spilled range that was already calculated? On a related note, I have found a circumstance where sheet-based spilled result =Ledger.FilterMatch produces a different result from VBA Dim B as Variant B = Evaluate("=Ledger.FilterMatch") This seems like a serious bug. So I am considering materializing all my AFE-based named formulas onto a Calcs sheet just to avoid this bug, even if there is no performance issue to consider in the questions I asked earlier.
Touch screen does not work for graphic in top left
https://1drv.ms/x/s!AnFi6uGE1rekjWGpZ8gIIOp89wsQ?e=2R6PaT in the workbook i have a blue graphic button with a simple macros. select a cell. then click the button and "1" goes into the active cell. but if i do touch screen, the touchscreen option for the button does not work when the button is in the highlighted yellow area (highlighted for reference). anyone know how to resolve for touchscreen?
Excel Security Warning:
Hello Everyone, I am having one strange issue. I am working on a excel which has few Power Queries and around 10 sheets. I have not created any macros. But in VBA Editor there are two VBA Projects. But they do not have any macros in it. Now the issue is, every time I open this excel file, I have been greeted with the "Security Warning: External Data connections have been disabled". and I need to press the "Enable Content" button every time. I have tried saving the file as .xlxs (from .xlam) but it didn't solve the issue completely, it does not throw error in some machines still and I have also tried to remove these two projects which has but no luck. Can someone let me know if this is a known issue? What are possible ways I can remove this warning (only for this file) as technically I am not using any macros. Any help would be highly appreciated. Let me know if you need more details. Thanking you.Add VBA Code to an Option button dynamically
Hello Excelers, I hope you are doing well. I am back for some more help and here is what I am trying to accomplish: I have some code that would add x Option Buttons to a User form. x is Unknown but usually less than 15 but maybe more at times. I need to assign a macro that would return the newly created Option Button's Caption. In a Module I have: Dim objOptBtn as MSForms.OptionButton In a For i = 1 to x loop I have: Set objOptBtn = uFrmChoice.controls.add("Forms.OptionButton.1","objOptBtn" & i,true) With objOptBtn .Caption = i .snglTextWidth = Len(objOptBtn.caption) .width = snglTextWidth + 18 .Left = 10 .Top = TopLevel + 10 .Width = 400 .Height = 18 End With I remember there was an .OnAction "MacroNameYouWantToRunWhenOptionButtonIsClicked" But I do not see it in the Intelisense???? How can I assign code to run once the user clicks on a Option Button on the UserForm and return the Caption of that Option button? I am trying to ask the user to choose ONE option from a set of unknown options.
Search for a date in a row does not work in VBA with ".find" when column is too narrow
I have a row with dates (row 3) and a cell with a date (C7) I want to get the column where this date (C7) is in that row3 This is my code: Sub find_date() Dim datum As Date Dim zoekrij As String Dim cel As Range datum = Range("C7").Value zoekrij = "A3:Z3" Set cel = Range(zoekrij).Find(DateValue(datum), LookIn:=xlValues, LookAt:=xlWhole) If Not cel Is Nothing Then MsgBox ("Date found in column: " & cel.Column) Else MsgBox ("Date not found") End If End Sub This works when the row 3 is formatted as date and the column is wide enough, but when the column is too narrow and shows "####" the date is not found. I also tried to use Clng(datum) and formatted the row as numeric, but with the same result (works only when column is wide enough)
Power query refresh using script
I am trying to automate the power query refresh using power automate. Change in a folder triggers multiple scripts. All the scripts seem to be working fine, but the final script to refresh the connections, to show updated query results in the various tabs is not working. function main(workbook: ExcelScript.Workbook) { // Refresh all data connections workbook.refreshAllDataConnections(); } what might be different about this? thanks!Power query refresh using script
I am trying to automate a power query refresh using power automate. The flow seems to be working fine, but the excel in itself isn't refreshing the output tables. The script I am using is this function main(workbook: ExcelScript.Workbook) { // Refresh all data connections workbook.refreshAllDataConnections(); } I can see the updated query in the query editor, but not in the output tabs within the excel.
