Hi,
I've been using the following formula in a macro for some time and it has been working as expected, however with large data sets it becomes extremely slow and oftentimes will crash. So i'm trying to figure how I might be able to convert this formula into VBA.
Any help would be really appreciated.
Please Login or Register to view this content.
Thanks
How have you been using it in a macro? As a formula the macro is writing? Share your code or workbook with us...it will help us make suggestions as to how to improve the speed. Simply converting this to a macro executable form won't speed this up...in fact it will slow it down. VBA is single threaded vs. Excel Formulas calculating multi-threaded.
Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!
Agree with @Obsessed, but I did this just for fun. If you put this code in a Module, then you can call it like any other function by typing this into any cell:
Please Login or Register to view this content.Please Login or Register to view this content.
I'm interested in starting a career working with VBA, if anyone knows of any opportunities!
Thanks
That's the code I run in the macro but it is really just the code of an excel formula added to the macro and I think this is the reason it is so slow. If I can have it written in VBA I think it may improve the speed?Please Login or Register to view this content.
For example I saw a variation of the code below online and tried adjusting it to work with my formula but I can't seem to execute it properly.
Please Login or Register to view this content.
The first bit of code is faster actually. The first snippet shows you writing an Index Match within Excel and using Excel to calculate it, vs having the VBA engine calculate it, which is the second snippet. Do you have calculation set to manual? When I have a ton of these (I literally have one workbook that I use at work that has 4 million index matches and sumifs...don't ask.), I typically systematically write a few hundred thousand, calculate them (with Excel's engine, not VBA's), and then hardcode them, and then proceed. I can typically calculate 4 million of these formulas within 7 or 8 minutes on my machine doing it this way, and the indexed arrays are 100k+ lines.
Last edited by Obsessed; 11-06-2015 at 10:46 AM.
You might be able to speed it up by writing the formula to the entire range, instead of one cell and copying via autofill?
I may be interpreting that wrong, but where are you actually writing the formula? One cell (G2?) or many cells?
EDIT:
I think you might want something like this:
That's one of the awesome shortcuts that using the .FormulaR1C1 offers - you can write formulas to an enormous range of cells in one line of code.Please Login or Register to view this content.
Last edited by GeneralDisarray; 11-06-2015 at 10:47 AM.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
I'd expect that it's not writing the formulas that are taking forever, but rather calculating them. Excel can write 100k+ formulas in the matter of seconds even on the slowest of machines. Calculating them is another story.Originally Posted by GeneralDisarray
I'm not sure if i have the calculation set to manual or not.... how do I check? What I do is have the macro write that formula above (the first one) into a cell and then autofill all the way down to however many rows there are. This is where it becomes extremely slow. I have a about 5 or 6 similar formulas in the same macro so when they all run and autofill it can sometimes crash.
@walruseggmanAgree with @Obsessed, but I did this just for fun. If you put this code in a Module, then you can call it like any other function by typing this into any cell:
thanks for this... i tried it but it only returns zero in the cell?
As obsessed mentioned, please post an example worksheet, and also your expected/desired results.
Share your workbook with us if you can. I'm betting the reason it is slow is you have auto calculation on, and your index matches are volatile. Means every time you write a new formula to calculate, it recalculates all of them, making the sheet exponentially slower.
Last edited by Obsessed; 11-06-2015 at 11:01 AM.
Ok, I've attached a sample worksheet.
The formula for this worksheet (which would go in column I) would be as follows:
So I want to match on ID and Event and the word "ACK Received" and I want the value returned to be the date in sheet1.Please Login or Register to view this content.
The problem is I need to have three variations of this formula, one where the word is "Reprocessed" one with "NACK Received" and another with "Accepted". And the larger the dataset is the slower it becomes. I have over 1,000 row in the ones I'm working with at the moment and it basically just crashes.
@Obsessed
I think I have auto calculation on but how do i ensure the formula is executed if i turn it off?
Can you share the complete code as well? I'd like to see how you have it setup to see if it can be improved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks