Compile error statement invalid outside type block

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

hi,

I have the following code in a module:

Public Function ExportToExcel(TableName As String, FilePathname As String, _
Optional SheetName As String) As Boolean

Dim oRS As Object, oExcelApp As Object, lngFieldCounter As Long
Dim blnFileExists As Boolean, blnExcelRunning As Boolean, oTargetSheet As Object

On Error GoTo errHandler

‘Firstly, open the recordset. The TableName argument can be either a table name or
‘a valid SQL statement.
Set oRS = CreateObject(«DAO.Recordset»)
oRS.Open TableName, CurrentDb.Connection, 0, 1

‘Get an instance of Excel. Use a running instance if one exists or create one if not.
On Error Resume Next
Set oExcelApp = GetObject(, «Excel.Application»)
If Err.Number <> 0 Then
blnExcelRunning = False
Set oExcelApp = CreateObject(«Excel.Application»)
Else
blnExcelRunning = True
End If
On Error GoTo errHandler

‘Now see if the specified file exists or create it if not.
If Dir(FilePathname) <> «» Then
blnFileExists = True
oExcelApp.Workbooks.Open Filename:=FilePathname
Else
oExcelApp.Workbooks.Add
End If

‘Get a reference to the sheet we’re going to dump the data into. If it already exists
‘then use that, otherwise add a sheet and name it.

If IsEmpty(SheetName) = False Then

On Error Resume Next
Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets(SheetName)

If Err.Number <> 0 Then
Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets.Add
oTargetSheet.Name = SheetName
End If

Else

Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets.Add

End If

On Error GoTo errHandler

‘This loop will place the recordset field names into row 1 of the worksheet
For lngFieldCounter = 1 To oRS.Fields.Count
oTargetSheet.Cells(1, lngFieldCounter) = oRS.Fields(lngFieldCounter — 1).Name
Next lngFieldCounter

oTargetSheet.Range(«A2»).CopyFromRecordset oRS
oRS.Close

‘Now save the Excel workbook and clean up
If blnFileExists Then
oExcelApp.ActiveWorkbook.Save
Else
oExcelApp.ActiveWorkbook.SaveAs Filename:=FilePathname
End If

oExcelApp.ActiveWorkbook.Close

If Not blnExcelRunning Then
oExcelApp.Quit
Set oExcelApp = Nothing
End If

Set oRS = Nothing
ExportToExcel = True
Exit Function

errHandler:
ExportToExcel = False
End Function

————
I use the following to call the function:
Sub ExportCMStatsToExcelSheet_Click()

ExportToExcel(«tblCMCode», «C:CMCodeStats.xls», «CMCodeStats») As Boolean

End Sub

When I compile it, I am getting a «Statement Invalid Outside Type Block».
Does anyone know how to fix this? Please?

Thank you in advance…

NeoPa: Your Do…Loops, and If…End Ifs, and With…End Withs do not balance up correctly.

You need to get these sorted before continuing, and certainly before it will work.

Welcome to Bytes!

I would add :

1. Line #75 closes CurrentDB. Not a good idea. If your code opens an object then it should close it. If it finds it already open, then it should leave it open. There may be exceptions to this rule, but they’re rare.
2. Line #81 is probably the offending property. You want Err.Description.
   Remember next time to include the line number though please.

У меня есть приведенный ниже код, который объединяет/объединяет все повторяющиеся строки с одним и тем же текстом в столбцах 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'