Expected array ошибка vba excel

Expected initializer before meaning error has a huge number of variations depending on its location in your program. Syntactic mistakes are the one and only reason behind the said error, and the variety in the same is the cause of multiple variations of the given error.

This post deals with pointing out the most common syntactic mistakes made by novice and expert developers and the practical solutions to get rid of the stated error. Continue reading to uncover the working solutions for the error in the title.

What Does the Behind the Expected Initializer Before Error Occurs?

The expected initializer before error occurs due to the mistakes in your program’s syntax, such as missing semicolons, parentheses, curly braces, etc. In short, anytime and anywhere you go against the language rules, you’ll see the same error popping up on your screen.

– The Syntactic Mistakes

A problematic program syntax can make the expected initializer before in C error appear on your screen. The syntactic mistakes can vary depending on your program. For example, you can have missing semicolons, incorrectly defined pointers, missing parentheses in the function definition, etc., resulting in the said error.

The semicolons working as statement terminators in your code require sufficient attention. If you ignore them often or don’t place them in their required positions, you’ll encounter the stated error.

The missing semicolons form the most common cause behind the given error. However, the last word in the error statement will be the one before which you are going wrong with the syntax.

To gain more clarity, please consider an example. Imagine that you have created a “course” struct with three properties. You have specified the properties correctly by mentioning their data types, followed by their names.

However, you forgot to end a particular property declaration statement with a semicolon. Plus, the next property after it has its data type set to int.

In the above situation, you’ll see the expected initializer before ‘int error on your screen. The error suggests that your code expects something before int. In this case, it is a semicolon. You can see a relevant erroneous code block below.

Moving towards another example, say that your program declares and defines a “create” function after creating a “student” struct.

Again, if you miss putting a semicolon after the curly bracket “}” that ends the struct, the error complaining about an expected initializer before function will pop up on your system. The code snippet depicting the current situation has been attached below.

Similarly, if you are using the conditional if-else blocks in your program and you make a mistake just before the “if” statement, your code will result in an expected initializer before ‘if error. Here you go with a sample code that aligns with the stated case.

Now, even if you receive the expected initializer before cout error, you’ll know that the syntax error sits just before the cout statement.

How To Resolve the Expected Initializer Before Error?

You can resolve the expected initializer before error by double-checking your erroneous line of code and correcting any coding mistakes inside it. Moreover, placing the missing semicolons at the end of the coding statement before the targeted word will help you resolve the error instantly.



– Be More Cautious About the Semicolons

The missing semicolons are the smallest culprits in your program. Thus, it would be best to give them extra attention and use them wherever required to resolve the expected initializer before Arduino error permanently. To know the expected location, notice the term following the error statement.

Now, please reconsider the previous examples. You’ll have to end the duration property of the course struct with a semicolon to resolve the error. Furthermore, you’ll need to place a semicolon after the closing curly bracket of the struct to make the same error go away.

– Double-Check Your Code Syntax and Make It Right

You must double-check the syntax and correct any syntactic mistakes to remove the said error from your PC. Mostly, the error statement targets a particular line of code. Thus, you don’t need to check the entire code, only the targeted line, to make the right changes.

However, to check the syntax effectively, it is crucial to have sufficient knowledge about the language’s syntax. You must know what’s acceptable and what isn’t to be able to correct your accidental mistakes.

Thus, if you are a beginner, then you might need an expert eye on your code block. Also, if you can’t reach out to an expert, you might look for the correct syntax for the line of code throwing the same error.

Say that you are getting the above error targeting the if statement. The line before the if statement is a function definition, as displayed in an example earlier. In such a scenario, if you are certain about the syntax used to create a function, you’ll notice the missing curly bracket. Consequently, you’ll add one to push away the error.

On the other hand, if you are a beginner, you might not be able to figure out the issue. In this case, you’ll need expert advice or look at an online or offline programming guide to learn about the correct way to create a function. It will help you see the difference between the right syntax and your block of code.

FAQs

1. What Is a Helpful Tip To Avoid the Expected Initializer Before Errors?

You can avoid a variety of expected initializer before errors by learning the syntax by heart. If you know the correct syntax and code your programs with zero syntax errors, you’ll not see the mentioned error unless you are in a hurry and make a mistake accidentally.

2. What Does the Error Expected ‘}’ Before ‘Else’ Mean?

The expected’}’ before ‘else’ error means that you haven’t closed the if body using the ‘}’ curly bracket and have started the else scope inside the if scope. You can resolve the error by adding the missing curly bracket at the end of the if body before the else statement.

3. What Does the Expected ‘)’ Before ‘;’ Token Inform?

The expected’)’ before ‘;’ token error informs that you have unnecessarily terminated a statement with a semicolon. For example, if you end the #define statements with a semicolon, running your program will result in the said error. It is because such statements do not require a semicolon.

Conclusion

The expected initializer before error tells you about the syntax errors not fulfilling the expectations of the language in use. You can gather more details about the error by looking at the last term in the error statement and observing the targeted line of code.

Here are a few tips from the above post that’ll help you deal with the error like a pro.

• Double-check the targeted line of code for correctness and make the right changes.
• If you are a beginner, gain expert advice regarding the mistakes in your code.
• Look into the online or offline programming guides to match your code with the correct syntax.
• End your coding statements with semicolons to lower the risk of the given error.
• Always close the brackets you open to ensure no bracket goes missing.

Being cautious about every character you type in your program will help you stay on the right track and avoid such errors.

A variable name with a subscript indicates the variable is an array. This error has the following cause and solution: The syntax you specified is appropriate for an array, but no array with this name is in scope. Check to make sure the name of the variable is spelled correctly.

What does expected array mean?

An array is a type of variable which differs from a ‘normal’ variable in that it can hold multiple values rather than just one value at a time. There can be a few reasons why you would receive an “Expected array” error. We have declared the Status variable as a String – but we have NOT declared it as a String ARRAY.

How do you create an array in VBA?

Use a Static, Dim, Private, or Public statement to declare an array, leaving the parentheses empty, as shown in the following example. You can use the ReDim statement to declare an array implicitly within a procedure. Be careful not to misspell the name of the array when you use the ReDim statement.

How do you reset an array in Excel VBA?

VBA Clear Array (Erase) You need to use the “Erase” statement to clear an array in VBA. Add the keyword “Erase” before the name of the array that you want to clear but note that all the values will be reset from the array. In the following array, you have 3 elements and we have used the erase statement to erase all.

How do you clear a collection in VBA?

To remove all items from a collection you can simply set it to nothing. If you set this collection to nothing then it will be set to the state where the “object is not set”. When you add a new item VBA automatically sets the Collection variable to a valid collection.

How do you clear a string in VBA?

A variant variable has the Empty value prior to it being assigned a value.

1. Initialising a Variant. The Empty data type is the default value when you declare a variable as a Variant.
2. Empty = 0. The Empty keyword is also a zero value.
3. Empty = “” The Empty keyword is also a zero length string.
4. IsEmpty Function.
5. vbVarType.

How do I fix type mismatch error in Excel?

Solution: Try to make assignments only between compatible data types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except a user-defined type) can be assigned to a Variant.

Why do I get expected array error in Excel?

I have been getting a compile error (…: “Expected Array”) when dealing with arrays in my Excel workbook. Basically, I have one ‘mother’ array (2D, Variant type) and four ‘baby’ arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes.

Why do I get array error in VBA?

You need to check the syntax of your code carefully – it may be that you have an error in your code and you DON’T actually want an array, or you may have declared the variable incorrectly. See also: Can’t Assign to Array. ​. Tagged with: Array, Arrays, compile error, declaration, redim, vba error.  .

Why is my expected array not in scope?

Expected array. A variable name with a subscript indicates the variable is an array. This error has the following cause and solution: The syntax you specified is appropriate for an array, but no array with this name is in scope. Check to make sure the name of the variable is spelled correctly.

Why is there no array in Microsoft Docs?

This error has the following cause and solution: The syntax you specified is appropriate for an array, but no array with this name is in scope. Check to make sure the name of the variable is spelled correctly. Unless the modulecontains Option Explicit, a variable is created on first use.

Why do I get expected array error in VBA?

Basically, I have one ‘mother’ array (2D, Variant type) and four ‘baby’ arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes. Unfortunately, the final of the baby arrays craps out (giving the “Compile Error: Expected Array”).

Why do I get compile error expected array?

Unfortunately, the final of the baby arrays craps out (giving the “Compile Error: Expected Array”). Strangely, if I remove this final baby array (‘final’ – as in the order of declaration/definition) the 2nd to last baby array starts crapping out. I have tried to declare all arrays as the Variant type, but to no avail.

When does a compile error occur in VBA?

The syntax error occurs even before you run the code and the font of the problematic line turns to red. The compile error occurs when you try to run the code and VBA identifies that something is missing.

How to fix expected end of statement in VBA?

Step 1: Open the Visual Basic Editor and create a new module as seen in the gif below. The error comes from the fact that two statements have been written in one line instead of two. The code should be: From the types of errors in VBA described above, you must have guessed that the “Expected: end of statement” error is a syntax error.

Edit 2: THIS HAS BEEN RESOLVED. I made the mistake of declaring a variable with the name ‘Year’…

I am trying to get a cell to contain a copyright in a series of reports I deliver, and since the copyright changes with the year, I want to call the Year function to give me the current year so I don’t have to update the macro every January. My code in question looks like this:

With .Cells(5, 1)
            .Value = Chr(169) & " " & Year(Date) & " NCH Marketing Services, Inc"
            .Font.Bold = False
            .Font.Name = "Arial"
            .Font.Size = 11
        End With

When I execute this code, I get the error message «Compile Error: Expected Array». The odd thing is, I have tried to run this macro on three separate occasions, and it did not compile the first or third times, but the second time, it ran just fine. I have not made any edits to the code, and I have looked online for syntax/usage of the Year function, and I cannot figure out why this is working sometimes. Is there a way I can implement this in a more reliable fashion?

Edit: Below is the full code

Option Explicit
Sub RGA_Format_Reports()

Application.ScreenUpdating = False

Dim Year As Integer
Dim Quarter As Integer

Dim FolderPath As String
Dim FileName As String
Dim WorkBk As Workbook

Dim iRows As Integer
Dim iCols As Integer

Dim shpTitle As Shape
Dim shpLogo As Shape


'Quarter = InputBox("Please enter the quarter number for which the reports are being run.")

'Year = InputBox("Please enter the year for which the reports are being run.")

'FolderPath = "G:\Analytical Services\Internal Client Requests\NRS\Scheduled\" & Year & "\Quarterly RGA Store Alert Reports\" & Year & " Q" & Quarter & "\"

'FolderPath = "G:\Analytical Services\General Team Folders\Kyle\Macro Tests\RGA Reports\"

'FileName = Dir(FolderPath & "*.xl*")

'DoWhile FileName <> ""

    'Set WorkBk = Workbooks.Open(FolderPath & FileName)
        'With WorkBk

With ActiveWorkbook

        With .ActiveSheet

            iRows = .UsedRange.Rows.Count
            iCols = .UsedRange.Columns.Count

            .Rows.AutoFit
            .Columns.AutoFit

            .Columns(1).ColumnWidth = 30


            ActiveWindow.FreezePanes = False
            .Range("A9").Select
            ActiveWindow.FreezePanes = True

            With .Range(.Cells(iRows - 2, 1), .Cells(iRows, 1))
                .WrapText = False
                .Font.Name = "Arial"
                .Font.Size = 10
            End With


            With .PageSetup
                .TopMargin = Application.InchesToPoints(0.25)
                .BottomMargin = Application.InchesToPoints(0.25)
                .LeftMargin = Application.InchesToPoints(0.25)
                .RightMargin = Application.InchesToPoints(0.25)
                .CenterHorizontally = True
                .Zoom = False
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = False
                .PrintTitleRows = "$1:$8"
            End With


            .Range(.Cells(9, 3), .Cells(iRows, 3)).HorizontalAlignment = xlRight
            .Range(.Cells(iRows - 4, 1), .Cells(iRows - 4, iCols)).Font.Bold = True
            .Range(.Cells(iRows - 4, 1), .Cells(iRows - 4, iCols)).Interior.Color = RGB(238, 236, 225)


            With .Range(.Cells(7, 9), .Cells(7, 12))
                .Merge
                .HorizontalAlignment = xlCenter
                .Font.Bold = True
                .Font.Name = "Arial"
                .Font.Size = 10
                .Interior.Color = RGB(238, 236, 225)

                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                .Value = "Total Billed"
            End With


            With .Range(.Cells(7, 13), .Cells(7, 19))
                .Merge
                .HorizontalAlignment = xlCenter
                .Font.Bold = True
                .Font.Name = "Arial"
                .Font.Size = 10
                .Interior.Color = RGB(238, 236, 225)

                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                .Value = "Total Adjustments"
            End With


            With .Range(.Cells(8, 1), .Cells(iRows - 4, 8))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With
            End With


            With .Range(.Cells(7, 9), .Cells(iRows - 4, 12))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With
            End With


            With .Range(.Cells(7, 13), .Cells(iRows - 4, 19))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

            End With

            If .Cells(9, 4) = 0 Then
                Columns("D:E").Delete
            End If

            For iRows = 1 To 4
                .Cells(iRows, 1).Font.Bold = True
                .Cells(iRows, 1).Font.Name = "Arial"
                If iRows = 1 Then
                    .Cells(iRows, 1).Font.Size = 14
                Else
                    .Cells(iRows, 1).Font.Size = 12
                End If
            Next iRows

            With .Cells(5, 1)
                .Value = Chr(169) & " " & Year(Date) & " NCH Marketing Services, Inc"
                .Font.Bold = False
                .Font.Name = "Arial"
                .Font.Size = 11
            End With

            .Columns("A").ColumnWidth = 200

            .Range(Cells(1, 1), Cells(5, 1)).WrapText = False

            .Range(Cells(1, 1), Cells(5, 1)).Copy
            .Range("B1").Select
            Application.Wait (Now + TimeValue("00:00:01"))
            .Pictures.Paste.Select
            .Range(Cells(1, 1), Cells(5, 1)).ClearContents
            .Columns("A").AutoFit
            .Range("A1").Select

            Set shpTitle = .Shapes("Picture 1")
            With shpTitle
                .Name = "Title Picture"
                .Placement = xlFreeFloating
            End With

            Set shpLogo = .Shapes.AddPicture("G:\Analytical Services\AS Tools\AS Templates\NCH Logo.png", False, True, 1, 1, 60, 67)
            With shpLogo
                .Name = "Logo Picture"
                .Placement = xlFreeFloating
            End With

            With shpTitle
                .Left = 67
            End With


            .Columns("A").ColumnWidth = 30



        End With

End With


End Sub

An array is a type of variable which differs from a ‘normal’ variable in that it can hold multiple values rather than just one value at a time.  There can be a few reasons why you would receive an “Expected array” error.

Let’s look at some code that we have to loop through a range of cells on an Excel worksheet.

Option Explicit
Public n As Long, i As Long, Status As String
Private Sub GetStatus()
'count the rows in the list of clients
	n = ClientList.Range("G5", ClientList.Range("G5").End(xlDown)).Rows.Count
'redim the Status to have the amount of rows we have counted in the 'array
	ReDim Status(n)
'loop through the array and get the status of the account
	For i = 1 To n
		If ClientList.Range("G5").Offset(i, 0) < 0 Then
			Status(i) = "Debit"
		Else
			Status(i) = "Credit"
		End If
	Next i
End Sub

The code above looks perfect, but when we run it – this error will occur:

The code is written to loop through the following rows in Excel:

The first line of the code will count how many rows are in the list, and then it will ReDim the Status variable to be able to contain that amount of rows.   The Status variable has been declared to hold multiple values – and the code will loop through the cells from G5 to the last cell, and store either Debit or Credit as the status for that cell, depending on what is in the appropriate cell.

At a glance, the code looks fine, but there clearly is a problem as we get a compile error.   This type of error can be hard to find.

If we look closely at the code, and at the error – the error says ‘expected array’ – and we have re-dimmed the variable Status in line 2 of the code.   However, in order to ReDim an Array, we first have to actually declare an Array – and there lies our problem.  We have declared the Status variable as a String – but we have NOT declared it as a String ARRAY. The solution is annoyingly simple – which is why so many people make the same error.  The correct code is below – can you spot the amendment?

Option Explicit
Public n As Long, i As Long, Status() As String

Private Sub GetStatus()
'count the rows in the list of clients
	n = ClientList.Range("G5", ClientList.Range("G5").End(xlDown)).Rows.Count
'redim the Status to have the amount of rows we have counted in the 'array
	ReDim Status(n)
'loop through the array and get the status of the account
	For i = 1 To n
		If ClientList.Range("G5").Offset(i, 0) < 0 Then
			Status(i) = "Debit"
		Else
			Status(i) = "Credit"
		End If
	Next i
End Sub

Look at the Public variables at the top of the module – just under Option Explicit.

In the first code snippet – the variables look like this:

Public n As Long, i As Long, Status As String

But in the second code snipped, the variables now look like this:

Public n As Long, i As Long, Status() As String

The Status has now been declared as an ARRAY – simply by adding the brackets behind the word STATUS.  This now enables the variable Status to hold multiple values, and not just one value.

So in summary, if you get this message, there is a variable in your code that is being expecting to be populated with multiple values, but your declaration of the variable is indicating that only a single value can go into that variable.   You need to check the syntax of your code carefully – it may be that you have an error in your code and you DON’T actually want an array, or you may have declared the variable incorrectly.

See also: Can’t Assign to Array