+ Reply to Thread
Results 1 to 7 of 7

Compile Error - Expected End Sub

  1. #1
    Registered User
    Join Date
    12-04-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    5

    Unhappy Compile Error - Expected End Sub

    I am getting this error message for the below VBA

    Sub RunAllMacros()
    Procedure1
    Procedure2
    Procedure3
    Procedure4
    End Sub

    Sub Procedure1()
    Sub Recalc()
    If Not Range("D1").Value = "Stop" Then
    Range("A1").Value = Format(Now, "dd-mmm-yy")
    Range("A2").Value = Format(Time, "hh:mm:ss AM/PM")
    Call SetTime
    End Sub

    Sub Procedure2()
    Sub SetTime()
    SchedRecalc = Now + TimeValue("00:00:06")
    Application.OnTime SchedRecalc, "Recalc"
    End Sub

    Sub Procedure3()
    Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    End Sub

    Sub Procedure4()
    Sub Settle()
    If WorksheetFunction.CountIf(Columns("G"), "Settle") > 0 Then Exit Sub
    End Sub

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Compile Error - Expected End Sub

    Please use code tags when posting code.

    You have two Sub lines for each routine. Delete the red ones
    Please Login or Register  to view this content.
    Note that Procedure4 is pointless.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    12-04-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    5

    Re: Compile Error - Expected End Sub

    Thanks xlnitwit
    I added Procedure 4 to stop the loop if a word appears on a specific sheet. In any case, I am getting Compile Error Sub or Function Not Defined
    Sub Procedure1()
    If Not Range("D1").Value = "Stop" Then
    Range("A1").Value = Format(Now, "dd-mmm-yy")
    Range("A2").Value = Format(Time, "hh:mm:ss AM/PM")
    Call SetTime
    End Sub

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Compile Error - Expected End Sub

    Having looked at the code, I can't really follow what you are trying to achieve. You have 4 routines. The first one calls the second, which schedules the first one again. The third one cancels that schedule. The fourth one does nothing. What is the point of calling all 4 in succession?

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Compile Error - Expected End Sub

    no End If ??

  6. #6
    Registered User
    Join Date
    12-04-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    5

    Re: Compile Error - Expected End Sub

    Quote Originally Posted by xlnitwit View Post
    Having looked at the code, I can't really follow what you are trying to achieve. You have 4 routines. The first one calls the second, which schedules the first one again. The third one cancels that schedule. The fourth one does nothing. What is the point of calling all 4 in succession?
    The procedures macro activates a clock with date/time, the fourth stops the loop if a cell in the attached sheet reads " Settle"

    Thanks
    Last edited by chrs17sim; 02-20-2018 at 07:55 AM. Reason: Error

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Compile Error - Expected End Sub

    Quote Originally Posted by chrs17sim View Post
    the fourth stops the loop if a cell in the attached sheet reads " Settle"
    No, it doesn't. That might be the intention, but that routine does absolutely nothing. And I still don't understand why you want to schedule a macro and then immediately cancel it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] compile error expected: (
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2018, 03:10 AM
  2. [SOLVED] Compile error: Expected end with???
    By OpOrange in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2014, 08:27 AM
  3. Compile Error: Expected End With?
    By Jeka__7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2013, 10:30 AM
  4. Compile Error: Expected End Sub
    By Dcauth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2011, 01:56 PM
  5. Compile error: Expected End Sub
    By Leeboy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-31-2011, 09:44 AM
  6. Compile Error: Expected End Sub
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2011, 10:55 AM
  7. compile error: Expected: = ...Why?
    By cesw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2005, 08:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1