Expected array ошибка vba excel - Не ошибается лишь тот, кто ничего не делает!

Niarah

Пользователь

Сообщений: 34
Регистрация: 14.05.2013

01.08.2013 12:04:24

Добрый день коллеги!
Ситуация делаю UserForm где есть выпадающий список с названиями
использую combobox
в него мне нужно добавить список из листа

Написал вот такой код…..
Выдает ошибку Compile error: Expected array
и выделяет Redim preserve rngY(1
если rngY меняю на Вариант
то выделяет ее же и пишет Type mismatch
В общем не могу понять что не так делаю

Код

Private Sub DolgnosList_DropButtonClick()
Dim colDolgnost As New Collection
Dim i%, k%
Dim rngY As String
Dim intROW As Integer

intROW = Worksheets("Должность".Cells(Rows.Count, 1).End(xlUp).Row
Do
i = i + 1
ReDim Preserve rngY(1 To intROW - 1)
rngY(i) = Worksheets("Должность".Cells(i + 1, 1)
Loop Until rngY(i) = intROW - 1

For k = 1 To i - 1
DolgnosList.AddItem rngY(k)
Next k
End Sub

Юрий М Модератор Сообщений: 60761 Регистрация: 14.09.2012 Контакты см. в профиле	А зачем вообще ReDim Preserve? Забирайте в массив и сразу этот массив в ComboBox. Ну или циклом AddItem.

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

01.08.2013 12:33:20

Цитата
Niarah пишет: что не так делаю

Многое!

Цитата
Юрий М пишет: А зачем вообще ReDim Preserve?

Да еще в цикле. Размерность же не меняется.
Вместо всего того великолепия, одна строчка кода:

Код
Private Sub DolgnosList_DropButtonClick() DolgnosList.List = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row).Value End Sub

И используйте теги для оформления кода.

Изменено: KuklP — 19.10.2015 16:13:26

Я сам — дурнее всякого примера! …

Niarah

Пользователь

Сообщений: 34
Регистрация: 14.05.2013

01.08.2013 12:37:47

Цитата
Юрий М пишет: А зачем вообще ReDim Preserve? Забирайте в массив и сразу этот массив в ComboBox. Ну или циклом AddItem.

М-м-м в первом варианте я задаю rngY как массив ? array ? или new collection
я еще слаб в массивах )

а циклом вот так ?

Код
for i = 1 to introw ' introw = число строк в списке dolgnosList.additem worksheets("Должность".cells(i,1) next i

А потом то что они выберут мне нужно внести в лист
.cells(1,1)=dolgnosList.selected
вот так ?

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

Niarah Пользователь Сообщений: 34 Регистрация: 14.05.2013

Sanya_Bars

Пользователь

Сообщений: 2
Регистрация: 09.02.2014

Спасибо мало…..
Человек вопрос задал а вы ему так и не ответили а предложили свое решение
Ваш форум читает не только создатель темы но еще многие пользователи интернета по запросу в поисковике
а данное решение мне вот например не подходит
Мне нужно знать где я ошибся чтоб больше не повторять эти ошибки
Так что буду искать дальше в чем я ошибся

Юрий М Модератор Сообщений: 60761 Регистрация: 14.09.2012 Контакты см. в профиле	Почему предложенное решение не подходит? И какая проблема у Вас — как проявляется ошибка?

Sanya_Bars

Пользователь

Сообщений: 2
Регистрация: 09.02.2014

1.Я свою проблему решил
при создании массива ReDim newarr(…бла бла бла) VBA ругался .Compile error: Expected array
я ошибочно присвоил переменную as Range для массива newarr
замена переменнной на variant помогла… ну либо просто без объявления
2. мне в коде не нужен UserForm и выпадающий список, а нужен был именно массив для работы с данными, я просто скопировал его
из своего другого кода но там он работал а в новом нет. долго ломал голову. в старом коде Dim далеко в верху

я сам не программист и VBA недели 2 изучаю, так что много детских ошибок ни одного действия без интернета не получилось бы
щас появилось время и я пытаюсь облегчить себе жизнь при помощи VBA
очень часто ошибаюсь с синтаксисом

По поводу темы форума, меняем на variant массив и дальше начинает ругаться VBA «…пишет Type mismatch…»
дальше надо смотреть где собака порылась [IMG]
……посмотрел….поправте если я не прав чтоб применить ReDim Preserve rngY() нужно сначала создать ReDim rngY()

Niarah

у Вас что-то в цикле напутано, у Вас условие выхода из цикла сравнение значения массива и номера предпоследней строки
такое совпадение редко может у Вас в ячейках буквы вообще
может rngY(i) = intROW — 1 заменить на i = intROW — 1 или rngY(i) = rngY(intROW — 1)
мне не понятна задача что вы хотите сделать с данными
может нужно просто попробовать другой ЦИКЛ

Софья Золкина

Пользователь

Сообщений: 14
Регистрация: 01.01.1970

#10

19.10.2015 16:06:26

Sanya_Bars, Спасибо тебе добрый человек. Я долго мучилась с этим «Type mismatch…».
мой исходник выглядел так:

Скрытый текст

После я решила применить ваше указание по поводу «сначала создать ReDim «, но подумав, получается без Preserve мой массив не сохраняет предыдущие вычисления с снова переопределяет массив. Немного подумав я решила обмануть программу и написала так. и все заработало:

Скрытый текст

Удачи всем. Возможно мой опыт тоже кому-то поможет, как мне помог опыт Sanya_Bars.

Изменено: Софья Золкина — 19.10.2015 16:13:02

Источник

Can anyone help me?

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. 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.

Here is my code:

 Public Mother_Array() as Variant, BabyOne_Array(), BabyTwo_Array(), BabyThree_Array(), BabyFour_Array() as Double 'declare may other variables and arrays, too

Sub MainMacro()
    'do stuff

   Call SunRaySubRoutine(x, y)

    'do stuff

    Range("blah") = BabyOne_Array: Range("blahblah") = BabyTwo_Array
    Range("blahbloh" = BabyThree_Array: Range("blahblue") = BabyFour_Array

End Sub

Sub SunRaySubRoutine(x,y)
    n = x * Sheets("ABC").Range("A1").Value + 1

    ReDim Mother_Array(18, n) as Variant, BabyOne_Array(n), BabyTwo_Array(n) as Double
    ReDim BabyThree_Array(n), BabyFour_Array(n) as Double

    'do stuff

    For i = 0 to n

        BabyOne_Array(i) = Mother_Array(0,i)
        BabyTwo_Array(i) = Mother_Array(2,i)
        BabyThree_Array(i) = Mother_Array(4,i)
        BabyFour_Array(i) = Mother_Array(6,i)
    Next        

End Sub

I have tried to declare all arrays as the Variant type, but to no avail. I have tried to give BabyFour_Array() a different name, but to no avail.

What’s really strange is that even if I comment out the part which makes the BabyFour_Array(), the array still has zero values for each element.

What’s also a bit strange is that the first baby array never craps out (although, the 2nd one crapped out once (one time out of maybe 30).

BANDAID: As a temporary fix, I just publicly declared a fifth dummy array (which doesn’t get filled or Re-Dimensioned). This fifth array has no actual use besides tricking the system out of having the «Compile Error: Expected Array».

Does anyone know what’s causing this «Compile Error: Expected Array» problem with Excel VBA?

Thanks,

Elias

Источник

The compile error is because you’ve got:

Code:

Dim Look_up_Data As String
....
ReDim Look_up_Data(End_line)

If you’re going to use ReDim once you know how big to make the array, you should declare the variable as an array initially:

Code:

Dim Look_up_Data[B][COLOR=#ff0000]()[/COLOR][/B] As String

BUT ….

It looks like there might be other problems with your code …

This line, for example, looks like it’s meant to assign the values in a 2-D range Look_up_data_range to a VBA array. However, this will only work if Look_up_Data is of type Variant, not String or any other type.

Code:

Look_up_Data = Look_up_data_range.Value

Did your colleague have the code working? Have you perhaps made changes to code that was once working?

Doesn’t work here anymore.
And the assign it to me (as «Chinese Volunteer»)

Don’t know if it was working at all

I’ve changed :

Dim Look_up_data_range() As Range
Dim Look_up_Data As Variant

When I run the code now, I’ve got on following line the error message: Can’t assign to array for this line:

Set Look_up_data_range = ActiveSheet.UsedRange

This line was OK without the brackets:

Code:

Dim Look_up_data_range[COLOR=#ff0000][B]()[/B][/COLOR] As Range

But before we go on making piecemeal changes ….

The code you’ve posted doesn’t actually do a lot, and it does it in a roundabout way.

How much more code is there?

Perhaps the code never worked in the first place. If so, and if you’re not clear what it’s intended to do, then potentially, it’s going to be difficult to «correct».

As I’ve mentioned, they have assign it to me.
So I don’t know if it has worked at all.

Below the complete code of the 5 different userforms.

Userform1:

Code:

Private Sub CmdBtn_Check_Shelf_Life_Click()UserForm1.Hide
UserForm2.Show


End Sub


Private Sub CmdBtn_Invoeren_Shelf_Life_900_Click()
UserForm1.Hide
UserForm3.Show


End Sub


Private Sub CmdBtn_Local_Company_Click()
UserForm1.Hide
UserForm4.Show
End Sub


Private Sub CmdBtn_IQC_Click()
UserForm1.Hide
UserForm4.Show
End Sub


Private Sub CmdBtn_View_Excel_Click()


Dim Password As String
Answer = InputBox("Enter Password")
Password = "xxyyzz"


If Answer = Password Then
Application.Visible = True
'Worksheets("Shelf_Life_900").Activate
UserForm1.Hide
'CmdBtn_View_Excel.Enabled = True
Else
CmdBtn_View_Excel.Enabled = True
End If


End Sub

Userform2:

Code:

Option Explicit

Private Sub UserForm_Initialize()
     UserForm1.BackColor = RGB(153, 204, 153)
Dim v, e


With Sheets("LookUpList").Range("A1:A5")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.CmbBox_Period.List = Application.Transpose(.keys)
End With
     
End Sub
Private Sub CommandButton2_Click()


Worksheets("Shelf_Life_900").Activate
UserForm2.Hide
    
End Sub


Private Sub CommandButton3_Click()


UserForm2.Hide
UserForm1.Show


End Sub
Private Sub CommandButton1_Click()


Const Look_up_sheet = "Shelf_Life_900"
Const CST_First_Line = 2
Const CST_Item_Col = 1
Const CST_Shelf_Life_Col = 2
Const CST_Number_of_Periods = 3
Const CST_Technical_Responsible_Col = 4
Const CST_Date_Col = 5
Const CST_Remarks_Col = 6
Dim ctl_Cont As Control
Dim Row As Long
Dim ws As Worksheet
Dim MyString As String
Dim Item As Variant
Dim Look_up_data_range As Range
Dim Look_up_Data() As String
Dim End_line As Integer
Dim Item_Name As String
Dim Item_exist As Boolean
Dim Index_Cell As Integer
Dim My_Cell As Variant
Dim x As Range


'Check if TxtBox_Item is not empty
If TxtBox_Item_Number.Text = "" Then
    MsgBox ("Item is not filled in")
    Exit Sub
End If


'Activate look up worksheet
Worksheets(Look_up_sheet).Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False


'Read look data
Set x = Sheets("Request_for_Shelf_Life").Range("A:A").Find(TxtBox_Item_Number.Text)
    If Not x Is Nothing Then TxtBox_TC.Text = x.Offset(, 2).Value
'Set Look_up_data_range = ActiveSheet.UsedRange
'End_line = Look_up_data_range.Rows.Count
'Set Look_up_data_range = ActiveSheet.Range( _
            ActiveSheet.Cells(CST_First_Line, CST_Item_Col), _
            ActiveSheet.Cells(End_line, CST_Item_Col))


'Look_up_Data = Look_up_data_range.Value
ReDim Look_up_Data(End_line)
Index_Cell = 0
For Each My_Cell In Look_up_data_range.Cells
    Look_up_Data(Index_Cell) = My_Cell.Value
    Index_Cell = Index_Cell + 1
Next My_Cell
                
'Check If data already present
Item_Name = TxtBox_Item_Number.Text
Item_exist = False
For Each Item In Look_up_Data
    If Item = Item_Name Then
        Item_exist = True
        Exit For
    End If
Next Item
       
'Msg box if item exist
If Item_exist = True Then
    MsgBox (Item_Name & " " & "already excists")
Else
    
    'Check if CmbBox_Period is not empty
    If CmbBox_Period.Text = "" Then
        MsgBox ("Shelf Life Period is not filled in")
        Exit Sub
    End If
    
    'Check if TxtBox_Shelf_Life is not empty
    If TxtBox_Shelf_Life.Text = "" Then
        MsgBox ("Number of Periods is not filled in")
        Exit Sub
    End If
    
    'Check if Technical Responsible is not empty
    If TxtBox_TC.Text = "" Then
        MsgBox ("Requester is not filled in")
        Exit Sub
    End If
    
    'Fill in the cells
     Application.ActiveSheet.Cells(End_line + 1, CST_Item_Col).Value = TxtBox_Item_Number.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Shelf_Life_Col).Value = CmbBox_Period.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Number_of_Periods).Value = TxtBox_Shelf_Life.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Technical_Responsible_Col).Value = TxtBox_TC.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Remarks_Col).Value = TxtBox_Remarks.Text
     
     'Clear the text boxes
     TxtBox_Item_Number.Text = ""
     CmbBox_Period.Text = ""
     TxtBox_Shelf_Life.Text = ""
     TxtBox_TC.Text = ""
     TxtBox_Remarks = ""
End If


End Sub

Userform3:

Code:

Private Sub CommandButton2_Click()

UserForm3.Hide
UserForm1.Show


End Sub


Private Sub CommandButton1_Click()


Const Look_up_sheet = "Request_for_Shelf_Life"
Const CST_First_Line = 2
Const CST_Item_Col = 1
Const CST_Request_Col = 2
Const CST_Department_Col = 3
Const CST_Responsible_Col = 4
Dim ctl_Cont As Control
Dim Row As Long
Dim ws As Worksheet
Dim MyString As String
Dim Item As Variant
Dim Look_up_data_range As Range
Dim Look_up_Data() As String
Dim End_line As Integer
Dim Item_Name As String
Dim Item_exist As Boolean
Dim Index_Cell As Integer




'Check if TxtBox_Item is not empty
If TxtBox_Item.Text = "" Then
    MsgBox ("Item is not filled in")
    Exit Sub
End If


'Activate look up worksheet
Worksheets(Look_up_sheet).Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False


'Read look data
Set Look_up_data_range = ActiveSheet.UsedRange
End_line = Look_up_data_range.Rows.Count
Set Look_up_data_range = ActiveSheet.Range( _
            ActiveSheet.Cells(CST_First_Line, CST_Item_Col), _
            ActiveSheet.Cells(End_line, CST_Item_Col))
'Look_up_Data = Look_up_data_range.Value
ReDim Look_up_Data(End_line)
Index_Cell = 0
For Each My_Cell In Look_up_data_range.Cells
    Look_up_Data(Index_Cell) = My_Cell.Value
    Index_Cell = Index_Cell + 1
Next My_Cell
        
        
        
'Check If data already present
Item_Name = TxtBox_Item.Text
Item_exist = False
For Each Item In Look_up_Data
    If Item = Item_Name Then
        Item_exist = True
        Exit For
    End If
Next Item
       
'Msg box if item exist
If Item_exist = True Then
    MsgBox (Item_Name & " " & "already exists")
Else
    
    'Check if TxtBox_Requester is not empty
    If TxtBox_Requester.Text = "" Then
        MsgBox ("Requester is not filled in")
        Exit Sub
    End If
    
    'Check if TxtBox_Requester is not empty
    If TxtBox_Afdeling.Text = "" Then
        MsgBox ("Department is not filled in")
        Exit Sub
    End If
    
    'Check if Responsible is not empty
    If TxtBox_TC.Text = "" Then
        MsgBox ("Technical Responsible is not filled in")
        Exit Sub
    End If
    
    'Fill in the cells
     Application.ActiveSheet.Cells(End_line + 1, CST_Item_Col).Value = TxtBox_Item.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Request_Col).Value = TxtBox_Requester.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Department_Col).Value = TxtBox_Afdeling.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Responsible_Col).Value = TxtBox_TC.Text
     
     'Clear the text boxes
     TxtBox_Item.Text = ""
     TxtBox_Requester.Text = ""
     TxtBox_Afdeling.Text = ""
     TxtBox_TC.Text = ""
End If


End Sub

Userform4:

Code:

Private Sub CmdBtn_Local_Company_Click()


Const Look_up_sheet = "Local_Company"
Const CST_First_Line = 2
Const CST_Item_Col = 1
Const CST_Division_Col = 2
Const CST_Responsible_Col = 3
Const CST_Company_Col = 4
Dim ctl_Cont As Control
Dim Row As Long
Dim ws As Worksheet
Dim MyString As String
Dim Item As Variant
Dim Look_up_data_range As Range
Dim Look_up_Data() As String
Dim End_line As Integer
Dim Item_Name As String
Dim Item_exist As Boolean
Dim Index_Cell As Integer
Dim My_Cell As Variant




'Check if TxtBox_Item is not empty
If TxtBox_Item_Number.Text = "" Then
    MsgBox ("Item is not filled in")
    Exit Sub
End If


'Activate look up worksheet
Worksheets(Look_up_sheet).Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False


'Read look data
Set Look_up_data_range = ActiveSheet.UsedRange
End_line = Look_up_data_range.Rows.Count
Set Look_up_data_range = ActiveSheet.Range( _
            ActiveSheet.Cells(CST_First_Line, CST_Item_Col), _
            ActiveSheet.Cells(End_line, CST_Item_Col))


'Look_up_Data = Look_up_data_range.Value
ReDim Look_up_Data(End_line)
Index_Cell = 0
For Each My_Cell In Look_up_data_range.Cells
    Look_up_Data(Index_Cell) = My_Cell.Value
    Index_Cell = Index_Cell + 1
Next My_Cell
                
'Check If data already present
Item_Name = TxtBox_Item_Number.Text
Item_exist = False
For Each Item In Look_up_Data
    If Item = Item_Name Then
        Item_exist = True
        Exit For
    End If
Next Item
       
'Msg box if item exist
If Item_exist = True Then
    MsgBox (Item_Name & " " & "already exists")
Else
    
    'Check if TxtBox_Division is not empty
    If TxtBox_Division.Text = "" Then
        MsgBox ("Division is not filled in")
        Exit Sub
    End If
    
    'Check if TxtBox_Responsible is not empty
    If TxtBox_Responsible.Text = "" Then
        MsgBox ("Responsible is not filled in")
        Exit Sub
    End If
    
    'Check if TxtBox_Company is not empty
    If TxtBOx_Company.Text = "" Then
        MsgBox ("Local Company is not filled in")
        Exit Sub
    End If
    
    'Fill in the cells
     Application.ActiveSheet.Cells(End_line + 1, CST_Item_Col).Value = TxtBox_Item_Number.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Division_Col).Value = TxtBox_Division.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Responsible_Col).Value = TxtBox_Responsible.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Company_Col).Value = TxtBOx_Company.Text
     
     'Clear the text boxes
     TxtBox_Item_Number.Text = ""
     TxtBox_Division.Text = ""
     TxtBox_Responsible.Text = ""
     TxtBOx_Company.Text = ""


End If


End Sub




Private Sub CmdBtn_Menu_Click()


UserForm4.Hide
UserForm1.Show


End Sub




Private Sub CmdBtn_View_Excel_Click()


Worksheets("Local_Company").Activate
UserForm4.Hide


End Sub

Userform5:

Code:

Private Sub CmdBtn_IQC_Click()

UserForm3.Hide
UserForm1.Show


End Sub


Private Sub CmdBtn_IQC_Write_Click()


Const Look_up_sheet = "IQC"
Const CST_First_Line = 2
Const CST_Item_Col = 1
Const CST_Responsible_Col = 2
Const CST_Refrigerator_Col = 3
Dim ctl_Cont As Control
Dim Row As Long
Dim ws As Worksheet
Dim MyString As String
Dim Item As Variant
Dim Look_up_data_range As Range
Dim Look_up_Data() As String
Dim End_line As Integer
Dim Item_Name As String
Dim Item_exist As Boolean
Dim Index_Cell As Integer




'Check if TxtBox_Item is not empty
If TxtBox_Item_Number.Text = "" Then
    MsgBox ("Item is not filled in")
    Exit Sub
End If


'Activate look up worksheet
Worksheets(Look_up_sheet).Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False


'Read look data
Set Look_up_data_range = ActiveSheet.UsedRange
End_line = Look_up_data_range.Rows.Count
Set Look_up_data_range = ActiveSheet.Range( _
            ActiveSheet.Cells(CST_First_Line, CST_Item_Col), _
            ActiveSheet.Cells(End_line, CST_Item_Col))
'Look_up_Data = Look_up_data_range.Value
ReDim Look_up_Data(End_line)
Index_Cell = 0
For Each My_Cell In Look_up_data_range.Cells
    Look_up_Data(Index_Cell) = My_Cell.Value
    Index_Cell = Index_Cell + 1
Next My_Cell
        
        
        
'Check If data already present
Item_Name = TxtBox_Item_Number.Text
Item_exist = False
For Each Item In Look_up_Data
    If Item = Item_Name Then
        Item_exist = True
        Exit For
    End If
Next Item
       
'Msg box if item exist
If Item_exist = True Then
    MsgBox (Item_Name & " " & "already exists")
Else
    
    'Check if TxtBox_Responsible is not empty
    If TxtBox_Responsible.Text = "" Then
        MsgBox ("Reponsible is not filled in")
        Exit Sub
    End If
    
    'Check if TxtBox_Refrigerator is not empty
    If TxtBox_Refrigerator.Text = "" Then
        MsgBox ("Refrigerator is not filled in")
        Exit Sub
    End If
    
    
    'Fill in the cells
     Application.ActiveSheet.Cells(End_line + 1, CST_Item_Col).Value = TxtBox_Item_Number.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Responsible_Col).Value = TxtBox_Responsible.Text
     Application.ActiveSheet.Cells(End_line + 1, CST_Refrigerator_Col).Value = TxtBox_Refrigerator.Text
     
     'Clear the text boxes
     TxtBox_Item_Number.Text = ""
     TxtBox_Responsible.Text = ""
     TxtBox_Refrigerator.Text = ""
     
End If


End Sub


Private Sub CmdBtn_Menu_Click()
UserForm5.Hide
UserForm1.Show
End Sub


Private Sub CmdBtn_View_Excel_Click()
Worksheets("IQC").Activate
UserForm5.Hide
End Sub

As a first step, try replacing all this code (from Sub CommandButton1_Click in UserForm2):

Code:

Look_up_Data = Look_up_data_range.Value
ReDim Look_up_Data(End_line)
Index_Cell = 0
For Each My_Cell In Look_up_data_range.Cells
   Look_up_Data(Index_Cell) = My_Cell.Value
   Index_Cell = Index_Cell + 1
Next My_Cell
                
'Check If data already present
Item_Name = TxtBox_Item_Number.Text
Item_exist = False
For Each Item In Look_up_Data
    If Item = Item_Name Then
        Item_exist = True
        Exit For
    End If
Next Item

With:

Code:

Item_exist = Not IsError(Application.Match(TxtBox_Item_Number.Text, Look_up_data_range, 0))

Источник

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) &lt; 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) &lt; 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.