Results 1 to 8 of 8

Thread: My macro keep on running even though it should stop

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    4

    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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,104

    Re: My macro keep on running even though it should stop

    Quote Originally Posted by zaingaba View Post
    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    4

    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?

  4. #4
    Hyperactive Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    492

    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)

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,574

    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).

  6. #6
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,252

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    4

    Re: My macro keep on running even though it should stop

    Quote Originally Posted by passel View Post
    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.

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    4

    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
  •  



Click Here to Expand Forum to Full Width