BMSs Пользователь Сообщений: 19 |
#1 10.04.2018 18:17:08
|
||
Hugo Пользователь Сообщений: 23371 |
В 22-ю строку добавьте то, о чём Вам говорит отладчик. |
RAN Пользователь Сообщений: 7139 |
Берем лупу, и считаем количество With и End With. PS Но какой-то странный Ёжуж (или Ужёж) Изменено: RAN — 10.04.2018 18:24:37 |
BMSs Пользователь Сообщений: 19 |
Добавил, макрос выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку |
Ігор Гончаренко Пользователь Сообщений: 13859 |
#5 10.04.2018 19:32:03
закомментируйте 3, 4 и 5 строки Вашего кода Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете! |
||
BMSs Пользователь Сообщений: 19 |
#6 10.04.2018 21:16:00
|
||
Юрий М Модератор Сообщений: 60761 Контакты см. в профиле |
Не писал такого Виктор ) |
BMSs Пользователь Сообщений: 19 |
Суть в появлении ошибки при запуске макроса, макрос в студии, куда еще короче? |
BMSs Пользователь Сообщений: 19 |
Вопрос закрыт, всем спасибо! |
vikttur Пользователь Сообщений: 47199 |
Поделиться решением забыли. |
BMSs Пользователь Сообщений: 19 |
#11 12.04.2018 08:30:03
|
||
BMSs Пользователь Сообщений: 19 |
#12 12.04.2018 08:30:42 Изменены строки с 23 по 27. |
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 |
Short version
Add spaces between operators and their operands. Problem solved.
Long version
Any identifier that is immediately followed by a &
, like name&
and affiliation&
, is interpreted as a Long
variable, so the lack of whitespace in front of what’s meant to be concatenation operators is causing a parse error, because VBA doesn’t know what literal expression could possibly follow the Combined = name&
assignment — the instruction is complete as it is; the only token that should be where " ("
is, is an end-of-statement token:
Expected: end of statement
Says exactly that. Everything before " ("
is a perfectly valid instruction, except it’s not terminated.
So it’s not the " ("
, it’s the type hints. Insert spaces to separate the operators from the operands, and you’ll fix the problem. More explicitness couldn’t hurt, either:
Option Explicit
Public Function Combined(ByVal name As String, ByVal affiliation As String, email As String) As String
Combined = name & " (" & affiliation & ") " & "<" & email & ">"
End Function
When a type isn’t specified, a declaration is implicitly Variant
, which incurs some unnecessary run-time overhead.
When ByVal
isn’t specified, parameters are passed ByRef
by default, which means the function could be assigning to the parameters.
You could also implement the function like so:
Combined = Join(Array(name, "(" & affiliation & ")", "<" & email & ">"), " ")
BMSs Пользователь Сообщений: 19 |
#1 10.04.2018 18:17:08
|
||
Hugo Пользователь Сообщений: 23100 |
В 22-ю строку добавьте то, о чём Вам говорит отладчик. |
RAN Пользователь Сообщений: 7081 |
Берем лупу, и считаем количество With и End With. PS Но какой-то странный Ёжуж (или Ужёж) Изменено: RAN — 10.04.2018 18:24:37 |
BMSs Пользователь Сообщений: 19 |
Добавил, макрос выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку |
Ігор Гончаренко Пользователь Сообщений: 13125 |
#5 10.04.2018 19:32:03
закомментируйте 3, 4 и 5 строки Вашего кода Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете! |
||
BMSs Пользователь Сообщений: 19 |
#6 10.04.2018 21:16:00
|
||
Юрий М Модератор Сообщений: 60335 Контакты см. в профиле |
Не писал такого Виктор ) |
BMSs Пользователь Сообщений: 19 |
Суть в появлении ошибки при запуске макроса, макрос в студии, куда еще короче? |
BMSs Пользователь Сообщений: 19 |
Вопрос закрыт, всем спасибо! |
vikttur Пользователь Сообщений: 47199 |
Поделиться решением забыли. |
BMSs Пользователь Сообщений: 19 |
#11 12.04.2018 08:30:03
|
||
BMSs Пользователь Сообщений: 19 |
#12 12.04.2018 08:30:42 Изменены строки с 23 по 27. |
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 |
Not only should there be at least one End With, there shouldn’t be so many With statements.
Sub RowRangeMove()
Sheets.Add().Name = "CopySheet"
With Sheets("BigDataSet - Copy")
.Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
.Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
.Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
.Range("D65000", .Range("D13000").End(xlUp)).Copy Destination:=Range("D1")
.Range("E65000", .Range("E13000").End(xlUp)).Copy Destination:=Range("E1")
.Range("F65000", .Range("F13000").End(xlUp)).Copy Destination:=Range("F1")
.Range("G65000", .Range("G13000").End(xlUp)).Copy Destination:=Range("G1")
.Range("H65000", .Range("H13000").End(xlUp)).Copy Destination:=Range("H1")
.Range("I65000", .Range("I13000").End(xlUp)).Copy Destination:=Range("I1")
.Range("J65000", .Range("J13000").End(xlUp)).Copy Destination:=Range("J1")
End With
End Sub
would be the correct syntax.
The With statement is simply a way to shorten up your lines of code. The With statement is a way of saying «I’m going to perform a bunch of actions on a specific object» and shorten up the individual lines of code.
Example:
Without the With statement, the code above would look like this:
Sub RowRangeMove()
Sheets.Add().Name = "CopySheet"
Sheets("BigDataSet - Copy").Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
Sheets("BigDataSet - Copy").Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
Sheets("BigDataSet - Copy").Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
Sheets("BigDataSet - Copy").Range("D65000", .Range("D13000").End(xlUp)).Copy Destination:=Range("D1")
Sheets("BigDataSet - Copy").Range("E65000", .Range("E13000").End(xlUp)).Copy Destination:=Range("E1")
Sheets("BigDataSet - Copy").Range("F65000", .Range("F13000").End(xlUp)).Copy Destination:=Range("F1")
Sheets("BigDataSet - Copy").Range("G65000", .Range("G13000").End(xlUp)).Copy Destination:=Range("G1")
Sheets("BigDataSet - Copy").Range("H65000", .Range("H13000").End(xlUp)).Copy Destination:=Range("H1")
Sheets("BigDataSet - Copy").Range("I65000", .Range("I13000").End(xlUp)).Copy Destination:=Range("I1")
Sheets("BigDataSet - Copy").Range("J65000", .Range("J13000").End(xlUp)).Copy Destination:=Range("J1")
End Sub
In shorter terms, the With statement allows you to start out individual lines of code with a dot, and inside that with statement, the compiler will assume you mean the thing declared in your with statement.
So
With Answerer
' Inside this with block, any line beginning with "." ,
' the compiler will assume you mean "Answerer.".
' Therefore ".FirstName" is the same as "Answerer.FirstName"
.FirstName = "David"
.LastName = "Stratton"
End With
is equivalient to
Answerer.FirstName = "David"
Amswerer.LastName = "Stratton"
-
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
RobLast edited by Robert110; 02-14-2013 at 06:37 AM.
-
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.
-
02-13-2013, 02:23 PM
#3
Registered User
Re: Compile Error: Expected End With
-
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.
-
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
-
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.
-
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 meRob
-
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
-
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.
-
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:-)
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!
FreeBASIC
Compiler Error Messages
During the program compilation three types of errors can arise:
Compiler Warnings:
The warnings don’t stop the compilation, just alert the user some non-recommended and error-prone operation is attempted in the code.
Sometimes one of these operations is coded deliberately to achieve a result, in this case the warnings can be disabled by setting the -w 1 option at the command line.
- 1 Passing scalar as pointer
- 2 Passing pointer to scalar
- 3 Passing different pointer types
- 4 Suspicious pointer assignment
- 5 Implicit conversion
- 6 Cannot export symbol without -export option
- 7 Identifier’s name too big, truncated
- 8 Literal number too big, truncated
- 9 Literal string too big, truncated
- 10 UDT with pointer or var-len string fields
- 11 Implicit variable allocation
- 12 Missing closing quote in literal string
- 13 Function result was not explicitly set
- 14 Branch crossing local variable definition
- 15 No explicit BYREF or BYVAL
- 16 Possible escape sequence found in
- 17 The type length is too large, consider passing BYREF
- 18 The length of the parameters list is too large, consider passing UDT’s BYREF
- 19 The ANY initializer has no effect on UDT’s with default constructors
- 20 Object files or libraries with mixed multithreading (-mt) options
- 21 Object files or libraries with mixed language (-lang) options
- 22 Deleting ANY pointers is undefined
- 23 Array too large for stack, consider making it var-len or SHARED
- 24 Variable too large for stack, consider making it SHARED
- 25 Overflow in constant conversion
- 26 Variable following NEXT is meaningless
- 27 Cast to non-pointer
- 28 Return method mismatch
- 29 Passing Pointer
- 30 Command line option overrides directive
- 31 Directive ignored after first pass
- 32 ‘IF’ statement found directly after multi-line ‘ELSE’
- 33 Shift value greater than or equal to number of bits in data type
- 34 ‘=’ parsed as equality operator in function argument, not assignment to BYREF function result
- 35 Mixing signed/unsigned operands
- 36 Mismatching parameter initializer
- 37
- 38 Mixing operand data types may have undefined results
- 39 Redefinition of intrinsic
Compiler Error messages:
The error messages stop the compilation after 10 errors (see the -maxerr command-line option to change that default value) or a fatal error occurred, and require a correction by the user before the compilation can be continued. The compiler signals the lines where the errors have been found, so the correction can be done quickly. In a few cases the place pointed at by the error messages is not where the errors can be found, it’s the place where the compiler has given up in waiting for something that should be somewhere.
- 1 Argument count mismatch
- 2 Expected End-of-File
- 3 Expected End-of-Line
- 4 Duplicated definition
- 5 Expected ‘AS’
- 6 Expected ‘(‘
- 7 Expected ‘)’
- 8 Undefined symbol
- 9 Expected expression
- 10 Expected ‘=’
- 11 Expected constant
- 12 Expected ‘TO’
- 13 Expected ‘NEXT’
- 14 Expected identifier
- 15 Expected ‘-‘
- 16 Expected ‘,’
- 17 Syntax error
- 18 Element not defined
- 19 Expected ‘END TYPE’ or ‘END UNION’
- 20 Type mismatch
- 21 Internal!
- 22 Parameter type mismatch
- 23 File not found
- 24 Invalid data types
- 25 Invalid character
- 26 File access error
- 27 Recursion level too deep
- 28 Expected pointer
- 29 Expected ‘LOOP’
- 30 Expected ‘WEND’
- 31 Expected ‘THEN’
- 32 Expected ‘END IF’
- 33 Illegal ‘END’
- 34 Expected ‘CASE’
- 35 Expected ‘END SELECT’
- 36 Wrong number of dimensions
- 37 Array boundaries do not match the original EXTERN declaration
- 38 ‘SUB’ or ‘FUNCTION’ without ‘END SUB’ or ‘END FUNCTION’
- 39 Expected ‘END SUB’ or ‘END FUNCTION’
- 40 Illegal parameter specification
- 41 Variable not declared
- 42 Variable required
- 43 Illegal outside a compound statement
- 44 Expected ‘END ASM’
- 45 Function not declared
- 46 Expected ‘;’
- 47 Undefined label
- 48 Too many array dimensions
- 49 Array too big
- 50 User Defined Type too big
- 51 Expected scalar counter
- 52 Illegal outside a CONSTRUCTOR, DESTRUCTOR, FUNCTION, OPERATOR, PROPERTY or SUB block
- 53 Expected var-len array
- 54 Fixed-len strings cannot be returned from functions
- 55 Array already dimensioned
- 56 Illegal without the -ex option
- 57 Type mismatch
- 58 Illegal specification
- 59 Expected ‘END WITH’
- 60 Illegal inside functions
- 61 Statement in between SELECT and first CASE
- 62 Expected array
- 63 Expected ‘{‘
- 64 Expected ‘}’
- 65 Expected ‘]’
- 66 Too many expressions
- 67 Expected explicit result type
- 68 Range too large
- 69 Forward references not allowed
- 70 Incomplete type
- 71 Array not dimensioned
- 72 Array access, index expected
- 73 Expected ‘END ENUM’
- 74 Var-len arrays cannot be initialized
- 75 ‘…’ ellipsis upper bound given for dynamic array (this is not supported)
- 76 ‘…’ ellipsis upper bound given for array field (this is not supported)
- 77 Invalid bitfield
- 78 Too many parameters
- 79 Macro text too long
- 80 Invalid command-line option
- 81 Selected non-x86 CPU when compiling for DOS
- 82 Selected -gen gas ASM backend for non-x86 CPU
- 83 -asm att used for -gen gas, but -gen gas only supports -asm intel
- 84 -pic used when making executable (only works when making a shared library)
- 85 -pic used, but not supported by target system (only works for non-x86 Unixes)
- 86 Var-len strings cannot be initialized
- 87 Recursive TYPE or UNION not allowed
- 88 Recursive DEFINE not allowed
- 89 Identifier cannot include periods
- 90 Executable not found
- 91 Array out-of-bounds
- 92 Missing command-line option for
- 93 Expected ‘ANY’
- 94 Expected ‘END SCOPE’
- 95 Illegal inside a compound statement or scoped block
- 96 UDT function results cannot be passed by reference
- 97 Ambiguous call to overloaded function
- 98 No matching overloaded function
- 99 Division by zero
- 100 Cannot pop stack, underflow
- 101 UDT’s containing var-len string fields cannot be initialized
- 102 Branching to scope block containing local variables
- 103 Branching to other functions or to module-level
- 104 Branch crossing local array, var-len string or object definition
- 105 LOOP without DO
- 106 NEXT without FOR
- 107 WEND without WHILE
- 108 END WITH without WITH
- 109 END IF without IF
- 110 END SELECT without SELECT
- 111 END SUB or FUNCTION without SUB or FUNCTION
- 112 END SCOPE without SCOPE
- 113 END NAMESPACE without NAMESPACE
- 114 END EXTERN without EXTERN
- 115 ELSEIF without IF
- 116 ELSE without IF
- 117 CASE without SELECT
- 118 Cannot modify a constant
- 119 Expected period (‘.’)
- 120 Expected ‘END NAMESPACE’
- 121 Illegal inside a NAMESPACE block
- 122 Symbols defined inside namespaces cannot be removed
- 123 Expected ‘END EXTERN’
- 124 Expected ‘END SUB’
- 125 Expected ‘END FUNCTION’
- 126 Expected ‘END CONSTRUCTOR’
- 127 Expected ‘END DESTRUCTOR’
- 128 Expected ‘END OPERATOR’
- 129 Expected ‘END PROPERTY’
- 130 Declaration outside the original namespace
- 131 No end of multi-line comment, expected «‘/»
- 132 Too many errors, exiting
- 133 Expected ‘ENDMACRO’
- 134 EXTERN or COMMON variables cannot be initialized
- 135 EXTERN or COMMON dynamic arrays cannot have initial bounds
- 136 At least one parameter must be a user-defined type
- 137 Parameter or result must be a user-defined type
- 138 Both parameters can’t be of the same type
- 139 Parameter and result can’t be of the same type
- 140 Invalid result type for this operator
- 141 Invalid parameter type, it must be the same as the parent TYPE/CLASS
- 142 Vararg parameters are not allowed in overloaded functions
- 143 Illegal outside an OPERATOR block
- 144 Parameter cannot be optional
- 145 Only valid in -lang
- 146 Default types or suffixes are only valid in -lang
- 147 Suffixes are only valid in -lang
- 148 Implicit variables are only valid in -lang
- 149 Auto variables are only valid in -lang
- 150 Invalid array index
- 151 Operator must be a member function
- 152 Operator cannot be a member function
- 153 Method declared in anonymous UDT
- 154 Constant declared in anonymous UDT
- 155 Static variable declared in anonymous UDT
- 156 Expected operator
- 157 Declaration outside the original namespace or class
- 158 A destructor should not have any parameters
- 159 Expected class or UDT identifier
- 160 Var-len strings cannot be part of UNION’s or nested TYPE’s
- 161 Dynamic arrays cannot be part of UNION’s or nested TYPE’s
- 162 Fields with constructors cannot be part of UNION’s or nested TYPE’s
- 163 Fields with destructors cannot be part of UNION’s or nested TYPE’s
- 164 Illegal outside a CONSTRUCTOR block
- 165 Illegal outside a DESTRUCTOR block
- 166 UDT’s with methods must have unique names
- 167 Parent is not a class or UDT
- 168 CONSTRUCTOR() chain call not at top of constructor
- 169 BASE() initializer not at top of constructor
- 170 REDIM on UDT with non-CDECL constructor
- 171 REDIM on UDT with non-CDECL destructor
- 172 REDIM on UDT with non-parameterless default constructor
- 173 ERASE on UDT with non-CDECL constructor
- 174 ERASE on UDT with non-CDECL destructor
- 175 ERASE on UDT with non-parameterless default constructor
- 176 This symbol cannot be undefined
- 177 RETURN mixed with ‘FUNCTION =’ or EXIT FUNCTION (using both styles together is unsupported when returning objects with constructors)
- 178 ‘FUNCTION =’ or EXIT FUNCTION mixed with RETURN (using both styles together is unsupported when returning objects with constructors)
- 179 Missing RETURN to copy-construct function result
- 180 Invalid assignment/conversion
- 181 Invalid array subscript
- 182 TYPE or CLASS has no default constructor
- 183 Function result TYPE has no default constructor
- 184 Missing BASE() initializer (base UDT without default constructor requires manual initialization)
- 185 Missing default constructor implementation (base UDT without default constructor requires manual initialization)
- 186 Missing UDT.constructor(byref as UDT) implementation (base UDT without default constructor requires manual initialization)
- 187 Missing UDT.constructor(byref as const UDT) implementation (base UDT without default constructor requires manual initialization)
- 188 Invalid priority attribute
- 189 PROPERTY GET should have no parameter, or just one if indexed
- 190 PROPERTY SET should have one parameter, or just two if indexed
- 191 Expected ‘PROPERTY’
- 192 Illegal outside a PROPERTY block
- 193 PROPERTY has no GET method/accessor
- 194 PROPERTY has no SET method/accessor
- 195 PROPERTY has no indexed GET method/accessor
- 196 PROPERTY has no indexed SET method/accessor
- 197 Missing overloaded operator:
- 198 The NEW[] operator does not allow explicit calls to constructors
- 199 The NEW[] operator only supports the { ANY } initialization
- 200 The NEW operator cannot be used with fixed-length strings
- 201 Illegal member access
- 202 Expected ‘:’
- 203 The default constructor has no public access
- 204 Constructor has no public access
- 205 Destructor has no public access
- 206 Accessing base UDT’s private default constructor
- 207 Accessing base UDT’s private destructor
- 208 Illegal non-static member access
- 209 Constructor declared ABSTRACT
- 210 Constructor declared VIRTUAL
- 211 Destructor declared ABSTRACT
- 212 Member cannot be static
- 213 Member isn’t static
- 214 Only static members can be accessed from static functions
- 215 The PRIVATE and PUBLIC attributes are not allowed with REDIM, COMMON or EXTERN
- 216 STATIC used here, but not the in the DECLARE statement
- 217 CONST used here, but not the in the DECLARE statement
- 218 VIRTUAL used here, but not the in the DECLARE statement
- 219 ABSTRACT used here, but not the in the DECLARE statement
- 220 Method declared VIRTUAL, but UDT does not extend OBJECT
- 221 Method declared ABSTRACT, but UDT does not extend OBJECT
- 222 Not overriding any virtual method
- 223 Implemented body for an ABSTRACT method
- 224 Override has different return type than overridden method
- 225 Override has different calling convention than overridden method
- 226 Implicit destructor override would have different calling convention
- 227 Implicit LET operator override would have different calling convention
- 228 Override is not a CONST member like the overridden method
- 229 Override is a CONST member, but the overridden method is not
- 230 Override has different parameters than overridden method
- 231 This operator cannot be STATIC
- 232 This operator is implicitly STATIC and cannot be VIRTUAL or ABSTRACT
- 233 This operator is implicitly STATIC and cannot be CONST
- 234 Parameter must be an integer
- 235 Parameter must be a pointer
- 236 Expected initializer
- 237 Fields cannot be named as keywords in TYPE’s that contain member functions or in CLASS’es
- 238 Illegal outside a FOR compound statement
- 239 Illegal outside a DO compound statement
- 240 Illegal outside a WHILE compound statement
- 241 Illegal outside a SELECT compound statement
- 242 Expected ‘FOR’
- 243 Expected ‘DO’
- 244 Expected ‘WHILE’
- 245 Expected ‘SELECT’
- 246 No outer FOR compound statement found
- 247 No outer DO compound statement found
- 248 No outer WHILE compound statement found
- 249 No outer SELECT compound statement found
- 250 Expected ‘CONSTRUCTOR’, ‘DESTRUCTOR’, ‘DO’, ‘FOR’, ‘FUNCTION’, ‘OPERATOR’, ‘PROPERTY’, ‘SELECT’, ‘SUB’ or ‘WHILE’
- 251 Expected ‘DO’, ‘FOR’ or ‘WHILE’
- 252 Illegal outside a SUB block
- 253 Illegal outside a FUNCTION block
- 254 Ambiguous symbol access, explicit scope resolution required
- 255 An ENUM, TYPE or UNION cannot be empty
- 256 ENUM’s declared inside EXTERN .. END EXTERN blocks don’t open new scopes
- 257 STATIC used on non-member procedure
- 258 CONST used on non-member procedure
- 259 ABSTRACT used on non-member procedure
- 260 VIRTUAL used on non-member procedure
- 261 Invalid initializer
- 262 Objects with default [con|de]structors or methods are only allowed in the module level
- 263 Static member variable in nested UDT (only allowed in toplevel UDTs)
- 264 Symbol not a CLASS, ENUM, TYPE or UNION type
- 265 Too many elements
- 266 Only data members supported
- 267 UNIONs are not allowed
- 268 Arrays are not allowed
- 269 COMMON variables cannot be object instances of CLASS/TYPE’s with cons/destructors
- 270 Cloning operators (LET, Copy constructors) can’t take a byval arg of the parent’s type
- 271 Local symbols can’t be referenced
- 272 Expected ‘PTR’ or ‘POINTER’
- 273 Too many levels of pointer indirection
- 274 Dynamic arrays can’t be const
- 275 Const UDT cannot invoke non-const method
- 276 Elements must be empty for strings and arrays
- 277 GOSUB disabled, use ‘OPTION GOSUB’ to enable
- 278 Invalid -lang
- 279 Can’t use ANY as initializer in array with ellipsis bound
- 280 Must have initializer with array with ellipsis bound
- 281 Can’t use … as lower bound
- 282 FOR/NEXT variable name mismatch
- 283 Selected option requires an SSE FPU mode
- 284 Expected relational operator ( =, >, <, <>, <=, >= )
- 285 Unsupported statement in -gen gcc mode
- 286 Too many labels
- 287 Unsupported function
- 288 Expected sub
- 289 Expected ‘#ENDIF’
- 290 Resource file given for target system that does not support them
- 291 -o <file> option without corresponding input file
- 292 Not extending a TYPE/UNION (a TYPE/UNION can only extend other TYPEs/UNIONs)
- 293 Illegal outside a CLASS, TYPE or UNION method
- 294 CLASS, TYPE or UNION not derived
- 295 CLASS, TYPE or UNION has no constructor
- 296 Symbol type has no Run-Time Type Info (RTTI)
- 297 Types have no hierarchical relation
- 298 Expected a CLASS, TYPE or UNION symbol type
- 299 Casting derived UDT pointer from incompatible pointer type
- 300 Casting derived UDT pointer from unrelated UDT pointer type
- 301 Casting derived UDT pointer to incompatible pointer type
- 302 Casting derived UDT pointer to unrelated UDT pointer type
- 303 ALIAS name string is empty
- 304 LIB name string is empty
- 305 UDT has unimplemented abstract methods
- 306 Non-virtual call to ABSTRACT method
- 307 #ASSERT condition failed
- 308 Expected ‘>’
- 309 Invalid size
- 310 ALIAS name here is different from ALIAS given in DECLARE prototype
- 311 vararg parameters are only allowed in CDECL procedures
- 312 the first parameter in a procedure may not be vararg
- 313 CONST used on constructor (not needed)
- 314 CONST used on destructor (not needed)
- 315 Byref function result not set
- 316 Function result assignment outside of the function
- 317 Type mismatch in byref function result assignment
- 318 -asm att|intel option given, but not supported for this target (only x86 or x86_64)
- 319 Reference not initialized
- 320 Incompatible reference initializer
- 321 Array of references — not supported yet
- 322 Invalid CASE range, start value is greater than the end value
- 323 Fixed-length string combined with BYREF (not supported)
Third party programs errors
These errors occur after the source has been compiled into assembler, they come from the auxiliary programs FB requires to compile a source into an executable: the linker, the assembler and (for Windows programs) the resource compiler.
If an IDE or a make utility are been used, additional errors can arise. These errors are outside the scope of this help.
При выполнении кода я получил следующую ошибку «Ожидайте завершения с». Это может быть здорово, если помощь будет оказана.
Я создал код ниже, который сравнивает две таблицы. Первоначально сравнивается диапазон значений от sheet1 («Status») до sheet2 («Interface»). Всякий раз, когда диапазон совпадает с диапазонами, представленными на другом листе, он ничего не делает. Когда диапазон не совпадает на другом листе, вся запись копируется с листа2 на лист1. У меня есть около 1500 строк данных на обоих листах с 15 столбцами.
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
2 ответа
Лучший ответ
Option Explicit '<- Always use Option Explicit
Function UpdateNEW2() As Long
Const Delimiter As String = "|"
Dim dic As Object
Dim newRows As Range
Dim vSteps, key, vInterface, results '<- You could declare variables
Dim r As Long, c As Long, n As Long
Set dic = CreateObject("Scripting.Dictionary")
With ThisWorkbook '<- To avoid issues if two workbooks are open create a "With Statement" for this workbook
With .Sheets("Steps") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"
vSteps = .Range("A2:C2", .Cells(.Rows.Count, 1).End(xlUp)).Value
End With
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
With .Sheets("Interface") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"
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") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"
With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
.Resize(n, 15).Value = results
End With
UpdateNEW2 = n
End With
End With
End Function
1
Error 1004
17 Апр 2019 в 08:57
With Sheets("Steps")
With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
.Resize(n, 15).Value = results
End With
UpdateNEW2 = n
End with <-- you've forgotten this one.
Как видите, правильный отступ облегчает чтение и исправление в случае необходимости.
0
Dominique
17 Апр 2019 в 08:48
-
#3
You have part of what looks like a recorded macro followed by an event procedure.
You just need
Code:
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
not the part above it.
<!— / message —>
-
#4
Hi,
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.
For an excellent explanation take a look at
http://www.cpearson.com/excel/Events.aspx
M.
-
#7
Or, if you wish, you can use an event procedure like
Code:
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
Application.EnableEvents = False
With Target
If IsNumeric(.Value) Then
Range("I34").Value = Range("I34").Value + .Value
.Value = 0
End If
End With
Application.EnableEvents = True
End If
End Sub
M.
Joe4
MrExcel MVP, Junior Admin
-
#2
Welcome to the Board!
The error code is actually telling you exactly what your issue is. Every time you use a «With» statement, you need a corresponding «End With» statement to mark the end of the With statement. You have a «With», but no «End With». It looks like it should be right after your «.Apply» line.
-
#3
Im afraid that could never have run unless you deleted End With without noticing. Any its here its needed:
Code:
With ActiveWorkbook.Worksheets("March Other Sources").Sort
.SetRange Range("A4:AF5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
-
#4
Welcome to the Board!
The error code is actually telling you exactly what your issue is. Every time you use a «With» statement, you need a corresponding «End With» statement to mark the end of the With statement. You have a «With», but no «End With». It looks like it should be right after your «.Apply» line.
Thanks for the quick response! Knew I’d get the answer from here
Joe4
MrExcel MVP, Junior Admin
-
#5
You are welcome!
Be sure to pay special attention to the error messages that are returned. Many times, they are cryptic, and it is tough to figure out what it is trying to tell you, but other times it is a little clearer and pretty indicative of what the problem actually is.