Permalink
Cannot retrieve contributors at this time
title | keywords | f1_keywords | ms.prod | ms.assetid | ms.date | ms.localizationpriority |
---|---|---|---|---|---|---|
Statement invalid outside Type block |
vblr6.chm1040053 |
vblr6.chm1040053 |
office |
287d4cf7-257a-2cc4-2e5d-42e578c8b862 |
06/08/2017 |
medium |
The syntax for declaring variables outside a Type…End Type statement block is different from the syntax for declaring the elements of the user-defined type. This error has the following causes and solutions:
- You tried to declare a variable outside a Type…End Type block or outside a statement. When declaring a variable with an As clause outside a Type…End Type block, use one of the declaration statements, Dim, ReDim, Static, Public, or Private. For example, the first declaration of
MyVar
in the following code generates this error; the second and third declarations ofMyVar
are valid:
MyVar As Double ' Invalid declaration syntax. Dim MyVar As Double Type AType MyVar As Double ' This is valid declaration syntax End Type ' because it's inside a Type block.
- You used an End Type statement without a corresponding Type statement. Check for an unmatched End Type, and either precede its block with a Type statement, or delete the End Type statement if it isn’t needed.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
[!includeSupport and feedback]
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible.
My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the pros and cons of each data type, the balance between accuracy/flexibility and CPU intensiveness/macro execution time). However, when I run the macro, I get the following error message:
Compile error:
Statement invalid outside Type block
Here is the offending portion of the code:
Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Here is the relevant part of the macro:
' Develop fake data to at glance recognize whether program works.
' Source http://www.cpearson.com/excel/optimize.htm
Option Explicit
Private Sub Function1()
On Error GoTo ErrorHandler
Dim userChoice As Variant
Dim strPath As String, strFileN As String, strDirN As String, strRangeNOut As String, strRangeNIn As String, strFilename As String, strTLCorn As String, strBRCorn As String, strSelectedFile As String, strtemp_name As String
Dim lngCount As Long
Dim vResMatrix(), vCPath, vFileN As Variant
' MEeff = measure of efflux due to crudely purified HDL in scintillation
' https://msdn.microsoft.com/en-us/library/ae55hdtk.aspx
' Give the user macro options based on how fast or slow the computer is
userChoice = MsgBox("This macro by default treats all numbers as doubles for maximum precision. If you are running this macro on an old computer, you may want to redeclare numbers as singles, to speed up the macro." & vbNewLine & "You can also use integers for a quick estimate of data results.")
If userChoice = "Double" Then
Dim RangeNOut As Double, vRangeNIn As Double, Ind6 As Double, Ind4 As Double, Ind5 As Double
Dim Step2 As Double, MRow As Double, ColIn As Double, Ind3 As Double, Mcol As Double
Dim MxRNo As Double, BgrSum As Double, RowIn As Double, Ind As Double, M40eff As Double, Step As Double
Dim ColNo As Double, Startcol As Double, Startrow As Double, MeanComp As Double
Dim PlateNo As Double, MonoVal As Double, Ind1 As Double, EntryRow2 As Double, EntryRow As Double
Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Dim BrgSum As Double, BgrVal As Double, RangeNIn As Double, RangeNOut As Double, TLCorn As Double
Dim Volcorr As Double, BRCorn As Double, MEeff As Double, MediaVal As Double
ElseIf userChoice = "Integer" Then
Dim RangeNOut As Integer, vRangeNIn As Integer, ecInd6 As Integer, Ind4 As Integer, Ind5 As Integer
Dim Step2 As Integer, MRow As Integer, ColIn As Integer, Ind3 As Integer, Mcol As Integer
Dim MxRNo As Integer, BgrSum As Integer, RowIn As Integer, Ind As Integer, M40eff As Integer
Dim Step As Integer, ColNo As Integer, Startcol As Integer, Startrow As Integer, MeanComp As Integer
Dim PlateNo As Integer, MonoVal As Integer, Ind1 As Integer, EntryRow2 As Integer, EntryRow As Integer
Dim Ind2 As Integer, BgrValP As Integer, BgrRow As Integer, M40eff As Integer
Dim BrgSum As Integer, BgrVal As Integer, RangeNIn As Integer, RangeNOut As Integer, TLCorn As Integer
Dim Volcorr As Integer, BRCorn As Integer, MEeff As Integer, MediaVal As Integer
ElseIf userChoice = "Single" Then
Dim RangeNOut As Single, vRangeNIn As Single, ecInd6 As Single, Ind4 As Single, Ind5 As Single
Step2 As Single, MRow As Single, ColIn As Single, Ind3 As Single, Mcol As Single
Dim MxRNo As Single, BgrSum As Single, RowIn As Single, Ind As Single, M40eff As Single, Step As Single
Dim ColNo As Single, Startcol As Single, Startrow As Single, MeanComp As Single
Dim PlateNo As Single, MonoVal As Single, Ind1 As Single, EntryRow2 As Single, EntryRow As Single
Ind2 As Single, BgrValP As Single, BgrRow As Single, M40eff As Single
Dim BrgSum As Single, BgrVal As Single, RangeNIn As Single, RangeNOut As Single, TLCorn As Single
Volcorr As Single, BRCorn As Single, MEeff As Single, MediaVal As Single
Else
GoTo Function1
MsgBox("This is not a supported data type: double, single, or integer.", vbCritical, "Unsupported Data Type")
Here is the code I am currently using for this:
Private Sub Function2(ByVal VarType As String)
Dim mVers As String
Dim userChoice As Variant
' Give the user macro options based on how fast or slow the computer is using advanced conditional compliling
userChoice = MsgBox("This macro by default treats all numbers as doubles for maximum precision. If you are running this macro on an old computer, you may want to relare numbers as singles, to speed up the macro." & vbNewLine & "You can also use integers for a quick estimate of data results.")
userChoice = VarType
#If VarType = "Double" Or "double" Then
Dim RangeNOut As Double, vRangeNIn As Double, Ind6 As Double, Ind4 As Double, Ind5 As Double
Dim Step2 As Double, MRow As Double, ColIn As Double, Ind3 As Double, Mcol As Double
Dim MxRNo As Double, BgrSum As Double, RowIn As Double, Ind As Double, M40eff As Double, Step As Double
Dim ColNo As Double, Startcol As Double, Startrow As Double, MeanComp As Double
Dim PlateNo As Double, MonoVal As Double, Ind1 As Double, EntryRow2 As Double, EntryRow As Double
Dim Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Dim BrgSum As Double, BgrVal As Double, RangeNIn As Double, RangeNOut As Double, TLCorn As Double
Dim Volcorr As Double, BRCorn As Double, MEeff As Double, MediaVal As Double
#ElseIf VarType = "Single" Or "single" Then
Dim RangeNOut As Single, vRangeNIn As Single, ecInd6 As Single, Ind4 As Single, Ind5 As Single
Step2 As Single, MRow As Single, ColIn As Single, Ind3 As Single, Mcol As Single
Dim MxRNo As Single, BgrSum As Single, RowIn As Single, Ind As Single, M40eff As Single, Step As Single
Dim ColNo As Single, Startcol As Single, Startrow As Single, MeanComp As Single
Dim PlateNo As Single, MonoVal As Single, Ind1 As Single, EntryRow2 As Single, EntryRow As Single
Dim Ind2 As Single, BgrValP As Single, BgrRow As Single, M40eff As Single
Dim BrgSum As Single, BgrVal As Single, RangeNIn As Single, RangeNOut As Single, TLCorn As Single
Dim Volcorr As Single, BRCorn As Single, MEeff As Single, MediaVal As Single
#ElseIf VarType = "Integer" Or "integer" Then
Dim RangeNOut As Integer, vRangeNIn As Integer, ecInd6 As Integer, Ind4 As Integer, Ind5 As Integer
Dim Step2 As Integer, MRow As Integer, ColIn As Integer, Ind3 As Integer, Mcol As Integer
Dim MxRNo As Integer, BgrSum As Integer, RowIn As Integer, Ind As Integer, M40eff As Integer
Dim Step As Integer, ColNo As Integer, Startcol As Integer, Startrow As Integer, MeanComp As Integer
Dim PlateNo As Integer, MonoVal As Integer, Ind1 As Integer, EntryRow2 As Integer, EntryRow As Integer
Dim Ind2 As Integer, BgrValP As Integer, BgrRow As Integer, M40eff As Integer
Dim BrgSum As Integer, BgrVal As Integer, RangeNIn As Integer, RangeNOut As Integer, TLCorn As Integer
Dim Volcorr As Integer, BRCorn As Integer, MEeff As Integer, MediaVal As Integer
#Else
MsgBox "VarType " & VarType & " is not valid. Check spelling."
#End If
' MEeff = measure of efflux due to crudely purified HDL in scintillation
MsgBox "For additional information about this macro:" & vbNewLine & "1. Go to tab Developer" & vbNewLine & "2. Select Visual Basic or Macro." & vbNewLine & "See the comments or MsgBoxes (message boxes)."
' Start File Explorer to select file containing data (simple GUI, much easier than coding in the file)
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
Next lngCount
For Each strFilename In .SelectedItems
MsgBox strFilename
Function2
Next
End With
ErrorHandler:
MsgBox "Error detected" & vbNewLine & "Error" & Err.Number & ": " & Err.Description, vbCritical, "Error Handler: Error " & Err.Number
MsgBox "If you want to force the program to run, go to the line below and insert a ' mark to comment the line out." & vbNewLine & "On Error GoTo ErrorHandler", vbCritical, "Error Handler: Error " & Err.Number
End Sub
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible.
My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the pros and cons of each data type, the balance between accuracy/flexibility and CPU intensiveness/macro execution time). However, when I run the macro, I get the following error message:
Compile error:
Statement invalid outside Type block
Here is the offending portion of the code:
Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Here is the relevant part of the macro:
' Develop fake data to at glance recognize whether program works.
' Source http://www.cpearson.com/excel/optimize.htm
Option Explicit
Private Sub Function1()
On Error GoTo ErrorHandler
Dim userChoice As Variant
Dim strPath As String, strFileN As String, strDirN As String, strRangeNOut As String, strRangeNIn As String, strFilename As String, strTLCorn As String, strBRCorn As String, strSelectedFile As String, strtemp_name As String
Dim lngCount As Long
Dim vResMatrix(), vCPath, vFileN As Variant
' MEeff = measure of efflux due to crudely purified HDL in scintillation
' https://msdn.microsoft.com/en-us/library/ae55hdtk.aspx
' Give the user macro options based on how fast or slow the computer is
userChoice = MsgBox("This macro by default treats all numbers as doubles for maximum precision. If you are running this macro on an old computer, you may want to redeclare numbers as singles, to speed up the macro." & vbNewLine & "You can also use integers for a quick estimate of data results.")
If userChoice = "Double" Then
Dim RangeNOut As Double, vRangeNIn As Double, Ind6 As Double, Ind4 As Double, Ind5 As Double
Dim Step2 As Double, MRow As Double, ColIn As Double, Ind3 As Double, Mcol As Double
Dim MxRNo As Double, BgrSum As Double, RowIn As Double, Ind As Double, M40eff As Double, Step As Double
Dim ColNo As Double, Startcol As Double, Startrow As Double, MeanComp As Double
Dim PlateNo As Double, MonoVal As Double, Ind1 As Double, EntryRow2 As Double, EntryRow As Double
Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Dim BrgSum As Double, BgrVal As Double, RangeNIn As Double, RangeNOut As Double, TLCorn As Double
Dim Volcorr As Double, BRCorn As Double, MEeff As Double, MediaVal As Double
ElseIf userChoice = "Integer" Then
Dim RangeNOut As Integer, vRangeNIn As Integer, ecInd6 As Integer, Ind4 As Integer, Ind5 As Integer
Dim Step2 As Integer, MRow As Integer, ColIn As Integer, Ind3 As Integer, Mcol As Integer
Dim MxRNo As Integer, BgrSum As Integer, RowIn As Integer, Ind As Integer, M40eff As Integer
Dim Step As Integer, ColNo As Integer, Startcol As Integer, Startrow As Integer, MeanComp As Integer
Dim PlateNo As Integer, MonoVal As Integer, Ind1 As Integer, EntryRow2 As Integer, EntryRow As Integer
Dim Ind2 As Integer, BgrValP As Integer, BgrRow As Integer, M40eff As Integer
Dim BrgSum As Integer, BgrVal As Integer, RangeNIn As Integer, RangeNOut As Integer, TLCorn As Integer
Dim Volcorr As Integer, BRCorn As Integer, MEeff As Integer, MediaVal As Integer
ElseIf userChoice = "Single" Then
Dim RangeNOut As Single, vRangeNIn As Single, ecInd6 As Single, Ind4 As Single, Ind5 As Single
Step2 As Single, MRow As Single, ColIn As Single, Ind3 As Single, Mcol As Single
Dim MxRNo As Single, BgrSum As Single, RowIn As Single, Ind As Single, M40eff As Single, Step As Single
Dim ColNo As Single, Startcol As Single, Startrow As Single, MeanComp As Single
Dim PlateNo As Single, MonoVal As Single, Ind1 As Single, EntryRow2 As Single, EntryRow As Single
Ind2 As Single, BgrValP As Single, BgrRow As Single, M40eff As Single
Dim BrgSum As Single, BgrVal As Single, RangeNIn As Single, RangeNOut As Single, TLCorn As Single
Volcorr As Single, BRCorn As Single, MEeff As Single, MediaVal As Single
Else
GoTo Function1
MsgBox("This is not a supported data type: double, single, or integer.", vbCritical, "Unsupported Data Type")
Here is the code I am currently using for this:
Private Sub Function2(ByVal VarType As String)
Dim mVers As String
Dim userChoice As Variant
' Give the user macro options based on how fast or slow the computer is using advanced conditional compliling
userChoice = MsgBox("This macro by default treats all numbers as doubles for maximum precision. If you are running this macro on an old computer, you may want to relare numbers as singles, to speed up the macro." & vbNewLine & "You can also use integers for a quick estimate of data results.")
userChoice = VarType
#If VarType = "Double" Or "double" Then
Dim RangeNOut As Double, vRangeNIn As Double, Ind6 As Double, Ind4 As Double, Ind5 As Double
Dim Step2 As Double, MRow As Double, ColIn As Double, Ind3 As Double, Mcol As Double
Dim MxRNo As Double, BgrSum As Double, RowIn As Double, Ind As Double, M40eff As Double, Step As Double
Dim ColNo As Double, Startcol As Double, Startrow As Double, MeanComp As Double
Dim PlateNo As Double, MonoVal As Double, Ind1 As Double, EntryRow2 As Double, EntryRow As Double
Dim Ind2 As Double, BgrValP As Double, BgrRow As Double, M40eff As Double
Dim BrgSum As Double, BgrVal As Double, RangeNIn As Double, RangeNOut As Double, TLCorn As Double
Dim Volcorr As Double, BRCorn As Double, MEeff As Double, MediaVal As Double
#ElseIf VarType = "Single" Or "single" Then
Dim RangeNOut As Single, vRangeNIn As Single, ecInd6 As Single, Ind4 As Single, Ind5 As Single
Step2 As Single, MRow As Single, ColIn As Single, Ind3 As Single, Mcol As Single
Dim MxRNo As Single, BgrSum As Single, RowIn As Single, Ind As Single, M40eff As Single, Step As Single
Dim ColNo As Single, Startcol As Single, Startrow As Single, MeanComp As Single
Dim PlateNo As Single, MonoVal As Single, Ind1 As Single, EntryRow2 As Single, EntryRow As Single
Dim Ind2 As Single, BgrValP As Single, BgrRow As Single, M40eff As Single
Dim BrgSum As Single, BgrVal As Single, RangeNIn As Single, RangeNOut As Single, TLCorn As Single
Dim Volcorr As Single, BRCorn As Single, MEeff As Single, MediaVal As Single
#ElseIf VarType = "Integer" Or "integer" Then
Dim RangeNOut As Integer, vRangeNIn As Integer, ecInd6 As Integer, Ind4 As Integer, Ind5 As Integer
Dim Step2 As Integer, MRow As Integer, ColIn As Integer, Ind3 As Integer, Mcol As Integer
Dim MxRNo As Integer, BgrSum As Integer, RowIn As Integer, Ind As Integer, M40eff As Integer
Dim Step As Integer, ColNo As Integer, Startcol As Integer, Startrow As Integer, MeanComp As Integer
Dim PlateNo As Integer, MonoVal As Integer, Ind1 As Integer, EntryRow2 As Integer, EntryRow As Integer
Dim Ind2 As Integer, BgrValP As Integer, BgrRow As Integer, M40eff As Integer
Dim BrgSum As Integer, BgrVal As Integer, RangeNIn As Integer, RangeNOut As Integer, TLCorn As Integer
Dim Volcorr As Integer, BRCorn As Integer, MEeff As Integer, MediaVal As Integer
#Else
MsgBox "VarType " & VarType & " is not valid. Check spelling."
#End If
' MEeff = measure of efflux due to crudely purified HDL in scintillation
MsgBox "For additional information about this macro:" & vbNewLine & "1. Go to tab Developer" & vbNewLine & "2. Select Visual Basic or Macro." & vbNewLine & "See the comments or MsgBoxes (message boxes)."
' Start File Explorer to select file containing data (simple GUI, much easier than coding in the file)
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
Next lngCount
For Each strFilename In .SelectedItems
MsgBox strFilename
Function2
Next
End With
ErrorHandler:
MsgBox "Error detected" & vbNewLine & "Error" & Err.Number & ": " & Err.Description, vbCritical, "Error Handler: Error " & Err.Number
MsgBox "If you want to force the program to run, go to the line below and insert a ' mark to comment the line out." & vbNewLine & "On Error GoTo ErrorHandler", vbCritical, "Error Handler: Error " & Err.Number
End Sub
У меня есть приведенный ниже код, который объединяет/объединяет все повторяющиеся строки с одним и тем же текстом в столбцах 1-4 и суммирует значение в столбце 5.
Код работал правильно в течение некоторого времени, но внезапно перестал работать, и я столкнулся с приведенным ниже сообщением об ошибке:
Ошибка компиляции:
Statement invalid outside type block
Любая помощь в решении этого вопроса будет высоко оценена
Sub nSum()
Dim Rng As Range, Dn As Range, n As Long, Txt As String, Ac As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ray(1 To Rng.Count, 1 To 4) 'Column count ''***ERROR LINE***************
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 3))), ",")
If Not .Exists(Txt) Then
n = n + 1
For Ac = 1 To 4: ray(n, Ac) = Dn.Offset(, Ac - 1): Next Ac
.Add Txt, n
Else
ray(.Item(Txt), 4) = ray(.Item(Txt), 4) + Dn.Offset(, 3)
End If
Next
n = .Count
End With
With Sheets("Sheet2").Range("A1").Resize(n, 4)
.Value = ray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub'
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
Goto
to 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