-
Oct 6th, 2020, 11:32 PM
#1
Thread Starter
New Member
My macro keep on running even though it should stop
Hi
I am a new VBA user and working on a project. I made two two macros i.e. models. In the beginning I wanted to ask the user to decide which model to run. So when user enters 1 the model 1 runs fine right till the end when I entered a msg box whether he want to recalculate. Now here is the problem. If I click yes then everything works accordingly that it runs all over again but if I click 'No' it should end it there. But it reruns the model 1. I am not sure what is the problem.
Here is my code
Code:
Sub model()
modelchoice = InputBox("What model to run?")
If modelchoice = 1 Then
Run ([Retirementincome()])
Else: modelchoice = 2
Run ([Semiannualsavings()])
End If
End Sub
Sub Retirementincome()
RetirementcalculatorA:
Cells.Clear
Dim PMTbeforetirement, CurrentAge As Integer, RetirementAge As Integer, lifeexpat As Integer, rate, FVatretirement, PMTafterretirement, Monthlyincome
PMTbeforeretirement = InputBox("What is the amount to be saved semi annualy?", "Enter $ Amount")
Cells(1, 1).Value = "Semi annual savings till retirement"
Cells(1, 2).Value = PMTbeforeretirement
Cells(1, 2).NumberFormat = "$#,##0.00"
CurrentAge:
CurrentAge = InputBox("What is your current age in years?", "Enter current age in years between 18 and 65")
If CurrentAge >= 18 And CurrentAge <= 65 Then
Cells(2, 1).Value = "Current Age"
Cells(2, 2).Value = CurrentAge
Else: GoTo CurrentAge
End If
RetirementAge:
RetirementAge = InputBox("When is your retirement age in years?", "Enter retirement age in years between 55 and 75")
If RetirementAge >= 55 And RetirementAge <= 75 Then
Cells(3, 1).Value = "Retirement Age"
Cells(3, 2).Value = RetirementAge
Else: GoTo RetirementAge
End If
lifeexpat:
lifeexpat = InputBox("What is your life expactancy in years?", "Enter life expactancy in years between Retirement age and 100")
If lifeexpat >= RetirementAge And RetirementAge <= 100 Then
Cells(4, 1).Value = "Life Expactancy"
Cells(4, 2).Value = lifeexpat
Else: GoTo lifeexpat
End If
rate:
rate = InputBox("What is the interest rate?", "Enter interest rate as percentage points Hint:interest rate should be positive")
If rate >= 0 Then
Cells(5, 1).Value = "Interest Rate"
Cells(5, 2).Value = rate / 100
Cells(5, 2).NumberFormat = "0.00%"
Else: GoTo rate
End If
confirm = MsgBox("Please confirm,Semi Annual Savings=" & PMTbeforeretirement & ", Current Age=" & CurrentAge & ", Retirement Age=" & RetirementAge & ", Life Expactancy=" & lifeexpat & ", Interest Rate=" & rate & "%", vbYesNo, "Please confirm all inputs")
If confirm = 6 Then
FVatretirement = -FV(Cells(5, 2).Value / 2, (Cells(3, 2).Value - Cells(2, 2).Value) * 2, Cells(1, 2).Value)
Cells(6, 1).Value = "Future value at retirement"
Cells(6, 2).Value = FVatretirement
Cells(6, 2).NumberFormat = "$#,##0.00"
PMTafterretirement = Pmt(Cells(5, 2).Value / 2, (Cells(4, 2).Value - Cells(3, 2).Value) * 2, -Cells(6, 2).Value)
Cells(7, 1).Value = "Semi annual payments after retirements till death"
Cells(7, 2).Value = PMTafterretirement
Cells(7, 2).NumberFormat = "$#,##0.00"
Monthlyincome = PMTafterretirement / 6
Cells(8, 1).Value = "Monthly retirement income till death"
Cells(8, 2).Value = Monthlyincome
Cells(8, 2).NumberFormat = "$#,##0.00"
Columns("A").AutoFit
Columns("B").AutoFit
End If
again = MsgBox("Do you want to recalculate?", vbYesNo)
If again = 6 Then
Run ([model()])
Else:
Exit Sub
End If
End Sub
Sub Semiannualsavings()
RetirementcalculatorB:
Cells.Clear
Dim PMTbeforetirement, CurrentAge As Integer, RetirementAge As Integer, lifeexpat As Integer, rate, PVatretirement, PMTafterretirement, Monthlysavings
PMTafterretirement:
PMTafterretirement = InputBox("What is the amount you want after retirement semi annualy? Amount should be greater then $12000 i.e. $2000 per month", "Enter $ Amount")
If PMTafterretirement >= 12000 Then
Cells(1, 1).Value = "Semi annual Retirement Income "
Cells(1, 2).Value = PMTafterretirement
Cells(1, 2).NumberFormat = "$#,##0.00"
Else: GoTo PMTafterretirement
End If
CurrentAge:
CurrentAge = InputBox("What is your current age in years?", "Enter current age in years between 18 and 65")
If CurrentAge >= 18 And CurrentAge <= 65 Then
Cells(2, 1).Value = "Current Age"
Cells(2, 2).Value = CurrentAge
Else: GoTo CurrentAge
End If
RetirementAge:
RetirementAge = InputBox("When is your retirement age in years?", "Enter retirement age in years between 55 and 75")
If RetirementAge >= 55 And RetirementAge <= 75 Then
Cells(3, 1).Value = "Retirement Age"
Cells(3, 2).Value = RetirementAge
Else: GoTo RetirementAge
End If
lifeexpat:
lifeexpat = InputBox("What is your life expactancy in years?", "Enter life expactancy in years between Retirement age and 100")
If lifeexpat >= RetirementAge And RetirementAge <= 100 Then
Cells(4, 1).Value = "Life Expactancy"
Cells(4, 2).Value = lifeexpat
Else: GoTo lifeexpat
End If
rate:
rate = InputBox("What is the interest rate?", "Enter interest rate as percentage points")
If rate >= 0 Then
Cells(5, 1).Value = "Interest Rate"
Cells(5, 2).Value = rate / 100
Cells(5, 2).NumberFormat = "0.00%"
Else: GoTo rate
End If
confirm = MsgBox("Please confirm,Semi Annual Retirement Income=" & PMTafterretirement & ", Current Age=" & CurrentAge & ", Retirement Age=" & RetirementAge & ", Life Expactancy=" & lifeexpat & ", Interest Rate=" & rate & "%", vbYesNo, "Please confirm all inputs")
If confirm = 6 Then
PVatretirement = PV(Cells(5, 2).Value / 2, (Cells(4, 2).Value - Cells(3, 2).Value) * 2, -Cells(1, 2).Value)
Cells(6, 1).Value = "Present Value at retirement"
Cells(6, 2).Value = PVatretirement
Cells(6, 2).NumberFormat = "$#,##0.00"
PMTbeforeretirement = Pmt(Cells(5, 2).Value / 2, (Cells(3, 2).Value - Cells(2, 2).Value) * 2, -Cells(6, 2).Value)
Cells(7, 1).Value = "Semi Annual Savings till retirement "
Cells(7, 2).Value = PMTbeforeretirement
Cells(7, 2).NumberFormat = "$#,##0.00"
Monthlysavings = PMTbeforeretirement / 6
Cells(8, 1).Value = "Monthly Savings till Retirement"
Cells(8, 2).Value = Monthlysavings
Cells(8, 2).NumberFormat = "$#,##0.00"
Columns("A").AutoFit
Columns("B").AutoFit
End If
again = MsgBox("Do you want to recalculate?", vbYesNo)
If again = 6 Then
Run ([model()])
End If
End Sub
Any help would be highly appreciated.
Thanks.
Last edited by si_the_geek; Oct 7th, 2020 at 05:41 AM.
Reason: added Code tags
-
Oct 6th, 2020, 11:35 PM
#2
Re: My macro keep on running even though it should stop
Originally Posted by zaingaba
I am a new VBA user and working on a project.
Rather than just posting in the first forum in the list on the home page, probably better to post in the one that is relevant to the language you're using. I have asked the mods to move this thread to the Office Development forum. Please don't create a duplicate thread in the meantime. You might consider formatting your code snippet as code though, for readability.
-
Oct 6th, 2020, 11:38 PM
#3
Thread Starter
New Member
Re: My macro keep on running even though it should stop
Hey thanks for the guidance. What do you mean by formatting your code as snippet?
-
Oct 7th, 2020, 03:44 AM
#4
Re: My macro keep on running even though it should stop
use the VE button or the # button in the editor
Last edited by Delaney; Oct 7th, 2020 at 08:28 AM.
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Oct 7th, 2020, 05:43 AM
#5
Re: My macro keep on running even though it should stop
Welcome to VBForums
I have moved this thread from the 'VB.Net' forum to the 'Office Development/VBA' forum.
I have also added Code tags to post 1 (using the # button).
-
Oct 7th, 2020, 07:34 AM
#6
Re: My macro keep on running even though it should stop
I don't know off hand, but this syntax looks odd. It may inline the else, and then execute the next line, which you wouldn't want.
So, wherever you use "Else:" I would remove the :, and put the line after the : on the next line.
It might not make a difference, but it isn't the usual syntax one expects to see.
i.e. what it might be doing is modelchoice is 1, so it does the Run Retirementincome, and then it sees the Else with code after it, so treats it as an inline Else and skips the code that follows the Else on that line, but then does the Run Semiannualsavings because it isn't part of the inline Else.
Code:
'Instead of this:
If modelchoice = 1 Then
Run ([Retirementincome()])
Else: modelchoice = 2
Run ([Semiannualsavings()])
End If
'I would use this
If modelchoice = 1 Then
Run ([Retirementincome()])
Else
modelchoice = 2
Run ([Semiannualsavings()])
End If
Also, doing a Run ([model()]) in the code to repeat the selection seems like it would be recursive, but perhaps it doesn't matter if you don't do it a large number of times, and the stack will unwind. I haven't used Office Macros much, so don't know that it is recursive, but it just appears that way to me.
Last edited by passel; Oct 7th, 2020 at 07:39 AM.
"Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930
-
Oct 7th, 2020, 08:42 AM
#7
Thread Starter
New Member
Re: My macro keep on running even though it should stop
Originally Posted by passel
I don't know off hand, but this syntax looks odd. It may inline the else, and then execute the next line, which you wouldn't want.
So, wherever you use "Else:" I would remove the :, and put the line after the : on the next line.
It might not make a difference, but it isn't the usual syntax one expects to see.
i.e. what it might be doing is modelchoice is 1, so it does the Run Retirementincome, and then it sees the Else with code after it, so treats it as an inline Else and skips the code that follows the Else on that line, but then does the Run Semiannualsavings because it isn't part of the inline Else.
Code:
'Instead of this:
If modelchoice = 1 Then
Run ([Retirementincome()])
Else: modelchoice = 2
Run ([Semiannualsavings()])
End If
'I would use this
If modelchoice = 1 Then
Run ([Retirementincome()])
Else
modelchoice = 2
Run ([Semiannualsavings()])
End If
Also, doing a Run ([model()]) in the code to repeat the selection seems like it would be recursive, but perhaps it doesn't matter if you don't do it a large number of times, and the stack will unwind. I haven't used Office Macros much, so don't know that it is recursive, but it just appears that way to me.
Hi,
Yes I have tried it like you said but the problem is still there. At the end if I click No it still runs model 1 again.
-
Oct 7th, 2020, 08:52 AM
#8
Thread Starter
New Member
Re: My macro keep on running even though it should stop
Hi,
I think I have found a solution. I was using Run to run another Sub but now I have used Call which is working fine now.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|