Public variable for captura path in vba excel

0 votes

I need to record the path of the excel file in a public VBA excel variable. I have a macro that utilises the path several times across various modules. My issue arises when the variable is declared in the Option Explicit Contex.

`Option Explicit Public ruta As String

ruta = ActiveWorkbook.Path

Public Const varRuta = ruta & "\ValidacionesSunat"

Sub EdufarmaEstadoRevisado()

the variables you must use in the code TextFile Should Be A Number I, J, K, L, and M, As Integer String: MyFile Dim Dark Zone Low values The same dim batch Set batchDe10 to Set batchSaldoUnits to 

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

Jan 22, 2023 in Others by Kithuzzz
• 38,000 points
695 views

1 answer to this question.

0 votes

An assignment statement (as ruta = ActiveWorkbook) cannot be used. Outside of a Sub() or Function is the Path ()

Therefore, take into account the following adjustments to your ruta-related variables along with a few more recommendations/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

Please be aware that any missing type declarations right after a variable may cause the VBA interpreter to treat the variable as being of type "Variant" with regard to any comments or suggestions regarding Dim.

answered Jan 22, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How can I set a number for a public variable at userforms in excel?

Try: Public b As Integer Private Sub exa_Click() b = ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
485 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,000 points
1,323 views
0 votes
1 answer
0 votes
1 answer

How Do I Correct a ByRef Mismatch Error in VBA for Worksheet_Change Sub in Excel?

Len of a string returns what you need. ...READ MORE

answered Apr 1, 2023 in Others by narikkadan
• 63,600 points
1,910 views
0 votes
1 answer

Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
630 views
0 votes
0 answers

Strikethrough in Excel VBA

When I enter the second date in ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 38,000 points
543 views
0 votes
1 answer

Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
480 views
0 votes
1 answer

I want to compare two Excel files and highlight the differences with VBA

The workbook doesn't have the UsedRange property ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,600 points
2,611 views
+1 vote
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
1,733 views
0 votes
1 answer

How to get the excel file name / path in VBA

Use FullName, for example: strFileFullName = ThisWorkbook.FullName ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,600 points
591 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP