statement invalid outside type block

[Nasir.Munir]

Can anyone help me out with this please, I get the error "statement
invalid outside type block"

Sub minNumber()

Sheet = UserForm1.TextBox1.Text ' getting values from form.
startRow = UserForm1.TextBox2.Text
startCol = UserForm1.TextBox7.Text
endCol = UserForm1.TextBox11.Text
startValRow = UserForm1.TextBox12.Text
startValCol = UserForm1.TextBox4.Text
endValCol = UserForm1.TextBox13.Text
endValRow = UserForm1.TextBox3.Text
destCol = UserForm1.TextBox10.Text

result(0 To endCol - 1) As Integer ************* thats the problem
line, need help **********************
smallest = Cells(startValRow, startValCol).Value
For counter = startRow To endRow

hold = ThisWorkbook.Sheets("Sheet1").Cells(counter,
startCol).Value
hold_first = Abs(hold - Cells(startValRow, startValCol).Value)
smallest = Cells(startValRow, startValCol).Value

For row = startValRow To endValRow
For col = startValCol To endValCol
temp = Abs(hold - Cells(row, col).Value)

If temp <= hold_first Then
smallest = Cells(row, col).Value
hold_first = temp
End If
Next col
Next row

mytype.result(counter - 1) = smallest
Next counter

For showResult = startRow To endRow
Debug.Print result(showResult)
ThisWorkbook.Sheets("Sheet1").Cells(showResult + 1,
destCol).Value = result(showResult)
Next showResult
End Sub

 

[Nasir.Munir]

....and if I dont declare result array up there and use it directly to
store values (down in the code), then i get "sub or function not
defined" I have also tried "type", didnt work either

 

[Jim Rech]

Dim result(0 To endCol - 1) As Integer

--
Jim
| ...and if I dont declare result array up there and use it directly to
| store values (down in the code), then i get "sub or function not
| defined" I have also tried "type", didnt work either
|
|
| On Feb 8, 11:28 am, (e-mail address removed) wrote:
| > Can anyone help me out with this please, I get the error "statement
| > invalid outside type block"
| >
| > Sub minNumber()
| >
| > Sheet = UserForm1.TextBox1.Text ' getting values from form.
| > startRow = UserForm1.TextBox2.Text
| > startCol = UserForm1.TextBox7.Text
| > endCol = UserForm1.TextBox11.Text
| > startValRow = UserForm1.TextBox12.Text
| > startValCol = UserForm1.TextBox4.Text
| > endValCol = UserForm1.TextBox13.Text
| > endValRow = UserForm1.TextBox3.Text
| > destCol = UserForm1.TextBox10.Text
| >
| > result(0 To endCol - 1) As Integer ************* thats the problem
| > line, need help **********************
| > smallest = Cells(startValRow, startValCol).Value
| > For counter = startRow To endRow
| >
| > hold = ThisWorkbook.Sheets("Sheet1").Cells(counter,
| > startCol).Value
| > hold_first = Abs(hold - Cells(startValRow, startValCol).Value)
| > smallest = Cells(startValRow, startValCol).Value
| >
| > For row = startValRow To endValRow
| > For col = startValCol To endValCol
| > temp = Abs(hold - Cells(row, col).Value)
| >
| > If temp <= hold_first Then
| > smallest = Cells(row, col).Value
| > hold_first = temp
| > End If
| > Next col
| > Next row
| >
| > mytype.result(counter - 1) = smallest
| > Next counter
| >
| > For showResult = startRow To endRow
| > Debug.Print result(showResult)
| > ThisWorkbook.Sheets("Sheet1").Cells(showResult + 1,
| > destCol).Value = result(showResult)
| > Next showResult
| > End Sub
|
|

 

[Nasir.Munir]

Thanks Jim, but now the error is "constant expression required" and it
points at "endCol"

 

[Dave Peterson]

This worked for me:

Dim endCol As Long
Dim Result() As Long
'...
endCol = 5
ReDim Result(0 To endCol - 1)