So - you are ready to start including the new Dynamic Array formulas in your VBA project. Except, they never work. Why is Excel wrapping your formulas in an at @ sign? Argh. It is for backwards compatibility. Today, the insider tip on how to right forwards-compatible code and easily enter array formulas.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2307, Entering Dynamic Array Formulas with VBA.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I got a quick one but an important one here today so you know the new Dynamic Array formulas: FILTER, SORT, UNIQUE, SEQUENCE.
I want 8 rows, three columns starting at five, jumping by 17, and generates that array on the fly. So you would think that you should be able to use VBA to do that. So Alt F11 to switch over to VBA. A simple little one-line macro. Here range F2 formula is the same. Formulas entered. Let's run that.
And I'll switch back to Excel and what the heck, the formula is there. But somehow there's an @ sign.
Excel put the @ sign there. That's the new dynamic array way to say SINGLE() that we just want a single value here.
certainly not in the code. Put the @ sign there Excel is doing that and they're doing this for backwards compatibility. If you had a formula that today all of a sudden becomes an array formula and you weren't expecting it to become an array formula.
Wrap it in the @ sign. So, the easy way to solve this problem instead of .Formula use .Formula2.
The .Formula method says we're going to allow dynamic arrays. So back here to VBA.
Test dynamic arrays. Run.
and bam - the formula is there and no @ sign and it works.
Simple - once you know it. Until you know though, it will drive you crazy. That is one of the updates in the second edition of Excel Dynamic Arrays book. I know a lot of you downloaded this, the original edition for free. The 2nd edition is now out there on my site for $3. Click the "i" in the top-right-hand corner and hey, if you like these tips please down below the video, click Subscribe and ring that bell.
Feel free to post any questions or comments in the comments below. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I got a quick one but an important one here today so you know the new Dynamic Array formulas: FILTER, SORT, UNIQUE, SEQUENCE.
I want 8 rows, three columns starting at five, jumping by 17, and generates that array on the fly. So you would think that you should be able to use VBA to do that. So Alt F11 to switch over to VBA. A simple little one-line macro. Here range F2 formula is the same. Formulas entered. Let's run that.
And I'll switch back to Excel and what the heck, the formula is there. But somehow there's an @ sign.
Excel put the @ sign there. That's the new dynamic array way to say SINGLE() that we just want a single value here.
certainly not in the code. Put the @ sign there Excel is doing that and they're doing this for backwards compatibility. If you had a formula that today all of a sudden becomes an array formula and you weren't expecting it to become an array formula.
Wrap it in the @ sign. So, the easy way to solve this problem instead of .Formula use .Formula2.
The .Formula method says we're going to allow dynamic arrays. So back here to VBA.
Test dynamic arrays. Run.
and bam - the formula is there and no @ sign and it works.
Simple - once you know it. Until you know though, it will drive you crazy. That is one of the updates in the second edition of Excel Dynamic Arrays book. I know a lot of you downloaded this, the original edition for free. The 2nd edition is now out there on my site for $3. Click the "i" in the top-right-hand corner and hey, if you like these tips please down below the video, click Subscribe and ring that bell.
Feel free to post any questions or comments in the comments below. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
