Public variable for captura path in vba excel-CodePudding

I have to capture excel file path in a public variable in VBA excel. I have a macro that use the path in several time, in some modules. My problem is when declare the variable in Option Explicit contex.

Option Explicit
Public ruta As String

ruta = ActiveWorkbook.Path

Public Const varRuta = ruta & "\ValidacionesSunat\"

Sub EdufarmaEstadoRevisado()

'variables that you need to use in the code
Dim TextFile As Integer
Dim i, j, k, l, m As Integer
Dim myFile As String
Dim zona
Dim valores As Long
Dim batch
Dim batchDe10
Dim batchSaldoUnidades


On Error Resume Next
Kill varRuta & "*.*"
On Error GoTo 0

enter image description here

CodePudding user response：

you cannot use an assignment statement (as ruta = ActiveWorkbook.Path is) outside a Sub() or Function()

so consider the following tweaking of your ruta related variables along with some other suggestions/pieces of advice

Option Explicit

Public varRuta As String

Public Const varRutaLastBit = "\ValidacionesSunat"

Sub EdufarmaEstadoRevisado()

    varRuta = ActiveWorkbook.Path & varRutaLastBit
    
    'variables that you need to use in the code
    Dim TextFile As Integer
    Dim iCol As Long '<-- use Long instead of Integer
    Dim myRange As Range
    Dim cVal As Range
    Dim i As Long, _
        j As Long, _
        k As Long, _
        l As Long, _
        m As Long
    Dim myFile As String
    Dim zona
    Dim valores As Long
    Dim batch '<-- implicitly assumed As Variant
    Dim batchDe10 '<-- implicitly assumed As Variant
    Dim batchSaldoUnidades
    
    On Error Resume Next
    Kill varRuta & "."
    On Error GoTo 0

End Sub

as to the Dim related comments/suggestions, please be informed that whatever missing type declaration right after a variable would have the VBA interpreter assume that variable as of "Variant" type

Dim i, j, k, l, m As Integer
...
Dim zona

would be taken by the VBA interpreter as:

Dim i As Variant, j As Variant, k As Variant, l As Variant, m As Integer
...
Dim zona As Variant