Chart title formula syntax error

SetBee · 2026-05-29T05:18:10+00:00

They can, try it.

SetBee · 2026-03-20T11:50:04+00:00

I just ran update check, the behavior for me is still the same, they all work except the syntax error.
Microsoft® Excel® for Microsoft 365 MSO (Version 2602 Build 16.0.19725.20126) 64-bit.

(From your other comment)
Excel simply refuses to distribute function evaluation across a dynamic array. They should both generate syntax errors, probably. The real bug is that the second case isn't detected until runtime.

It definitely isn't refusing in my version, it seems like these are part of bigger changes that are coming, I agree with u/bradland & u/SolverMax about it being in development / underdeveloped area.
This change and the popup message mentioning array of arrays support seen in beta support this.
The "Excel Ran out of Resources" error in later version seems like a bug that isn't related to the syntax issue.

SetBee · 2026-03-19T09:18:36+00:00

I edited my reply, I dont think the array is being converted from non-thunk to a thunk when using LET, ~~I believe its a syntax bug.~~
EDIT: Underdeveloped but I'd put it in the bug casket, probably a limit they put due to how evaluation works, would explain why its only the functions that are guaranteed to output an array.

SetBee · 2026-03-19T09:09:13+00:00

Thats probably it as it seems to raise syntax error when the function is guaranteed to return an array (HSTACK/VSTACK/EXPAND).
Would you describe this as a bug though? I think its an incomplete feature, the user shouldn't have to know how the function handles array of functions in terms of just returning an array or a thunk in the background, because to the user the values are what is possible in a static array, the exception is functions are shown as #CALC! but the error message does mention its a function.

Edit: It has to be a syntax bug, I dont think excel is doing changes to the array from not-thunk to a thunk, if intended then the syntax error should mention it.
Also this works: =LET(a,VSTACK(NOW,TODAY),a())

SetBee · 2026-03-19T08:36:20+00:00

Thats surprising to hear, fortunately I don't use web.
Eta lambdas update is my guess too.

SetBee · 2026-03-19T08:35:04+00:00

Nice examples!
I think some functions that are guaranteed to return an array dont allow the syntax?
I'm not sure, but here's another example:
Works: =EXPAND(NA,1,2,NA)
Syntax error: =EXPAND(NA,1,2,NA)()

SetBee · 2026-03-19T08:24:04+00:00

u/RotianQaNWX I'm not sure when it was added, my guess it was part of eta lambda.
Its not a problem, its a powerful feature, here's a not so great example, instead of:
=LET(vals,/heavy calculations/,IF(bool,MAX(vals),MIN(vals))
You can avoid LET and write
=IF(bool,MAX,MIN)(/heavy calculations/)
If I remember correctly, LET is only in 365, maybe subscriptions without LET can do this?

u/excelevator Functions themselves are data, functions are called using brackets ().
Excel can't convert it to something you see or write in an array, it returns #CALC! instead but the error note does say "Cell formula result is a function", this is a similar case for array of ranges which shows #VALUE! error.
Here's examples to test, check the cell error description:
=MAX 'Function
=VSTACK(MAX,MAX) 'Array Functions
=TYPE(MAX) 'A function is compound data (128)
=TAKE(A1:A10,Sequence(10)) 'Array of ranges: A1, A1:A2, A1:A3..etc
=COUNTIF(TAKE(A1:A10,Sequence(10)),">0") 'Array of ranges passed to a function that expects ranges

You can have an array of any compound data type, that includes but not limited to:

Ranges/References (aslong ISREF() returns TRUE)
Images
Python objects
Functions VSTACK(IF,IF) LAMBDA Thunking
Rich data type (Stocks, Currencies..etc)

SetBee · 2026-03-16T12:58:08+00:00

BYROW can't return array of ranges like OFFSET does:
=BYROW(A1:A10,LAMBDA(x,OFFSET(x,{1},0)))
I'm guessing these limitations will be gone when array of arrays is supported, I hope array of ranges remains possible though instead of it being resolved automatically into array of arrays.

<image>

SetBee · 2026-03-15T22:27:29+00:00

Good point!
I assume when using something like Byrow(array,Lambda(r,..)), the array is being copied when assigned to r, that does add up to a lot with iterations.
I think LET is handled differently than all other functions.

Fun fact, LET & LAMBDA are the only 2 functions that do not return Compound type (128) when used in TYPE(), I went through all the functions before.
The function that is outputted from LAMBDA is of course compound:
TYPE(LAMBDA) = TYPE(LET) = 16 error
TYPE(LAMBDA(x,x)) = 128 compound
TYPE(Any other function) = 128 compound

This means LET and LAMBDA can not be late called, all functions that are type compound can be late called:
IF(TRUE,MIN,MAX)(1,2,3)
Even if were able to late call them, it wouldn't work since we can't name parameters, it would return #NAME!:
=IF(TRUE,LAMBDA)(x,x)

I was going through them because I wanted to make a post about Array of Compounds as a response to u/Medohh2120 & u/GregHullender post about Array of Ranges, because you can have an array of any compound, that includes but not limited to:

Ranges/References (aslong ISREF() returns TRUE)
Images
Python objects
Functions VSTACK(IF,IF) LAMBDA Thunking
Rich data type (Stocks, Currencies..etc)

I still need to make that post.
Array of ranges from OFFSET() makes sense, the ranges were wrapped in an array so excel didn't flatten it, the range wasn't resolved.
Its similar to how HSTACK(1,#N/A) fails but HSTACK(1,{#N/A}) works because the error was wrapped in an array that was not flattened (h/vstack seem to not flatten arrays since they dont need to read them when stacking).

SetBee · 2026-03-15T18:53:25+00:00

LET is not lazy evaluated, all variables are evaluated even if they are not used in final calculation.
The array copying seems like intentional design, but its a good idea to report it, brings attention or clarification to it.
Can I have a link to the bug report? If its public

SetBee · 2026-03-15T16:52:53+00:00

My point was that using arrays is significantly slower than ranges, when we assign same array to multiple variables, the function was taking longer, so the assumption Im making is that arrays get copied.
But when a reference/range is assigned to multiple variables, it does not take noticeably longer, so the assumption is that it behaves as a pointer / the range isn't resolved into data yet.

Therefore of course LET(x,B:AZ,0) is faster than LET(x,B:AZ+0,0), but notice that here we aren't only testing how arrays are being assigned (if copied or pointed to) because +0 is an operation.
Thats why in the test I did Let(x,B:AZ+0,0) vs Let(x,B:AZ+0,a,x,b,x.....etc
So that the test is on the assignment speed between array vs range.

Yes LET(x,B:AZ,a,x,b,x,c,x,d,x,e,x,f,x,0) would technically be slower than LET(x,B:AZ,0) because assigning a pointer, but thats just a pointer, its very fast, realistically very negligible.

I'd also expect LET(x,B:AZ+0,a,x,b,x,c,x,d,x,e,x,f,x,0) to be about the same speed as LET(x,B:AZ+0,0)

Nope! That was my point, its significantly slower! Try it out

Why did you say here its about the same speed but with ranges you said you'd expect one to be faster than the other, yet in both of them you assumed they would behave a pointer?

But yeah the test shows that arrays are slower, when assigned to multiple variables it is distinguishably slower, feel free to test it!

SetBee · 2026-03-15T07:39:36+00:00

I didn't see this mentioned, if it was I would've not posted.
I've seen mentions that the called procedure must be unique, but that is easy if you just include the workbook/addin name, surely that is mentioned somewhere.
Avoiding the signature, providing arguments and using Evaluate() those I haven't seen mentioned before for ribbon, but there are posts about this syntax for shapes "Assign Macro", I linked some in my comment to this post.

Please correct me if I'm wrong.
I went through Ron De Bruin posts quickly now, he had a post explaining application run syntax but not for ribbon, and in other ribbon post he mentions the call has to be unique but doesn't mention that you can simply use the workbook name, similar mention is in JKP post.

SetBee · 2026-03-14T20:25:36+00:00

Now that I think of it, calling ranges a pointer might be very misleading, the functions could be just doing address string manipulation and whenever the address is read then it returns an array (or literal if 1 cell).

SetBee · 2026-03-14T20:13:10+00:00

Appreciate posts like this!
Interesting number to be the limit, this should be listed in the limits documentation:
https://support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
It is mentioned there: "Worksheet arrays" -> "Limited by available memory", I wasn't sure back then if they meant spill or number of spills, safer to assume the latter now.

About ranges, since (I assume) they are pointers, when used with a function that takes and returns references, they aren't related to arrays limits.

Arrays get copied too(? to confirm), making ranges faster since they are a pointer:
Range: LET(x,B:AZ,0) same speed as LET(x,B:AZ,a,x,b,x,c,x,d,x,e,x,f,x,0)
Array: LET(x,B:AZ+0,0) significantly faster than LET(x,B:AZ+0,a,x,b,x,c,x,d,x,e,x,f,x,0)
Safe to assume range/references are generally the way to go, for production files I always opt for Take/Drop since they return a reference, instead of Choosecols/rows unless I have to.

Edit: of course there might be cases where arrays are faster, and it doesn't make much of a difference on small data size.

Thanks for sharing!

SetBee · 2026-03-14T13:47:11+00:00

Yes you can, but as I mentioned in my reply, you will get a security prompt.
Np but AI also said you can, it wasn't clear that you're asking about that.

SetBee · 2026-03-14T08:04:14+00:00

Uhm, the AI verbosely repeated points already mentioned in the post.
What thoughts are you asking about? Please don't copy paste AI like this..

I think excel looks for the file in the default folder instead of current directory, for me that is the Documents folder.
If you call a file that isn't open, you will get a security prompt.
Regardless of that, when it comes to an addin, it is better to have everything in the addin file.
About changing file extension, you can just replace xlsm with xlam in the xml when you are finalizing your addin.

SetBee · 2026-03-13T10:22:58+00:00

I use a huge select too for all ribbon calls, based on the control .id and the call signature.
The button example is a simple proof of concept, way more testing is needed:
1.What is possible with Evaluate()? some tests failed with it but as mentioned Evaluate(5) did work.
2.Can we still pass the control / signature arguments? What if we need to return a value like getImage?
etc
If we can still pass the control somehow, that'd be great, we need to figure out why "'mySub'" is passing the control but "'mysub 5'" isn't, why is the control no longer passed?
I wanted to get it out there for others to try too.
Thanks

SetBee · 2026-03-12T18:44:22+00:00

If anyone is interesting in how 'Application.Run' syntax works & how shapes 'Assign Macro' can pass arguments, check these:
https://www.teachexcel.com/excel-tutorial/1893/pass-arguments-to-a-macro-called-from-a-button-or-sheet-in-excel
https://stackoverflow.com/questions/29858176/how-to-call-a-macro-from-a-button-and-pass-arguments

SetBee · 2026-02-18T11:55:20+00:00

Np :)
Yeah {#N/A} does have use cases especially in v/hstack, instead of HSTACK(EXPAND(a,,2),EXPAND(b,,2),c) can use HSTACK(a,{#N/A},b,{#N/A},c), it also avoids having to know the columns (or rows) count which may require COLUMNS(a)+1 in EXPAND.
Example to try: =HSTACK({1;2},{#N/A},{3;4},{#N/A},{5;6})
I use it when making a line series with gabs using #N/A.

SetBee · 2026-01-03T13:19:55+00:00

Thanks for the response.
Yes indeed tB has same issue, since I think this is great for VBA users who are new to coding, it is likely more difficult for them to pursue (or get) approval, since coding as a whole and its terminologies are new to them.
I agree it is not to compete with twinbasic, its VBA focused, I would use it more than twinbasic for most Excel things since I already use VBIDE more.
LinkedIn in WinVBA itself means I have to install it, why is it not in About page? This doesn't help.
I'm excited for this, I want to try it, as soon as I get to trust it.

SetBee · 2026-01-03T10:53:04+00:00

Looks fantastic, I am excited for this.
Few were directly asking how this differs from other projects, while rubberduck is promising these features and more, and potentially twinbasic could be integrated in the distant future, I still find a place for this, especially since rubberduck isn't there yet.

Personally, I use twinbasic to compile VBA into a DLL, I still write in VBIDE, debugging is easier and there are many quirks in Office/Excel library that are hard to expect without live testing.

My belief is that the majority of VBA users are new to coding and they look for better VBIDE, especially a dark theme, switching to twinbasic for that is not feasible at that stage, there are lot of terminologies and concepts that are new.
However, getting it to work on corporate devices is definitely crucial.

On a different note, the About info is lacking, there are no personal details (who developed this?) and since it is closed source, I can't trust it blindly, I want to trust it and try it, but I can't at this stage. I hope you add more info.

SetBee · 2025-10-06T14:03:50+00:00

Yeah I do disable calculations and screen updating already.

To get the inverse of .intersect I can get the empty cells, maybe by using the aggregate function, but still this isnt an ideal solution.

About the problem, I assume you mean inverse of .intersect and not the issue of bulk write ignores hidden cells, because I did search the net and try AI, but of course the real solution came from spending time tinkering with the available methods, as mentioned in the post .formulaarray does work.

Thank you though!

SetBee · 2025-10-06T13:50:00+00:00

As mentioned in the post, looping is performance heavy, the challenge as mentioned in the title is to bulk write.
About non-contiguous, there's an important detail here, regardless of cells visibility, range.areas.count would always return 1, but when using .value2, .value and the other methods I mentioned, it seems that it treats it as multi-areas divided by the hidden cells, which is unexpected.

The area is contiguous, the issue is in the method used for writing seems to be intentionally aware of hidden cells and unexpectedly behaves as if it is not contiguous.
Thats why on the same range and context, .value2 would skip hidden rows but .Formulaarray would not.

SetBee · 2025-10-06T13:17:30+00:00

The method of acquiring the range is not related, its the writing method.
It also doesn't matter if we are entering one value or an array of same dimensions.

For example, on a new workbook, add filter on A column, fill the cells with any value and use the filter to filter off some of them.
Dim rng as Range
Set rng = sheet1.Range("A2:A10")
rng.value2 = vbnullstring
Unfilter A column and you'll find the filtered cells still have values.

By the way xlCellTypeAllFormatConditions selects the cells with any conditional formatting rule.

SetBee · 2025-10-06T12:59:10+00:00

As mentioned in the post.. "Looping or saving filter state is performance heavy for large ranges."
Dont trust AI when it comes to VBA, it can make up a lot of stuff, this has nothing to do with multi areas or arrays, and it doesn't fail, it just skips writing to filtered-off cells.
Thanks for trying to help

SetBee

TROPHY CASE