VBA Type Mismatch (Conditional Compiling)

Question

My Dim statements are not working: there is an error mesasge:

Compile error: Type mismatch

I am conditionally compiling a VBA macro which uses a lot of algebra, a lot of numbers, and a lot of files, so execution time will be massive unless I tweak the data types. It is best to allow the user to do this, based on the speed of the user's computer and how much time is available.

Here is the code:

' Develop fake data to at glance recognize whether program works.
' Source http://www.cpearson.com/excel/optimize.htm
Option Explicit

Sub Function1(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 = "Long" Or "long" Then
        Dim RangeNOut As Long, vRangeNIn As Long, Ind6 As Long, Ind4 As Long, Ind5 As Long
        Dim Step2 As Long, MRow As Long, ColIn As Long, Ind3 As Long, Mcol As Long
        Dim MxRNo As Long, BgrSum As Long, RowIn As Long, Ind As Long, M40eff As Long, Step As Long
        Dim ColNo As Long, Startcol As Long, Startrow As Long, MeanComp As Long
        Dim PlateNo As Long, MonoVal As Long, Ind1 As Long, EntryRow2 As Long, EntryRow As Long
        Dim Ind2 As Long, BgrValP As Long, BgrRow As Long, M40eff As Long
        Dim BrgSum As Long, BgrVal As Long, RangeNIn As Long, RangeNOut As Long, TLCorn As Long
        Dim Volcorr As Long, BRCorn As Long, MEeff As Long, MediaVal As Long

    #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

The line which causes the type mismatch error message:

#If VarType = "Long" Or "long" Then

Answer 1

The type mismatch is that the compiler is expecting a Const - it has nothing do with the values you're checking against. The variable VarType isn't a constant, so this code won't work as structured currently. There simply isn't a way to select how variables are declared at runtime - that's what Variants are for. If you need to do this pre-compile, you need to declare a constant, change the constant to the value you want to compile with, then recompile. For example:

Option Explicit

#Const VarType = "Long"

Sub Function1()

    #If VarType = "Long" Then
        Dim RangeNOut As Long, vRangeNIn As Long, Ind6 As Long, Ind4 As Long, Ind5 As Long
        Dim Step2 As Long, MRow As Long, ColIn As Long, Ind3 As Long, Mcol As Long
        Dim MxRNo As Long, BgrSum As Long, RowIn As Long, Ind As Long, M40eff As Long, Step As Long
        Dim ColNo As Long, Startcol As Long, Startrow As Long, MeanComp As Long
        Dim PlateNo As Long, MonoVal As Long, Ind1 As Long, EntryRow2 As Long, EntryRow As Long
        Dim Ind2 As Long, BgrValP As Long, BgrRow As Long, M40eff As Long
        Dim BrgSum As Long, BgrVal As Long, RangeNIn As Long, RangeNOut As Long, TLCorn As Long
        Dim Volcorr As Long, BRCorn As Long, MEeff As Long, MediaVal As Long
    #ElseIf VarType = "Double" Or "double" Then
        '....
        '....
        '....
        '....
    #End If

If you need to be able to do this at runtime, you'll have to write a distinct function for each type and select between them with standard If or Select syntax, not precompiler switching.