Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Compile Error Expected Sub, Function, or Property

  1. #1
    Join Date
    9th February 2012
    Posts
    11

    Compile Error Expected Sub, Function, or Property

    I am relatively new to coding in VBA and keep getting the compile error and cannot figure out why it keeps coming up. Any help on quelling the pop up would be helpful.

    Here is the code where I keep getting the error:

    VB:
     
    Sub tax() 
        Tester = 0 
        livePriceTotal = Cells(lastLiveRow + 1, 2) 
        liveTaxTotal = Cells(lastLiveRow + 1, 3) 
        silentPriceTotal = Cells(lastSilentRow + 1, 7) 
        silentTaxTotal = Cells(lastSilentRow + 1, 8) 
        taxRate = Range("G1").Value 
        taxExempt = Range("G2") 
        taxRateAdj = ((liveTaxTotal + silentTaxTotal) * (100)) / (livePriceTotal + silentPriceTotal - taxExempt) 
        Do 
             
             
            If liveTaxTotal + silentTaxTotal = (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 1 
             'Cells(1, 8) = taxRateAdj
            If liveTaxTotal + silentTaxTotal <= (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 2 
             'ActiveCell(1, 8) = taxRateAdj
            If liveTaxTotal + silentTaxTotal > (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 3 
            If Tester = 1 Then taxRate 
            If Tester = 2 Then taxRate 
            If Tester = 3 Then taxRateAdj 
        End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,055

    Re: Compile Error Expected Sub, Function, or Property

    You have a "Do" statement but no concluding Loop. Either conclude the loop or remove the Do.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th February 2012
    Posts
    11

    Re: Compile Error Expected Sub, Function, or Property

    Removed the "Do" statement.
    Still getting the same error this time the highlighted term is "taxRate"
    Did I not declare taxRate correctly?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Compile Error Expected Sub, Function, or Property

    Hello vtflee,

    Welcome to Ozgrid.

    You should never code without using Option Explicit. It forces you to properly declare your variables.
    Turn on Option Explicit while in the VB Editor: Menu > Tools > Options > check the box for Require Variable Declaration

    Unless you haven't posted all of your code, none of your variable are declared.
    I don't see where you have defined "lastliverow" or "lastSilentrow".

    Correct your variable delcarations, then your loop structure, then step through the code.

    Correct/Efficient Uses of Excel Loops
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    9th February 2012
    Posts
    11

    Re: Compile Error Expected Sub, Function, or Property

    Thanks.

    I did declare it in the beginning but did not include it in my previous code. I added it right after the sub tax and ran it again, but still getting the same error and it stops at taxRate again.

    Here's the code again:

    VB:
     
    Sub tax() 
        Dim livePriceTotal As Currency 
        Dim liveTaxTotal As Currency 
        Dim silentPriceTotal As Currency 
        Dim silentTaxTotal As Currency 
        Dim taxRate As Long 
        Dim taxCorrect As Long 
        Dim taxExempt As Currency 
        Dim taxRateAdj As Long 
         
         
        Tester = 0 
        livePriceTotal = Cells(lastLiveRow + 1, 2) 
        liveTaxTotal = Cells(lastLiveRow + 1, 3) 
        silentPriceTotal = Cells(lastSilentRow + 1, 7) 
        silentTaxTotal = Cells(lastSilentRow + 1, 8) 
        taxRate = Range("G1").Value 
        taxExempt = Range("G2") 
        taxRateAdj = ((liveTaxTotal + silentTaxTotal) * (100)) / (livePriceTotal + silentPriceTotal - taxExempt) 
         
         
        If liveTaxTotal + silentTaxTotal = (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 1 
         'Cells(1, 8) = taxRateAdj
        If liveTaxTotal + silentTaxTotal <= (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 2 
         'ActiveCell(1, 8) = taxRateAdj
        If liveTaxTotal + silentTaxTotal > (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 3 
        If Tester = 1 Then taxRate 
        If Tester = 2 Then taxRate 
        If Tester = 3 Then taxRateAdj 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,055

    Re: Compile Error Expected Sub, Function, or Property

    Where do you define lastLiveRow? What is in G1?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th February 2012
    Posts
    11

    Re: Compile Error Expected Sub, Function, or Property

    G1 is a tax rate. That value is being pulled from another spreadsheet. I used a vlookup function in the G1 cell.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,055

    Re: Compile Error Expected Sub, Function, or Property

    Post a workbook.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th February 2012
    Posts
    11

    Re: Compile Error Expected Sub, Function, or Property

    The other workbook where the tax rates are being pulled is too big to post. Hope that's not an issue.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,055

    Re: Compile Error Expected Sub, Function, or Property

    Hard to tell but if you declare something as Long it covers integers so if you have something 0.08 you need to declare as Double. Somewhat mystified by the error message you were getting though. I get an overflow error on the taxrateadj line.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: December 19th, 2006, 03:53
  2. Compile error: Sub or function not defined
    By samuels90 in forum EXCEL HELP
    Replies: 5
    Last Post: July 29th, 2006, 14:35
  3. Compile Error Expected Function or Variable
    By Niggle in forum EXCEL HELP
    Replies: 2
    Last Post: July 27th, 2006, 18:37
  4. Compile error expected =
    By Canuck in forum EXCEL HELP
    Replies: 1
    Last Post: November 17th, 2005, 10:30
  5. Compile error : Sub or Function not defined
    By alienscript in forum Excel and/or Access Help
    Replies: 5
    Last Post: October 15th, 2004, 05:21

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno