Dim array in userform

[chungtinhlak]

I get an error "constant Expression Required" at

Dim keywords(1 To UserForm1.numofkeywords) As Long, match As Long


Can somebody help me?

thanks

Private Sub CommandButton1_Click()
usedcell = Application.WorksheetFunction.CountA(Range("A:A"))
Dim keywords(1 To UserForm1.numofkeywords) As Long, match As Long

For w = 1 To numofkeywords
keywords(w) = InputBox("Please input keyword " & w)
Next

Range(des & 1).Value = "Contains Your keyword"
For i = 2 To usedcell
For x = 1 To numofkeywords
If Range(search & i).Value Like "*" & keywords(x) Then
match = match + 1
Next x
Range(des & i).Value = match
match = 0
Next i
End Sub

[Bob Phillips]

I wouldn't have thought you could dim your array like that, as it is the userform will not be loaded when the variable is alklocated, so the value will be zerom so the array will be invalid.

Declare as variant and then redim that number of times when you know that you have a value.

[chungtinhlak]

what is a variant and how do I use that? Sorry, i'm really new at this

thanks.

[Bob Phillips]

Like this



Dim keywords As Variant


and then later, you redim it



ReDim keywords(1 To UserForm1.numofkeywords)

[chungtinhlak]

at what point in the macro do i redim?

[Bob Phillips]

When you know that Userform1.numofKeywords has a value.

[chungtinhlak]

currently, the dimming is done in the click, and when we click, we know that it has value already, right?

[Bob Phillips]

No idea, it is your code. Is the click associated with the form, is the form up, with that field entered?

[chungtinhlak]

the click is associated with the form. When the form loads, userform1.numofKeywords is empty and the user inputs that in, then click search.

[Bob Phillips]

So yes, it would seem that at that point you can safely redim the array.