VBA Compilation Error Code After END SUB Statement

Question

VBA produces a compile error which says that I have code after an END SUB statement.  The problem is that the code was running well to this point.  Tried to move a DIM of the variable dbs to a Module from a Form's Command Button Click Event and that's when the compile errors started happening.  Restore my dbs SET statement but the compile error persists.

The early part of the VBA code is listed here: ( If more is needed I can post that too.)

Private Sub Add_Another_Group_Click()

'Dimension String variables to build SQL Statement for Selection

    Dim sql_construct_element, SqlStr As String

'Dimension dbs Database reference name and set value

     Dim dbs As Object

      Set dbs = CurrentDb

'Dimension SQL String to Get Count of Records Selected

    Dim sql_count_statment As String

'Increment the Group_Number for this Group Definition Try

    'Dim Group_Number As Integer

    Group_Number = Group_Number + 1

' Dimension New Group Table Name variable and establish value

    'Dim Group_Table_Name As String

    Group_Table_Name = "Group" & Group_Number

' Dimension variable to contain count of records in Group

    Dim Group_Count As Integer

'Create a string variable to produce the  quotation mark.

    Dim strquot_mark As String

    strquot_mark = Chr$(34)

'Dimension Check Search Variables

    Dim Search_Municipal, Search_Legislator, Search_Government_Official, Search_ILC_Check As Integer

Hope something pops out here.

Its not the name of the Command Button because I tried it with spaces instead of underscores. 

Michael

Answer 1

RunningManHD, I have carefully looked at your comments.  I think that the DIM of a list of variables is a legitimate syntax.  I submit as reference the following URL:

 https://msdn.microsoft.com/en-us/library/7ee5a7s1.aspx

The long names have troubled me but if someone other than myself looks at the code, I would want them to be able to follow what I have done.  The Camel-Case notation  is an alternate method .  In my system of notation the underscores purpose is to keep each variable a single text string.

Thanks.

Michael

Michael,

Let me address your first statement and the reference you provided.  That reference is for Visual Studio 2015/Visual Basic (VB), not Visual Basic for Applications (VBA).  If you declare your variables without explicitly defining the data type, then those undefined variables will default to variant.  If a variant data type is your intention for those variables not explicitly defined, then all is well.  However, if you expect that the following declaration defines all of your variables as integer, you are mistaken.

Dim Search_Municipal, Search_Legislator, Search_Government_Official, Search_ILC_Check As Integer

The first three are variants.  If you doubt what I'm telling you, a simple test will prove my point. 

Place the following code just below your declaration and run your procedure. 

Debug.Print VarType(Search_Municipal)

Debug.Print VarType(Search_Legislator)

Debug.Print VarType(Search_Government_Official)

Debug.Print VarType(Search_ILC_Check)

Check the immediate window for the results.  What you will see is:

0

0

0

2

Another piece of advice is to define all of your variables at the head of the procedure, not throughout your code.  It is a best practice and it makes it a lot easier for the developer that follows behind you.  A practice I prefer to use is to define each of my variables separately, one below the other, not in-line as you have done.

As to your last statement about using underscores, I'm afraid I don't follow you.  You say that you are using underscores to keep variables as a single text string.  That doesn't make a lot of sense to me. 

Lastly, be sure you read through what Tom has posted.  He makes some additional good observations about your coding that you should make note of.

I'm glad you were able to spot your error problem.  Best of luck in your future coding.

RM

Answer 2

Hi RunningManHD,

I agree with you about data type prefixes, but not about abbrevs. I like abbreviations much better. Most of the time when I start typing abbr and hit Ctrl+Spacebar, intellisense helps me complete the variable name without spelling errors. I sleep better knowing that my successor can read my variable names without doing the in-memory expansion of abbreviations to what you really wanted to say. YMMV and reasonable people can disagree about this.

One thing you did not point out is that the OP is using a mismatch of variable naming conventions, which is always a REALLY BAD idea. "strquot_mark" seems to indicate he knows about data type prefixing, but the rest of the code negates that.

In our company we afford developers latitude as to WHICH variable naming convention they adopt, but once adopted, for this project it is adhered to 100%. No exceptions.

Another observation: The line "Group_Number = Group_Number + 1" does not compile, because Group_Number is undefined. Maybe it compiles for the OP, but then only because he did not have OPTION EXPLICIT in every module, per best practices, and did not set it to be the default in Tools > Options > Require Variable Declaration.

And we could go on and on. Kudos for the OP for posting code here. I'm hoping he takes these comments to heart.

And figures out the if/endif or similar mismatch which causes the original error in the first place. Me, I am using  Smart Indenter (

http://www.oaltd.co.uk/Indenter/Default.htm) so I don't easily fall prey to this problem.

Another reason why I do not, is because I always write the closing statement before the internal statements. For example I will write:

for i = 1 to 10

next i

before I write the body of that loop. Same with any other statements that belong together: if/else/endif, while/wend, etc. It's defensive programming.

'Nuf Said.

http://www.oaltd.co.uk/Indenter/Default.htm)

Answer 3

Thanks to both of you the information.  VBA was highlighting the first statement of the SUB as the offending statement so I was attempting to break the problem down into pieces.  If the problem wasn't evident in the code I provided, I was going to give the remaining code.

As I did some independent research, I continued with the break it down into  pieces troubleshooting and found that a Continuation character group (' _') had somehow disappeared from the last SQL statement being built in the SUB.  The partial text developed caused the compilation error.  Restoration of the continuation restored the correct operation of the SUB.  Odd that the compiler could not recognize the END SUB that remained as always as the last statement of the SUB.  I wouldn't construct my compiler to work in the way that it does :(

RunningManHD, I have carefully looked at your comments.  I think that the DIM of a list of variables is a legitimate syntax.  I submit as reference the following URL:

 https://msdn.microsoft.com/en-us/library/7ee5a7s1.aspx

The long names have troubled me but if someone other than myself looks at the code, I would want them to be able to follow what I have done.  The Camel-Case notation  is an alternate method .  In my system of notation the underscores purpose is to keep each variable a single text string.

Thanks.

Michael

Answer 4

VBA produces a compile error which says that I have code after an END SUB statement.  The problem is that the code was running well to this point.  Tried to move a DIM of the variable dbs to a Module from a Form's Command Button Click Event and that's when the compile errors started happening.  Restore my dbs SET statement but the compile error persists.

The early part of the VBA code is listed here: ( If more is needed I can post that too.)

Private Sub Add_Another_Group_Click()

'Dimension String variables to build SQL Statement for Selection

    Dim sql_construct_element, SqlStr As String

   

'Dimension dbs Database reference name and set value

     Dim dbs As Object

      Set dbs = CurrentDb

       

'Dimension SQL String to Get Count of Records Selected

    Dim sql_count_statment As String

   

'Increment the Group_Number for this Group Definition Try

    'Dim Group_Number As Integer

    Group_Number = Group_Number + 1

' Dimension New Group Table Name variable and establish value

    'Dim Group_Table_Name As String

    Group_Table_Name = "Group" & Group_Number

   

' Dimension variable to contain count of records in Group

    Dim Group_Count As Integer

   

'Create a string variable to produce the  quotation mark.

    Dim strquot_mark As String

    strquot_mark = Chr$(34)

   

'Dimension Check Search Variables

    Dim Search_Municipal, Search_Legislator, Search_Government_Official, Search_ILC_Check As Integer

Hope something pops out here.

Its not the name of the Command Button because I tried it with spaces instead of underscores. 

Michael

We can't tell you what's wrong with your procedure unless you provide the entire procedure from beginning to end.  You most likely have an If block or a Select Case block without the closing statement, End If or End Select. 

I can tell you that your variable declarations are poorly defined.  Take the following for instance:

Dim Search_Municipal, Search_Legislator, Search_Government_Official, Search_ILC_Check As Integer

The following variables are all defined as variant because you did not explicitly state what each variable should be.

• Search_Municipal
• Search_Legislator
• Search_Government_Official

Your naming is also a bit too lengthy and impossible to determine the variable type without looking to the declaration.  If your intention is to declare these variables as variant, then the naming should be something more like the following:

• varSrchMunicipal
• varSrchLegislator
• varSrchGovOfficial

Try to use underscores as little as possible.  Instead, use Camel-Case notation, where each word or abbreviation is capitalized, but all words are joined together, just as shown above. 

Keep in mind that the longer each variable or object name is, the more you have to type and read.  The more you have to type, the greater the possibility of errors.  The more you have to read, the more difficult it can become to spot errors.

Answer 5

Well, the error clearly says that the problem is not at the beginning of the code, but after the End Sub statement. What's at the bottom of this block of code? IS there something after the End Sub line?