Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
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 want to use a global variable (cmb) in a function (CommandButton2_Click) to copy columns from one workbook to another using VBA.
This is my code:
Dim wb As Workbook
Dim cmb As String
Private Sub ComboBox1_Change()
Cell As Range, rng As Range, sht As Worksheet
Set cmb = Form.ComboBox1.Value
Set sht = wb.Worksheets(cmb)
'assuming your headers are always on the first row...
Set rng = sht.Range(sht.Range("A1"), _
sht.Cells(1, Columns.Count).End(xlToLeft))
'add some code here to clear the lists first!...
For Each Cell In rng.Cells
If Len(Cell.Value) > 0 Then
Form.ComboBox2.AddItem (Cell.Value)
Form.ComboBox3.AddItem (Cell.Value)
Form.ComboBox4.AddItem (Cell.Value)
Form.ComboBox5.AddItem (Cell.Value)
Form.ComboBox6.AddItem (Cell.Value)
Form.ComboBox7.AddItem (Cell.Value)
Form.ComboBox8.AddItem (Cell.Value)
Form.ComboBox9.AddItem (Cell.Value)
Form.ComboBox10.AddItem (Cell.Value)
Form.ComboBox11.AddItem (Cell.Value)
Form.ComboBox12.AddItem (Cell.Value)
Form.ComboBox13.AddItem (Cell.Value)
End If
Next Cell
End Sub
Private Sub CommandButton1_Click()
Dim sFilePath As String
sFilePath = Application.GetOpenFilename()
Set wb = Workbooks.Open(sFilePath)
For Each sht In wb.Worksheets
Form.ComboBox1.AddItem sht.Name
Next sht
End Sub
Private Sub CommandButton2_Click()
'Copy Column from one workbook to another
Dim sourceColumn As Range, targetColumn As Range
Set sourceColumn = wb.Worksheets(cmb).Columns(Form.ComboBox2.Value)
Set targetColumn = ActiveWorkbook.ActiveSheet.Columns("PART NUMBER")
sourceColumn.Copy Destination:=targetColumn
End Sub
I’m getting a Compile Error: Statement invalid outside Type Blockerror at Private Sub ComboBox1_Change(). I wonder why though. I have declared the variable cmb as global. Why is the variable not in scope?
asked Sep 28, 2017 at 18:56
4
I believe it has to be:
Public wb as Workbook
Public cmb as String
and:
Private Sub ComboBox1_Change()
Dim Cell As Range, rng As Range, sht As Worksheet
answered Sep 28, 2017 at 18:59
Victor KVictor K
1,0492 gold badges10 silver badges21 bronze badges
4
-
- #1
Hi,
I am trying to have the worksheet names updated when a specific cell is changed.
I found on the internet the following code:Private Sub Worksheet_Change() If Not Intersect(Target, Range("AD6")) Is Nothing Then Call TabNames End If End SubAnd here is my TabNames macro:
Sub TabNames() TabName1 As String TabName2 As String TabName3 As String TabName4 As String Set TabName1 = Sheets(1).Range("AD7").Value Set TabName2 = Sheets(2).Range("AD7").Value Set TabName3 = Sheets(3).Range("AD7").Value Set TabName4 = Sheets(6).Range("AD7").Value ActiveWorkbook.Sheets(1).Activate ActiveSheet.Name = TabName1 ActiveWorkbook.Sheets(2).Activate ActiveSheet.Name = TabName2 ActiveWorkbook.Sheets(3).Activate ActiveSheet.Name = TabName3 ActiveWorkbook.Sheets(6).Activate ActiveSheet.Name = TabName4 End SubDisplay More
When I test the TabNames macro and run it with F8, I am getting the statement invalid outside type block error message.
Can someone help??
-
- #2
Re: Statement Invalid Outside Type Block
There are a couple of things missing/wrong with that code.
First you need to use Dim in front of every variable declaration.
Dim TabName1 As String Dim TabName2 As String Dim TabName3 As String Dim TabName4 As StringSecondly you only use Set with object variables, and Strings aren’t object variables.
Another thing is that you don’t actually need to activate sheets to rename them.
This would suffice.
ActiveWorkbook.Sheets(1).Name = TabName1 -
- #3
Re: Statement Invalid Outside Type Block
tblondet, please use code tags as you agreed,
-
#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]How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
-
#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.
