Expected end with ошибка

In this Article

This tutorial will explain what a VBA Compile Error means and how it occurs.

Before running your code, the VBA Editor compiles the code. This basically means that VBA examines your code to make sure that all the requirements are there to run it correctly – it will check that all the variables are declared (if you use Option Explicit which you should!), check that all the procedures are declared, check the loops and if statements etc. By compiling the code, VBA helps to minimize any runtime errors occurring.

(See our Error Handling Guide for more information about VBA Errors)

Undeclared Variables

If you do not declare variables, but your Option Explicit is switched on at the top of your module, and then you run the macro, a compile error will occur.

If you click OK, the relevant procedure will go into debug mode.

Alternatively, before you run your code, you can force a compilation of the code.

In the Menu, select Debug > Compile Project.

The compiler will find any compile errors and highlight the first one it finds accordingly.

Undeclared Procedures

If you code refers to a procedure that does not exist, you will also get a compile error.

However, if the procedure – NextProcedure does not exist, then a compile error will occur.

Incorrect Coding – Expected End of Statement

If you create a loop using For..Each..Next or With..End With and forget to and the Next or the End With… you will also get a compile error.

The same will happen with an If statement if the End If is omitted!

Missing References

If you are using an Object Library that is not part of Excel, but you are using the objects from the library in your variable declaration, you will also receive a compile error.

This can be solved by either Late Binding – declaring the variables are Objects; or by adding the relevant Object Library to the Project.

In the Menu, select Tools > References and add the relevant object library to your project.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

Compile Error: Expected End Sub

jzkemler1

New Member

Hi!!
Prefaced with I am a novice. But I am trying to get the macro below to work in Excel and I keep getting the Compile Error: Expected End Sub error. Can anyone with a trained set of eyes help me out? Thanks so much!! Zoe

Sub ctrln()
‘
‘ ctrln Macro
‘
‘ Keyboard Shortcut: Ctrl+n
‘
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(«G34»)) Is Nothing Then
With Target
If IsNumeric(.Value) And Not IsEmpty(.Value) Then
Range(«I34»).Value = Range(«I34»).Value + Range («G34»).Value
End If
End With
End If
End Sub

Excel Facts

MrExcel MVP

Welcome to the board.

Remove the lines that appear above Private Sub .

jasonb75

Well-known Member

You have part of what looks like a recorded macro followed by an event procedure.

Marcelo Branco

MrExcel MVP

As shg said you must remove the lines above Private Sub. but you need also put this code on a worksheet page-code, not in a Standard Module.

This is an event procedure, ie, it runs when something happens — in your case when G34 changes.

jzkemler1

New Member

I think perhaps I am going about this the wrong way entirely. I do want a macro that can be run when a user designates and not an event running everytime g34 is changed.

I am hoping to create a macro that will add g34 to i34- displaying the sum in cell i34, then revert the contents of g34 back to 0 while keeping the addition of the original value in the i34 cell.

So for example g34 would contain 100 and i34 would contain 660, the macro would be run and g34 would then contain 0 and i34 would contain 760. Then 14 would be entered into g34 and the macro run again and g34 would then contain 0 and i34 would contain 774.

Источник

Thread: Compile error: Expected end of statement

Thread Tools

Display

Compile error: Expected end of statement

I need your help. I have a problem with the code that I am using. I get the same errors in some line which I am using vb.net code, but I believes that it can be convert to vb. I am using httpwebrequest method, who let me to make a connect to the proxy server and then connect to the website.

However, here is the one that I uses on vb.net:

I have got an error for each line:

Each line I get the same error which it is: Compile error: Expected end of statement.

I have tried to fix them myself, but I couldn’t.

Please can you help me?

Re: Compile error: Expected end of statement

Wait. you’re trying to take some VB.NET code and use it in VB6? Ummm. it doesn’t work like that. that’s why you are getting the errors. for all intents and purposes, they are two different languages.. different syntax.

for instance. with VB6, you set strings like this:

That being said. the objects between the two are also different.

Re: Compile error: Expected end of statement

Thanks for your help tg, I have made some of the changes that you suggests in your post. However, what can I do with those on below?

Re: Compile error: Expected end of statement

Re: Compile error: Expected end of statement

None of that code is compatible with VB6, and there are no equivalents for those objects.

What you should do is find VB6 code that does what you want, because even if it is less than ideal you will spend far less time getting it working.

Re: Compile error: Expected end of statement

streamreader didn’t exist in vb6 so you’ll need a replacement.

try using simple
open «a.txt» for output as #1
print #1, «my text»
close #1

Also i don’t think try and catch statements were available back then either.
replace them with

on error goto err_trap

err_trap:
debug.print err.description

Experience with .Net:

Open:»Ooo, this is pretty»
Few minutes later:»Wow lots of useful Controls»
After an hour or so:»Hmm. seem to be doing very little programming here»
Some time later:»WHAT HAVE THEY DONE WITH MY CONTROL ARRAYS. »
vb6:Ahh.

Re: Compile error: Expected end of statement

Ok, so what can I do with the code on below that each of them have got the same error in each line, which the error is: Compile error user-defined type not defined??

The error are jumping in each of those line:

And on this one I have got, I got another compile error which the error is: Sub or Function not defined.

The error are jumping in that line:

Here’s the update code:

Hope you can help me with this

streamreader didn’t exist in vb6 so you’ll need a replacement.

try using simple
open «a.txt» for output as #1
print #1, «my text»
close #1

Also i don’t think try and catch statements were available back then either.
replace them with

on error goto err_trap

err_trap:
debug.print err.description

Re: Compile error: Expected end of statement

In order to get the code to work in VB6 you will need to do several things, including finding out what each thing does, finding out VB6 code that is roughly equivalent for each line, and finding equivalents for a few of those classes.

Even with assistance you are wasting your time, because just the last item by itself requires more time and effort than finding VB6 code to do the equivalent of the overall thing instead.

Re: Compile error: Expected end of statement

I told you. Trys weren’t around then. Use error trapping instead.
No streamreader/writers existed then.
The http classes you have referenced didn’t exist either. You’ll need to make/find substitutes.

Your «updated» code hasn’t taken on anything I told you, that is why you are still having problems.

I’d suggest just using visual studio. Unless you are familiar with vb6 and like it’s functions (like control arrays (which can be made in .net anyway, just takes a lot more work)) then stick with the one you know best.

Experience with .Net:

Open:»Ooo, this is pretty»
Few minutes later:»Wow lots of useful Controls»
After an hour or so:»Hmm. seem to be doing very little programming here»
Some time later:»WHAT HAVE THEY DONE WITH MY CONTROL ARRAYS. »
vb6:Ahh.

Источник

1. 02-13-2013, 02:16 PM

   #1

   

   
   Registered User
   

   

   ---

   Compile Error: Expected End With
   

   Hi,

   I have been recording 3 separate Marco’s in excel and now would like them to become one.

   The separate Marco’s are:-
   1) To format a header, and copy an “If” statement in to cell K4
   2) To fill the copied information in K4 down to a dynamic range
   3) To conditional format the rows that have «Yes» in the column K.

   The script is below, but I get the error «Compile Error: Expected End With»
   I have tried many things but I can’t seem to join these Marcos together.

   I hope someone can help me with this, as this s is driving me crazy

   Thanks in advance
   Rob

   Last edited by Robert110; 02-14-2013 at 06:37 AM.

   ---

2. 02-13-2013, 02:20 PM

   #2

   Re: Compile Error: Expected End With
   

   Rob

   It’s hard to tell where the problem is without seeing the code in tags.

   Could you edit and add them?

   If posting code please use code tags, see here.

   ---

3. 02-13-2013, 02:23 PM

   #3

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Expected End With
   

   ---

4. 02-13-2013, 02:25 PM

   #4

   Re: Compile Error: Expected End With
   

   Rob

   You can (should?) add code tags so that it makes the code easier to read/copy.

   The tags will preserve any indentation and will make the code stand out from the rest of the post.

   To add tags, select the code and press the # button in the toolbar above the reply box.

   PS Try sticking End With just before End Sub.

   ---

5. 02-13-2013, 02:38 PM

   #5

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Expected End With
   

   Ok Norie, Thanks for telling me about the coda posting ,

   Sorry I didn’t know about the cods tag’s I will do that in future.

   I did try «End With» but it still errored.

   I am working on a different computer at the min and don’t have the code here with me. so I had to copy and paste the code from Excel to and e-mail and then post it on this thread
   But if it helps I can get the code from excel tomorrow and post it in a code format.

   Rob

   ---

6. 02-13-2013, 02:42 PM

   #6

   Re: Compile Error: Expected End With
   

   Rob

   I might have missed something — it is hard to read the code without the tags.

   PS You can edit your original post to add the code tags.

   ---

7. 02-13-2013, 02:50 PM

   #7

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Expected End With
   

   Norie,

   Thanks for the tip, I will edit my first post. Once I get on the other computer.
   Thanks very much for helping me

   Rob

   ---

8. 02-14-2013, 06:39 AM

   #8

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Expected End With
   

   Hi Norie,

   I have added the code in the correct format to my first message. I hope this makes it a bit clearer now.

   Thanks again for your help

   Rob

   ---

9. 02-14-2013, 06:48 AM

   #9

   Re: Compile Error: Expected End With
   

   Rob

   That does help, the answer is still the same though — all you need to do is add End With just before End Sub.

   ---

10. 02-14-2013, 07:14 AM

    #10

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Expected End With
    

    Thank you so much Norie, that worked perfectly.

    To help me understand, why did I need an «End With» there, why wouldn’t an End Sub just do?

    Thanks again, this was driving me crazy:-)

    ---

While executing the code I got the following error , «Expect End With » . It could be great , if help is provided .

I have created the code below that compare two spreadsheets . It initially compares the range of value from sheet1 («Status») to sheet2 («Interface») . Whenever a range matches with the ranges present in other sheet , it does nothing .When the range does not any matches in the other sheet , then the entire record is copied from sheet2 to sheet1 . I have around 1500 rows of data in both sheets with 15 columns .

Function UpdateNEW2() As Long
    Const Delimiter As String = "|"
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    Dim newRows As Range
    Dim vSteps, key
    With Sheets("Steps")
        vSteps = .Range("A2:C2", .Cells(.Rows.Count, 1).End(xlUp)).Value
    End With

        Dim r As Long, c As Long, n As Long
        For r = 1 To UBound(vSteps)
            key = vSteps(r, 2) & Delimiter & vSteps(r, 2)
            If Not dic.Exists(key) Then dic.Add key, 0
        Next

        Dim vInterface, results
        With Sheets("Interface")
            vInterface = .Range("A2:O2", .Cells(.Rows.Count, "C").End(xlUp)).Value
        End With

        ReDim results(1 To UBound(vInterface), 1 To 15)
        For r = 1 To UBound(vInterface)
            key = vInterface(r, 5) & Delimiter & vInterface(r, 5)
            If Not dic.Exists(key) Then
                n = n + 1
                For c = 3 To 15
                    results(n, c - 2) = vInterface(r, c)
                Next
            End If
        Next
        With Sheets("Steps")
        With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                .Resize(n, 15).Value = results
                  End With

              UpdateNEW2 = n

    End Function

Return to VBA Code Examples

This tutorial will explain what a VBA Compile Error means and how it occurs.

Before running your code, the VBA Editor compiles the code. This basically means that VBA examines your code to make sure that all the requirements are there to run it correctly – it will check that all the variables are declared (if you use Option Explicit which you should!), check that all the procedures are declared, check the loops and if statements etc. By compiling the code, VBA helps to minimize any runtime errors occurring.

(See our Error Handling Guide for more information about VBA Errors)

Undeclared Variables

If you do not declare variables, but your Option Explicit is switched on at the top of your module, and then you run the macro, a compile error will occur.

If you click OK,  the relevant procedure will go into debug mode.

Alternatively, before you run your code, you can force a compilation of the code.

In the Menu, select Debug > Compile Project.

The compiler will find any compile errors and highlight the first one it finds accordingly.

Undeclared Procedures

If you code refers to a procedure that does not exist, you will also get a compile error.

For example:

Sub CallProcedure()
'some code here then 
  Call NextProcedure
End Sub

However, if the procedure – NextProcedure does not exist, then a compile error will occur.

Incorrect Coding – Expected End of Statement

If you create a loop using For..Each..Next or With..End With and forget to and the Next or the End With… you will also get a compile error.

Sub CompileError()
 Dim wb As Workbook
 Dim ws As Worksheet
 For Each ws In wb
   MsgBox ws.Name
End Sub

The same will happen with an If statement if the End If is omitted!

Missing References

If you are using an Object Library that is not part of Excel, but you are using the objects from the library in your variable declaration, you will also receive a compile error.

This can be solved by either Late Binding – declaring the variables are Objects; or by adding the relevant Object Library to the Project.

In the Menu, select Tools > References and add the relevant object library to your project.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112

Private Sub CommandButton1_Click() 'Декларация переменных Dim fam, pred, exam, ocenk, resault, grup, semestr As String Dim summa As Single Dim nomer As Integer Dim data As Date   nomer = Application.CountA(ActiveSheet.Columns(1)) + 1 With UserForm1   If [Фамилия] = "" Then MsgBox "", vbExclamation Exit Sub End If   If [Группа] = "" Then MsgBox "", vbExclamation Exit Sub End If   If ComboBox1.Text = "" Then MsgBox "", vbExclamation Exit Sub End If   If ComboBox2.Text = "" Then MsgBox "", vbExclamation Exit Sub End If   If ComboBox3.Text = "" Then MsgBox "", vbExclamation Exit Sub End If   If ComboBox4.Text = "" Then MsgBox "", vbExclamation Exit Sub End If   If ComboBox4.Text = "" Then MsgBox "", vbExclamation Exit Sub End If     fam = [Фамилия] grup = [Группа] pred = ComboBox1.Value ocenk = ComboBox2.Value resault = ComboBox3.Value exam = ComboBox4.Value semestr = ComboBox5.Value     If IsDate([Дата]) = False Then MsgBox "", vbExclamation Exit Sub End If   data = [Дата]   With ActiveSheet .Cells(nomer, 1).Value = fam .Cells(nomer, 2).Value = pred .Cells(nomer, 3).Value = exam .Cells(nomer, 4).Value = ocenk .Cells(nomer, 5).Value = resault .Cells(nomer, 6).Value = grup .Cells(nomer, 7).Value = semestr .Cells(nomer, 8).Value = summa .Cells(nomer, 9).Value = nomer .Cells(nomer, 10).Value = data End With ClearForm End Sub   Private Sub ClearForm() Unload UserForm1 UserForm1.Show End Sub     Private Sub UserForm_Initialize() ComboBox1.AddItem "Информатика" ComboBox1.AddItem "Высшая  математика" ComboBox1.AddItem "Физика" ComboBox1.AddItem "История" ComboBox1.AddItem "Психология" ComboBox1.AddItem "Физическое воспитание" ComboBox1.AddItem "Иследовательская работа" ComboBox2.AddItem "Отлично" ComboBox2.AddItem "Хорошо" ComboBox2.AddItem "Удовлетворительно" ComboBox2.AddItem "Не сдал" ComboBox2.AddItem "Зачет" ComboBox2.AddItem "Незачет" ComboBox3.AddItem "сессия  закрыта  с  обычной  стипендией" ComboBox3.AddItem "закрыта  с  повышенной стипендией" ComboBox3.AddItem "закрыта  с  повышенной стипендией" ComboBox4.AddItem "Экзамен" ComboBox4.AddItem "Зачет" ComboBox5.AddItem "I" ComboBox5.AddItem "II" ComboBox5.AddItem "III" ComboBox5.AddItem "IV" ComboBox5.AddItem "V" ComboBox5.AddItem "VI" ComboBox5.AddItem "VIII" ComboBox5.AddItem "IX" ComboBox5.AddItem "X" End Sub

Good Afternoon r/excel,

I keep getting the same error with this macro designed to split out a single cell of delimited data into multiple rows:

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = "*"
  Const DelimitedColumn As String = "B"
  Const TableColumns As String = "A:B"
  Const StartRow As Long = 2
  Application.ScreenUpdating = False
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) > 0 Then
  Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
    End If
    If Len(Cells(X, DelimitedColumn)) Then
      Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
    End If
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error Resume Next
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  If Err.Number = 0 Then
    Table.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    Columns(DelimitedColumn).SpecialCells(xlFormulas).Clear
    Table.Value = Table.Value
  End If
  On Error GoTo 0

The debugger is pointing to the first line as the area with the error, with the message «Compile Error: Expected End Sub»

Any help here would be awesome!

Thanks very much

pavlusha15 Пользователь Сообщений: 4 Регистрация: 25.06.2017	#1 25.06.2017 22:09:12 Дело в том, что на одном листе создана кнопка и к нему привязан модуль с макросом, я хочу переместить кнопку в форму, но вылазит ошибка «Expected End Sub» Код Sub Show_Form() Reports.Show 'отображаем форму отчета End Sub Изменено: pavlusha15 — 25.06.2017 22:44:48

Hugo Пользователь Сообщений: 23134 Регистрация: 22.12.2012	Где-то в форме недописано окончание макроса.

pavlusha15 Пользователь Сообщений: 4 Регистрация: 25.06.2017	#3 25.06.2017 22:20:28 Цитата Hugo написал: Где-то в форме недописано окончание макроса Хм, ну где именно?

pavlusha15 Пользователь Сообщений: 4 Регистрация: 25.06.2017	#4 25.06.2017 22:21:23 Код Private Sub CommandButton8_Click() Sub Show_Form() Reports.Show End Sub Когда поставил в кнопку, вот так Изменено: pavlusha15 — 25.06.2017 22:21:41

Ігор Гончаренко Пользователь Сообщений: 13254 Регистрация: 01.01.1970	#5 25.06.2017 22:23:30 а так: Код Private Sub CommandButton8_Click()   Show_Form End Sub или Код Private Sub CommandButton8_Click() Reports.Show End Sub как Вам удобно Изменено: Ігор Гончаренко — 25.06.2017 22:24:03 Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Hugo Пользователь Сообщений: 23134 Регистрация: 22.12.2012	#6 25.06.2017 22:33:32 Цитата pavlusha15 написал: Хм, ну где именно? — хотелось ответить, но так отвечать нельзя Но после следующего поста отвечаю — ну неужели не видно? Должно быть как-то так, если не нравится то, что выше сказал Игорь: Код Private Sub CommandButton8_Click() 'тут можно что-то сделать, например вызвать другой макрос как в примере выше End Sub Sub Show_Form() Reports.Show End Sub

pavlusha15 Пользователь Сообщений: 4 Регистрация: 25.06.2017	Я просто новичок в этом, спасибо вам большое!

Юрий М Модератор Сообщений: 60383 Регистрация: 14.09.2012 Контакты см. в профиле	pavlusha15, название темы у Вас никудышное. Предложите новое — модераторы поменяют. Стоит только отойти на минутку — Игори тут, как тут )

JayBhagavan Пользователь Сообщений: 11833 Регистрация: 17.01.2014 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64	Юрий М, может так сгодится: Прошу помочь найти причину ошибки «Expected End Sub» <#0> Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori

Юрий М Модератор Сообщений: 60383 Регистрация: 14.09.2012 Контакты см. в профиле

Ігор Гончаренко Пользователь Сообщений: 13254 Регистрация: 01.01.1970	#11 25.06.2017 22:58:00 в каком-то из модулей количество Sub больше, чем количество End Sub Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!