Afternoon - I'm challenged with getting my VBA-embedded formula (an array) to work. It works out side of VBA, which makes me think I'm missing an important VBA rule to formulas. Attached is a sample spreadsheet. The formula I'm trying to embed into VBA is located in column E.
The formula I'm using for VBA is as follows:
Thank you in advance for any help!
Please Login or Register to view this content.
Last edited by JBeaucaire; 08-31-2016 at 09:33 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
What happened to the one I have corrected it?
Good question. This is a different formula, and I followed your advice, but this particular formula is not working. Apologies for my semi-redundancy.
You should have at least acknowledge on the thread.
Array in VBA can only take a max of 255 characters.
You're right, I should acknowledge that on the thread. My fault.
Ok, I now understand that there is a 255 character limit on the VBA array formula. I suppose I'll have to determine an alternative approach to my problem.
Thank you.
Put the formula in as a Named Formula. Then you should only need to enter the formula name.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are round ways by splitting the array in to chunks of max 255 characters (Google it), but once the code becomes massive, it will suffer from speed.
1) IMPORTANT, make sure cell E2 is the currently selected cell as you do these steps.
2) Copy the formula into memory
3) CTRL-F3 to open the Name Wizard
4) NEW >
Name: Uniques
RefersTo: (paste in the formula as is)
5) OK.
6) Change the formula in E2 to simply =Uniques
(that's entered as a normal formula. All Named Formulas are array-able by default)
Last edited by JBeaucaire; 08-31-2016 at 03:18 PM.
Wow - thanks for the help, JBeaucaire! The step-by-step instructions were super helpful. Much appreciated. With reference to my VBA code, I'm assuming that reference to the "formula name" would then not need an array reference...??? The name of my formula is "Unique_Components" and it indeed has the array built into it (as you mentioned). However, the VBA translation gets fuzzy for me.
Sorry for the naive questions. I'm doing my best to learn.Please Login or Register to view this content.
Last edited by JBeaucaire; 08-31-2016 at 09:31 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
I believe I asked my question incorrectly. I'll start over with a new line of thinking. Thanks for the help.
Please read the forum rules and abide them regarding CODE tags around your posted code. I've fixed your post above.
As I noted in post #8 that this is regular formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks