excel – VBA Compile Error: Statement invalid outside Type Block
You have:
Dim RangeNOut as Double
Dim RangeNOut as Integer
While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesnt do conditional compilation since it isnt a compiled language. When VBA runs your code, all your variables are declared (no matter where in the code the Dim statement is located), then the code begins executing.
Its a nice idea youve come up with, but attempting this in VBA is like bringing a piece of Silly Putty™ to a gun fight – its just horribly unequipped for the job.
Also, if youre that concerned about execution speed, VBA isnt your weapon of choice, either. I dont have any stats to back it up off the top of my head, but I doubt youd actually see much difference in execution speed based on your three different variable types.
To pass the variable type as a parameter to the function, use this:
Private Sub Function1(ByVal VarType as String)
#If VarType = Double then
...
#ELSEIF VarType = Single then
...
#ELSEIF VarType = Integer then
...
#ELSE
MsgBox You passed in a VarType of & VarType & - thats not valid
#ENDIF
Also, I just noticed that in your final Else you have Goto Function1. Im not sure what youre trying to accomplish there, but:
- Dont use
goto. Except in VBA style error handling, its almost never necessary - You dont have a label defined for the
Gototo jump to, anyway.
See also VBA function overloading for another possible option.
Notice: Despite the upvotes and accepted answer status, I tried the following, and it DOES NOT work as requested by OP:
Sub test()
func Double
func Single
func Integer
func String
End Sub
Function func(v As String)
#If v = Double Then
Dim myvar As Double
Range(A1) = MyVar type is: & vartype(v)
#ElseIf v = Single Then
Dim myvar As Single
Range(a2) = MyVar type is: & vartype(v)
#ElseIf v = Integer Then
Dim myvar As Integer
Range(a3) = MyVar type is: & vartype(v)
#Else
Range(A4) = Invalid var type passed: & v
#End If
MsgBox Passed in & v
End Function
All calls to Func() end up in the #Else section of code, populating Range(A4) with the Invalid var type passed: text.
Sadly, this will not work.
If it is truly necessary to have functions with different variable types doing the exact same thing, I think the following would be the best bet:
Sub Test()
Dim VType as String
While Vtype <> Integer and VType <> Double and VType <> Single and VType <> Cancel
vType = msgBox(Enter variable type)
Wend
If VType = Integer then
MyFuncInt()
ElseIf VType = Double then
MyFuncDouble()
Elseif VType = Single
MyFuncSingle()
Else
MsgBox Function call cancelled
End if
End Sub
Function MyFuncInt()
Dim AllTheVars as Integer
...
End Function
Function MyFuncDouble()
Dim AllTheVars as Double
...
End Function
Function MyFuncSingle()
Dim AllTheVars as Single
...
End Function
excel – VBA Compile Error: Statement invalid outside Type Block
-
#1
I’m getting the error in the title of the thread and VBA highlights the time_lastcell2 as the culprit.
Code:
Dim first_cell As Integer
Dim time2 As Worksheet
Set time2 = ActiveWorkbook.Sheets("time")
Dim lastday As Date, lastday_time As Date
time_lastcell2 As Integer
With time2
time_lastcell2 = .Range("e65000").End(xlUp).Row
End With
lastday_time = time2.Range("e" & time_lastcell).Address
'Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With ActiveSheet
first_cell = .Range("c65000").End(xlUp).Row
lastday = .Range("b" & first_cell).Address
[more code]Workdays for a market open Mon, Wed, Friday?
Yes! Use «0101011» for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
-
#2
kylefoley76,
Missing Dim….
Dim time_lastcell2 As Integer
-
#3
Missing Dim….
Dim time_lastcell2 As Integer
Also, since it will hold a row number, I would suggest declaring it as a Long instead of as an Integer…
Dim time_lastcell2 As Long
-
#4
Thanks that did it. Also Rick, about the fact that I thought you didn’t like one-liners expressing gratitude. I thought I read that on excelfox but I went back to look for it and I couldn’t find it.
|
|
Statement Invalid outside TypeBlock error
Originally Posted by Frodo_Baggins
-
06-04-2010, 04:15 PM
#1
Registered User
error:Statement invalid outside type block
I am trying to sort a number of columns in my worksheet based on the Cell Color. The columns have variable length of rows.
I have two questions:
1. When I run my current code in debug mode then I get the error: «Statement invalid outside type block». I am not sure which parantheses I should remove to get rid of the error.
2.Ssecondly I think my current code is not going to do the sort that I would like it to do. Can anyone please suggest what changes I should make in my code to get it working.Below I have pasted a sample of my sheet as a link:
http://picasaweb.google.com/manishab…14238835510658
-
06-04-2010, 04:20 PM
#2
Re: error:Statement invalid outside type block
1. Which line gives you the error?
2. Why post a picture, rather than a sample workbook? It would also help if you explained why you think the sort won’t do what you want, as well as what you actually do want.
Remember what the dormouse said
Feed your head
-
06-04-2010, 05:06 PM
#3
Registered User
Re: error:Statement invalid outside type block
Originally Posted by romperstomper
1. Which line gives you the error?
2. Why post a picture, rather than a sample workbook? It would also help if you explained why you think the sort won’t do what you want, as well as what you actually do want.
Thanks romperstomper for replying.
1. I was able to solve the first problem of the error: Statement invalid outside type block just a few minutes back. I had forgotten to add Dim infront of myRange
2. I need help with the second part that is sort the rows in each column according to color such that the Red colored cells are on the top and the blue colored on the bottom for each column.
I pasted a link of the picture of the worksheet to give an example of the format of the data I am dealing with. This time I attach the worksheet to this post for convinience of the readers.
below is also the edited code that doesnot throw the Statement invalid outside type block error
-
06-04-2010, 05:28 PM
#4
Re: error:Statement invalid outside type block
-
06-04-2010, 07:11 PM
#5
Registered User
Re: error:Statement invalid outside type block
thanks a lot!!!!! It worked fine. thanks for your help. My code was quite complicated.
Originally Posted by romperstomper
Originally Posted by romperstomper