How to Speed up My Calculation

Priyantha
StarLounger
Posts: 98
Joined: 10 Oct 2022, 02:52

How to Speed up My Calculation

Post by Priyantha »

Dear All,

I have crated a salary arrears calculation sheet & used a mcro to calculate arrears. For this purpose i used, 3 sheets, first _ "Arrearsformat", Second _ "Outpt" & third _ "Deta". There are may employees details in "Deta" & arrears format in "Arrearsformat" sheet. Filtered data will be in "Outpt" sheet belongs to C4 _ Employee number in "Arrearsformat" sheet. I am susing fitered deta in "Outpt" sheet for more calculation in "Arrearsformat" sheet.

My mcro is working crrectly. But it get more time to compleet process. Even though i searched a lot about this, I edite many times my code but, i did not get a proprer answer. When i ran the macro Debug _ Step into mode, i noticed that there was some deley in the below part of code under 'Proceed"

"Evaluate("IFERROR(INDEX('" & ws2.Name & "'!G:G, MATCH(C4&""-""&A" & startrow + 2 & "-DAY(A" & startrow + 2 & ")+1, '" & ws2.Name & "'!C:C&""-""&'" & ws2.Name & "'!B:B, 0)),0)") ".

I used many times this part of code to get deta from "Outpt" sheet & for more calulations in the "Arrearsformat" sheet. I would like any advice or modifications from experts as this or other problems may exist.


Looking forward any help to solve this problem.

Thanks,

Priyantha
You do not have the required permissions to view the files attached to this post.

Priyantha
StarLounger
Posts: 98
Joined: 10 Oct 2022, 02:52

Re: How to Speed up My Calculation

Post by Priyantha »

Does anyone help me? :

User avatar
HansV
Administrator
Posts: 78633
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to Speed up My Calculation

Post by HansV »

I'm sorry, but (1) the code is far too long and complicated to analyze, and (2) I have no idea how to speed it up.
Best wishes,
Hans

snb
4StarLounger
Posts: 588
Joined: 14 Nov 2012, 16:06

Re: How to Speed up My Calculation

Post by snb »

it's Data , not deta

Restrain reading/writing in a worksheet to once in VBA
Avoid the use of Ecelformulae in VBA, like all evaluate(' ... ")
You seem to have 2 workbooks in the workbook: 1 too many.
Avoid redundant Object Variables.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15655
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: How to Speed up My Calculation

Post by ChrisGreaves »

Priyantha wrote:
11 May 2024, 06:03
My mcro is working crrectly. But it get more time to compleet process.
Priyantha let's first define your problem.
(1) How much time does it take your macro to complete the process (in hours, minutes, and seconds)
(2) How much do you want to speed up your calculation? That is, how long did you expect it to take? Or else, how long do you want it to take?
Thanks, Chris
He who plants a seed, plants life.

Priyantha
StarLounger
Posts: 98
Joined: 10 Oct 2022, 02:52

Re: How to Speed up My Calculation

Post by Priyantha »

ChrisGreaves wrote:
14 May 2024, 17:46
Priyantha wrote:
11 May 2024, 06:03
My mcro is working crrectly. But it get more time to compleet process.
Priyantha let's first define your problem.
(1) How much time does it take your macro to complete the process (in hours, minutes, and seconds)
(2) How much do you want to speed up your calculation? That is, how long did you expect it to take? Or else, how long do you want it to take?
Thanks, Chris
Chris,

It takes the following times for one peocess.

Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)

I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.

Thanks,
Priyantha.

Priyantha
StarLounger
Posts: 98
Joined: 10 Oct 2022, 02:52

Re: How to Speed up My Calculation

Post by Priyantha »

snb wrote:
14 May 2024, 15:25
it's Data , not deta

Restrain reading/writing in a worksheet to once in VBA
Avoid the use of Ecelformulae in VBA, like all evaluate(' ... ")
You seem to have 2 workbooks in the workbook: 1 too many.
Avoid redundant Object Variables.
SNB,

There many detas in my "Deta" Sheet (about 50,000 rows) but cant upload sample sheet with all deta.
What can be done to get deta for "Evaluate function". I accepted any modification.

Thanks,
Priyantha.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15655
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: How to Speed up My Calculation

Post by ChrisGreaves »

Priyantha wrote:
15 May 2024, 11:55
It takes the following times for one peocess.
Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)
I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.
Priyantha, thank you for this data. If I understand it then you are looking for a speed up from 24 or 168 seconds to 8 or ten seconds.
This translates to a speed increase anywhere from 2.4 times to 20 times as fast, depending on the data?
That would be an acceptable measure of any solution?
Cheers, Chris
He who plants a seed, plants life.

Priyantha
StarLounger
Posts: 98
Joined: 10 Oct 2022, 02:52

Re: How to Speed up My Calculation

Post by Priyantha »

ChrisGreaves wrote:
15 May 2024, 12:12
Priyantha wrote:
15 May 2024, 11:55
It takes the following times for one peocess.
Time is :24.66796875 _ For Six Month Period Complet Process (Start Date 2023/11/01 End Date 2024/04/29)
Time is :77.640625 _ For One Year Period Complet Process (Start Date 2023/05/01 End Date 2024/04/29)
Time is :168.277343 _ For Two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29)
I hope to do two Year Period Complet Process (Start Date 2022/05/01 End Date 2024/04/29) about 8/10 seconds.
Priyantha, thank you for this data. If I understand it then you are looking for a speed up from 24 or 168 seconds to 8 or ten seconds.
This translates to a speed increase anywhere from 2.4 times to 20 times as fast, depending on the data?
That would be an acceptable measure of any solution?
Cheers, Chris
I will accept anything that takes less time than it currently takes. It is better if the formulas related to the calculations can be included in an excel sheet

Thanks
Priyantha