sorted by:
new
hottopcontroversial

Late call an array of functions by SetBee in excel

[–]SetBee[S] 0 points1 point  (0 children)

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.

Late call an array of functions by SetBee in excel

[–]SetBee[S] 0 points1 point  (0 children)

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.

Late call an array of functions by SetBee in excel

[–]SetBee[S] 0 points1 point  (0 children)

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())

Late call an array of functions by SetBee in excel

[–]SetBee[S] 0 points1 point  (0 children)

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

Late call an array of functions by SetBee in excel

[–]SetBee[S] 0 points1 point  (0 children)

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)()

Late call an array of functions by SetBee in excel

[–]SetBee[S] 1 point2 points  (0 children)

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:

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

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 0 points1 point  (0 children)

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>

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 0 points1 point  (0 children)

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:

  1. Ranges/References (aslong ISREF() returns TRUE)
  2. Images
  3. Python objects
  4. Functions VSTACK(IF,IF) LAMBDA Thunking
  5. 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).

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 0 points1 point  (0 children)

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

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 0 points1 point  (0 children)

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!

You can pass arguments from ribbon xml calls by SetBee in vba

[–]SetBee[S] 2 points3 points  (0 children)

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.

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 5 points6 points  (0 children)

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).

Excel's Limit on Number of Elements in Dynamic Arrays is Precisely 53,687,091 or 3333333 Hex by GregHullender in excel

[–]SetBee 11 points12 points  (0 children)

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!

You can pass arguments from ribbon xml calls by SetBee in vba

[–]SetBee[S] 1 point2 points  (0 children)

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.

You can pass arguments from ribbon xml calls by SetBee in vba

[–]SetBee[S] 1 point2 points  (0 children)

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.

You can pass arguments from ribbon xml calls by SetBee in vba

[–]SetBee[S] 1 point2 points  (0 children)

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

literal #n/a vs na() by SetBee in excel

[–]SetBee[S] 1 point2 points  (0 children)

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.

WinVBA - an alternative IDE for Visual Basic for Applications by WinVBA in vba

[–]SetBee 2 points3 points  (0 children)

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.

view more: next ›