Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > statement invalid outside type block
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default statement invalid outside type block
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default statement invalid outside type block
....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, 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,718
Default statement invalid outside type block
Dim result(0 To endCol - 1) As Integer

--
Jim
wrote in message
ups.com...
| ...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, 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
|
|


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default statement invalid outside type block
Thanks Jim, but now the error is "constant expression required" and it
points at "endCol"

On Feb 8, 11:41 am, "Jim Rech" wrote:
Dim result(0 To endCol - 1) As Integer

--
wrote in message

ups.com...
| ...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, 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
|
|


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default statement invalid outside type block
This worked for me:

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



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

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with VBA Type statement Rednosebob Excel Programming 4 February 6th 06 10:49 AM
Old Format or Invalid Type Library David Excel Programming 2 November 1st 05 11:39 AM
HELP:old format or invalid type library Iliopoulos Dimitris Excel Programming 0 June 2nd 04 06:01 PM
Why does this fail with an invalid type error Benkong2 Excel Programming 3 April 18th 04 01:59 PM
Need help in excel with "Statement invalid outside Type block. " error Brent[_6_] Excel Programming 3 January 17th 04 03:03 AM


All times are GMT +1. The time now is 08:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement