Help me to run this macro fast (Hydro energy modelling)

[jerrybetie]

Hi all sifu here,

My name is Jerry Betie and I'm from Malaysia. I'm new to this website. I need help to run my macro as shown below to run faster. It is basically for energy modeling from reservoir operation with daily input. I have tried to run it, but even after few hours, it still running. As I'm new to this vba pragramming, I couldn't find any better solution to run it fast. At last. I saw this website and hopefully somebody can help me to run my model below fast.

Option Explicit
Sub Button1_Click()
Dim x&, y&, A&, B&, C&, D&, vD7Value&, vJ13Value&, vJ14Value&
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = Cells(18, 2).Value + 20
vD7Value = Cells(7, 5).Value
vJ13Value = Cells(13, 11).Value
vJ14Value = Cells(14, 11).Value
For y = 21 To x
A = 0 ' First run plant discharge starts at 0.
B = Cells(y, 6).Value
C = Cells(y, 21).Value
D = 0.98 * vJ13Value
Do While B >= vD7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop
Do While B >= vD7Value
A = A + 0.1
If C >= vJ13Value Then Exit Do ' Rated power is achieved.
Loop
If C < vJ14Value Then Cells(y, 8).Value = 0 Else Cells(y, 8).Value = A
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I can't attached the original file as it is too large to attach.

However, I can attach the original file but with different vba script. The above script is using daily flow series while the attache is using monthly flow series. The attached macro excel is taking about 15 minutes to complete in my laptop. If I using daily inflow series, it will take forever to run. This is where I need your expert help.

I really appreciate for any comments and suggestion to improve my program as above.

Thanks in advance.

jerry.

 

[Smallman]

Hi Jerry

Welcome to the Forum.

Just cut out a sample of your data and upload it in file format. 100 -200 lines should do it with Variables covered. This is so people don't have to recreate the data which you have right in front of you. It takes more effort but you get a quicker response and it will be more tailored to your needs. This is a good lesson for future posts on Chandoo.

Take care

Smallman

 

[Marc L]

Hi Jerry,

the problem is a very bad code in the loops because they never end ‼​

Do While B >= vD7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop

If variable B is superior or equal to variable vD7Value, the loop starts
but 'cause this variable B never changes inside the loop, it's a NES ! (1)
Same for variable C when inferior to variable D, again a NES ‼

Same for the other loop, always NES ‼

So you have to think about your code logic …

Regards !

(1) NES : Never Ending Story … And particular to this case, a lock NES ‼​

 

[jerrybetie]

Smallman, Thanks for your comment. I do appreciate it. I will follow your suggestion in the future. Anyway, here is the part of the file that I planned to attach. I have taken out thousands of row to make the file size small enough to attach to this post.

 

[jerrybetie]

Hi Jerry,

the problem is a very bad code in the loops because they never end ‼​

Do While B >= vD7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop

If variable B is superior or equal to variable vD7Value, the loop starts
but 'cause this variable B never changes inside the loop, it's a NES ! (1)
Same for variable C when inferior to variable D, again a NES ‼

Same for the other loop, always NES ‼

So you have to think about the logical of your code …

Regards !

(1) NES : Never Ending Story … And particular to this case, a lock NES ‼​

Hi Marc L,

Thanks for your reply. I understand that there is something wrong with my code. It's just I couldn't figured out what is the problem as I'm new to vba and still not good at it and stil learning. Anyway, I will take note your comments.

 

[Marc L]

It's not a coding problem, whatever the development language, just review first your logic …
​

 

[jerrybetie]

It's not a coding problem, whatever the development language, just review first your logic …
​

Marc L, I will review my logic again in the mean time.

 

[jerrybetie]

Anyone there can help me on this?

Thanks.

 

[Marc L]

The code never ends because of a false logic …

So you have first to understand precisely your needs
and in a second time explain them well to us …

Regards.

______________________________________________________
If you can’t explain it simply, you don’t understand it well enough … (Albert Einstein)
​

 

[SirJB7]

Hi, jerrybetie!

I agree with Marc L, despite I know almost nothing about the subject of the workbook but I could be guided by you, it's you who should describe in detail the logic you used in each part of the code so as to could give you an advice.

For example, let us take this snippet:

Do While B >= vD7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop

The loop control is done with the variables B and vD7Value and none or them change within the Do...Loop cycle, so either the code will skip the loop completely or it will enter in and endless loop... Except C is not less than D, but none of them are neither set within the loop... so the final question is: what's the loop for?

With this things, what Marc L called logic, nothing can be done by nobody except you or anybody else who knows about the subject.

So if you write down in plain English (for laymen in energy stuff) step by step what should the code do, then you'd have more chances (at least chances) than for the time being (no chances at all, unless it appears the guy of the previous paragraph) that somebody will help you.

BTW, I tried to analyze the uploaded file but:
a) It has a a lot of circular references: RESOP G21, 'Reservoir volume' J5, ...
b) It references a UDF non existent function: Interpolate, 'Reservoir volume' J4:J5, at least.

Under that conditions only a magician would be able to help you.

Regards!

 

[jerrybetie]

The code never ends because of a false logic …

So you have first to understand precisely your needs
and in a second time explain them well to us …

_________________________________________________
If you can’t explain it simply, you don’t understand it well enough … (Albert Einstein)
​

Hi Marc L. I have used the second attachment which is the code not much different from the first. I manage to get the result. But it takes 15 minutes to run. That is for monthly inflow series. If I use daily inflow series, it will takes forever. I like to run it with daily inflow series as it will give me more accurate result in term of energy generated by hydropower plant.

This model is basically about reservoir operation study. I will explain it from the extracted code from my earlier email.


x = Cells(18, 2).Value + 20 - Represent number of rows of daily inflow
vD7Value = Cells(7, 5).Value – Minimum operating level of reservoir
vJ13Value = Cells(13, 11).Value – Target power for this hydro plant
vJ14Value = Cells(14, 11).Value – Minimum power available
For y = 21 To x – for projection daily inflow series from day 1 until 50 years later
A = 0 ' First run plant discharge starts at 0.
B = Cells(y, 6).Value – Month end reservoir level
C = Cells(y, 21).Value – Power generated by hydro plant
D = 0.98 * vJ13Value – 98% of the target power
Do While B >= vD7Value ' Do while Current reservoir level above MOL
A = A + 5 ' Increment @ 5. Plant discharge increase every 5 step
If C >= D Then Exit Do ' almost there. If power generated by hydro more than 98% of target power then loop
Loop
Do While B >= vD7Value – While month end reservoir level more than minimum reservoir level
A = A + 0.1 Discharge increment at 0.1 step to get closer to the discharge that can be used to obtain the target power.
If C >= vJ13Value Then Exit Do ' Rated power is achieved. If generated power is more or equal to target power, then stop.
Loop
If C < vJ14Value Then Cells(y, 8).Value = 0 Else Cells(y, 8).Value = A – If generated power power lesser that the target power, then discharge is zero mean no discharge is allowed to enter turbine to conserve water in the reservoir.
This code is programmed to achieve the target power for the hydro plant. In order to achieve the power, there are criteria and rules to follows.
1. The month end reservoir level must above the minimum operating level (mol). Below mol, no power generated.
2. If month end reservoir level above the mol, what is the optimum discharge to generate target power? Here is when I set the code that discharge will start at zero with 5 cumecs interval to get closer the optimum discharge as fast as I can. Why not 10?20?50 then? I will explain it below.
3. With discharge value increase in 5 cumecs step, and the power generated is about 98% of the target power, and the reservoir level is still above mol, I set the discharge increment at 0.1 cumecs to get the desire discharge as close as I can to achieve the target power. Once achieved, then stop. If I sue discharge interval at 10, 20 and 59 cumecs here, it will take a long time to get closer to the optimum discharge as I use 0.1 cumecs for second iteration.
4. If generated power is lesser than the target power, the discharge will be zero to conserve water to allow it raise above the mol and start generate the power again.
Basically this program is to get the optimised water discharge to generate the target power in the future.
Hope this can help explain a bit on what this program about.

 

[jerrybetie]

Hi, jerrybetie!

I agree with Marc L, despite I know almost nothing about the subject of the workbook but I could be guided by you, it's you who should describe in detail the logic you used in each part of the code so as to could give you an advice.

For example, let us take this snippet:

Do While B >= vD7Value ' Current reservoir level above MOL
A = A + 5 ' Increment @ 5.
If C >= D Then Exit Do ' almost there.
Loop

The loop control is done with the variables B and vD7Value and none or them change within the Do...Loop cycle, so either the code will skip the loop completely or it will enter in and endless loop... Except C is not less than D, but none of them are neither set within the loop... so the final question is: what's the loop for?

With this things, what Marc L called logic, nothing can be done by nobody except you or anybody else who knows about the subject.

So if you write down in plain English (for laymen in energy stuff) step by step what should the code do, then you'd have more chances (at least chances) than for the time being (no chances at all, unless it appears the guy of the previous paragraph) that somebody will help you.

BTW, I tried to analyze the uploaded file but:
a) It has a a lot of circular references: RESOP G21, 'Reservoir volume' J5, ...
b) It references a UDF non existent function: Interpolate, 'Reservoir volume' J4:J5, at least.

Under that conditions only a magician would be able to help you.

Regards!

Hi Mr. SirJB7,

Thanks for your reply. I understand your comments above. I do appreciate it. Regarding the logic, I have explained it in brief in my earlier reply to Marc L. You may refer to it. Glad to explain more should you have any queries on it. Fo the spreadshet, I use the interpolation extension called Xlxtr which you can download from the internet. I'm not sure about the circular reference as It works if I'm using the monthly inflow series. It is only not working if I use the daily inflow series. How about this one.

 

[SirJB7]

Hi, jerrybetie!

Thanks to you for your efforts to explain the code. It's an interesting and I think that very useful use of Excel, towards energy efficiency, more than the more usual questions regarding administration or commercial applications. I'll be happy to help you with the code (tomorrow, not today, I'm at GMT-3), but I should say that at a 1st glance, what I pointed out in my previous post regarding the Do...Loop it's still as it was, and your explanations haven't brought any light on it.

About the interpolation extension, it'd have been preferable it we could avoid its use (I'm always reluctant to install anything I haven't tested thoroughly) but let me see how I can manage to handle it.

Regards!

 

[jerrybetie]

Hi, jerrybetie!

Thanks to you for your efforts to explain the code. It's an interesting and I think that very useful use of Excel, towards energy efficiency, more than the more usual questions regarding administration or commercial applications. I'll be happy to help you with the code (tomorrow, not today, I'm at GMT-3), but I should say that at a 1st glance, what I pointed out in my previous post regarding the Do...Loop it's still as it was, and your explanations haven't brought any light on it.

About the interpolation extension, it'd have been preferable it we could avoid its use (I'm always reluctant to install anything I haven't tested thoroughly) but let me see how I can manage to handle it.

Regards!

Hi SirJB7,

Thanks for your reply.
About the loop, it is for the cycle/repetition of calculation to get the discharge closer enough to the optimum discharge that enable hydro plant to deliver the target power.

In my working place, most of us here use xlxtr extension for the interpolation as we found difficulties on how to interpolate the series of data.

The excel file that I have uploaded in my reply to your email is based on the monthly data. I try to use the daily inflow data, and there is when my problem comes. My model is running forever on this and I couldn't get result. It must be a lot of different ways to write the code to meet my needs, however this is the code I can think of at this moment. I will wait for your kind help and assisstance.

If you don't mind, I can send the spreadsheet that I have prepared with daily time series as input to your personal email. I can't upload it here due to a large size (about 5MB).


Thanks.

 

[NARAYANK991]

Hi Jerry ,

I would like to comment on your macro in the file you have uploaded most recently ; the code is reproduced below :

Sub Button1_Click()
    Dim y, x As Integer
  
    x = Cells(18, 2).Value + 20
 
    For y = 21 To x
        ' First run plant discharge starts at 0.
        Cells(y, 8).Value = 0
      
        ' Current reservoir level above MOL
        Do While Cells(y, 6).Value >= Cells(7, 5).Value
          ' Increment @ 2.
          Cells(y, 8).Value = Cells(y, 8).Value + 2
          If Cells(y, 21).Value >= 0.98 * Cells(13, 11).Value Then
              ' almost there.
              Exit Do
          End If
        Loop
      
        Do While Cells(y, 6).Value >= Cells(7, 5).Value
            Cells(y, 8).Value = Cells(y, 8).Value + 0.1
            If Cells(y, 21).Value >= Cells(13, 11).Value Then
            Exit Do
            ' Rated power is achieved.
            End If
        Loop
      
        If Cells(y, 21).Value >= Cells(14, 11).Value Then
          Cells(y, 8).Value = Cells(y, 8).Value
        ElseIf Cells(y, 21).Value < Cells(14, 11).Value Then
          Cells(y, 8).Value = 0
        End If
    Next y
End Sub

The code posted above deals exclusively with worksheet cells ; the code reads from some of these cells , and writes to other cells.

The rest of the worksheet has formulae which also make use of the same cells.

So when the code modifies a worksheet cell , the modified value is used by formulae in other worksheet cells , and these changed values may presumably be accessed by the code ; thus , there is a complex interaction between the formulae and the code , which can result ultimately in the code completing execution , with correct output.

The code you first posted however , does not modify worksheet cells ; it only reads from them , and stores their values in VBA variables ; the code uses these variables repeatedly. If we reproduce the code , it is :

x = Cells(18, 2).Value + 20
vD7Value = Cells(7, 5).Value
vJ13Value = Cells(13, 11).Value
vJ14Value = Cells(14, 11).Value
 
For y = 21 To x
    A = 0 ' First run plant discharge starts at 0.
    B = Cells(y, 6).Value
    C = Cells(y, 21).Value
    D = 0.98 * vJ13Value
 
    Do While B >= vD7Value ' Current reservoir level above MOL
      A = A + 5 ' Increment @ 5.
      If C >= D Then Exit Do ' almost there.
    Loop
 
    Do While B >= vD7Value
      A = A + 0.1
      If C >= vJ13Value Then Exit Do ' Rated power is achieved.
    Loop
  
    If C < vJ14Value Then Cells(y, 8).Value = 0 Else Cells(y, 8).Value = A
Next

Let us analyze the code :

1. x stores the value of the worksheet cell B18 , after incrementing it by 20.

2. vD7Value stores the value of the worksheet cell E7 ; this , I think , is a mistake ; the VBA statement should be :

vD7Value = Cells(7, 4).Value

Similar corrections must be made to the next two statements , which should be :

vJ13Value = Cells(13, 10).Value
vJ14Value = Cells(14, 10).Value

In the first pass through the For ... Next loop , y has the value 21 ; using this value of y , the values of the other variables A , B , C and D are :

A is 0
B is the value of the worksheet cell F21
C is the value of the worksheet cell U21
D is 98% of the internal variable vJ13Value

When we come to the Do ... Loop construct , we can safely ignore the incrementing of the variable A ; what we are left with is the following :

Do While B >= vD7Value
If C >= D Then Exit Do
Loop

The check B >= vD7Value may or may not be TRUE based on what the values of B ( worksheet cell F21 ) and vD7Value ( worksheet cell D7 ) are ; if it is true , then execution passes into the loop to check whether C is greater than or equal to D ; if it not true , then execution does not pass into the loop , and the check C >= D is never done.

Even if execution passes into the loop , the check whether C is greater than or equal to D depends on whether the value of worksheet cell U21 is greater than 98 % of the vaue of worksheet cell J13 ; since both these are read outside the loop , it is a matter of chance whether the check will return TRUE or FALSE ; if it returns TRUE , the loop will be exited , else execution will continue within the loop forever till an error is generated because the value in A exceeds the upper limit of its declared type !

Similar comments can be made regarding the other loops.

You need to rewrite the procedure completely , so that it deals exclusively with worksheet cells , so that when the code modifies a worksheet cell , the formulae in the worksheet use this modified value , which in turn can be accessed by the code in its next iteration. This will ensure that not only does the code execution ultimately end , but also that your final results are correct.

Additionally , when you have such iterative calculation , and exchange of data between the code and the worksheet , you should not put your calculation mode to manual , since this will defeat the purpose of the code modifying worksheet cells during its iterations.

Narayan

 

[jerrybetie]

Hi Jerry ,

I would like to comment on your macro in the file you have uploaded most recently ; the code is reproduced below :

Sub Button1_Click()
    Dim y, x As Integer
 
    x = Cells(18, 2).Value + 20
 
    For y = 21 To x
        ' First run plant discharge starts at 0.
        Cells(y, 8).Value = 0
     
        ' Current reservoir level above MOL
        Do While Cells(y, 6).Value >= Cells(7, 5).Value
          ' Increment @ 2.
          Cells(y, 8).Value = Cells(y, 8).Value + 2
          If Cells(y, 21).Value >= 0.98 * Cells(13, 11).Value Then
              ' almost there.
              Exit Do
          End If
        Loop
     
        Do While Cells(y, 6).Value >= Cells(7, 5).Value
            Cells(y, 8).Value = Cells(y, 8).Value + 0.1
            If Cells(y, 21).Value >= Cells(13, 11).Value Then
            Exit Do
            ' Rated power is achieved.
            End If
        Loop
     
        If Cells(y, 21).Value >= Cells(14, 11).Value Then
          Cells(y, 8).Value = Cells(y, 8).Value
        ElseIf Cells(y, 21).Value < Cells(14, 11).Value Then
          Cells(y, 8).Value = 0
        End If
    Next y
End Sub

The code posted above deals exclusively with worksheet cells ; the code reads from some of these cells , and writes to other cells.

The rest of the worksheet has formulae which also make use of the same cells.

So when the code modifies a worksheet cell , the modified value is used by formulae in other worksheet cells , and these changed values may presumably be accessed by the code ; thus , there is a complex interaction between the formulae and the code , which can result ultimately in the code completing execution , with correct output.

The code you first posted however , does not modify worksheet cells ; it only reads from them , and stores their values in VBA variables ; the code uses these variables repeatedly. If we reproduce the code , it is :

x = Cells(18, 2).Value + 20
vD7Value = Cells(7, 5).Value
vJ13Value = Cells(13, 11).Value
vJ14Value = Cells(14, 11).Value
 
For y = 21 To x
    A = 0 ' First run plant discharge starts at 0.
    B = Cells(y, 6).Value
    C = Cells(y, 21).Value
    D = 0.98 * vJ13Value
 
    Do While B >= vD7Value ' Current reservoir level above MOL
      A = A + 5 ' Increment @ 5.
      If C >= D Then Exit Do ' almost there.
    Loop
 
    Do While B >= vD7Value
      A = A + 0.1
      If C >= vJ13Value Then Exit Do ' Rated power is achieved.
    Loop
 
    If C < vJ14Value Then Cells(y, 8).Value = 0 Else Cells(y, 8).Value = A
Next

Let us analyze the code :

1. x stores the value of the worksheet cell B18 , after incrementing it by 20.

2. vD7Value stores the value of the worksheet cell E7 ; this , I think , is a mistake ; the VBA statement should be :

vD7Value = Cells(7, 4).Value

Similar corrections must be made to the next two statements , which should be :

vJ13Value = Cells(13, 10).Value
vJ14Value = Cells(14, 10).Value

In the first pass through the For ... Next loop , y has the value 21 ; using this value of y , the values of the other variables A , B , C and D are :

A is 0
B is the value of the worksheet cell F21
C is the value of the worksheet cell U21
D is 98% of the internal variable vJ13Value

When we come to the Do ... Loop construct , we can safely ignore the incrementing of the variable A ; what we are left with is the following :

Do While B >= vD7Value
If C >= D Then Exit Do
Loop

The check B >= vD7Value may or may not be TRUE based on what the values of B ( worksheet cell F21 ) and vD7Value ( worksheet cell D7 ) are ; if it is true , then execution passes into the loop to check whether C is greater than or equal to D ; if it not true , then execution does not pass into the loop , and the check C >= D is never done.

Even if execution passes into the loop , the check whether C is greater than or equal to D depends on whether the value of worksheet cell U21 is greater than 98 % of the vaue of worksheet cell J13 ; since both these are read outside the loop , it is a matter of chance whether the check will return TRUE or FALSE ; if it returns TRUE , the loop will be exited , else execution will continue within the loop forever till an error is generated because the value in A exceeds the upper limit of its declared type !

Similar comments can be made regarding the other loops.

You need to rewrite the procedure completely , so that it deals exclusively with worksheet cells , so that when the code modifies a worksheet cell , the formulae in the worksheet use this modified value , which in turn can be accessed by the code in its next iteration. This will ensure that not only does the code execution ultimately end , but also that your final results are correct.

Additionally , when you have such iterative calculation , and exchange of data between the code and the worksheet , you should not put your calculation mode to manual , since this will defeat the purpose of the code modifying worksheet cells during its iterations.

Narayan

Thanks NARAYANK991 for your valuable advise. I really appreciate it. It just a matter that I need some time to digest and understand by what you mean above as I'm still learning on excel macro. I understand some of your points. However, I'm not sure on what you mean as below.

2. vD7Value stores the value of the worksheet cell E7 ; this , I think , is a mistake ; the VBA statement should be :
vD7Value = Cells(7, 4).Value - The cell that I referred to is cells(7,5). How it could be cells (7,4)?
Similar question to the next two statements.
vJ13Value = Cells(13, 10).Value - Should be refer to Cells(13,11)
vJ14Value = Cells(14, 10).Value - Should be refer to Cells(14,11)

By the way, I will try to rewrite my code as per your advise. Just need time to do so. I will let you know if I'm manage to get it correctly or not.

 

[NARAYANK991]

Hi Jerry ,

Sorry for the late reply ; I assumed that vD7Value refers to the value in D7 , which would in VBA become :

Cells(7,4).Value

A similar argument can be made for Cells(13,10).Value referring to J13 ( vJ13Value ) and Cells(14,10).Value referring to J14 ( vJ14Value ).

Narayan

 

[Marc L]

And the loop still runs forever …​

 

[Hui]

I haven't had a chance to have a big look at this but

1. Fix the Circular Reference errors
2. Disable screen updating
3. Disable Calculation
4. Run optimised Code
5. Enable screen updating
6. Enable Calculation
see what impact that makes to the run time