0

I have a variable filename, defined within one of the sub routines. How can i use it globally elsewhere in any other sub-routine within the same module

Sub database_create()
    Dim FileFormat As String
    Dim LastRow As Long
    Dim i As Integer
    'Find the last row.
    With shPaths
        LastRow = .Cells(.Rows.count, "B").End(xlUp).Row
    End With
'    For i = 2 To LastRow
    For i = 2 To 2
    conversion_importexcel Cells(i, 3).Value

    Next
    'Inform the user that conversion finished.
    MsgBox "All files were converted successfully!", vbInformation, "Finished"
End Sub


Sub conversion_importexcel(OutPath As String)
Dim lRow As Long
Dim lCol As Long
Dim fNameAndPath As Variant
Dim filename As Variant
Dim convtexcelpath As String
Dim frstwrd As String
'convtexcelpath = WorksheetFunction.Substitute(OutPath, ".pdf", "_adobeConverted" & "." & LCase(FileExtension))
convtexcelpath = WorksheetFunction.Substitute(OutPath, ".pdf", "_Converted" & "." & "xlsx")

fNameAndPath = convtexcelpath
If fNameAndPath = False Then Exit Sub
Workbooks.Open filename:=fNameAndPath

filename = fNameAndPath
filename = (Replace(Mid(filename, InStrRev(filename, "\") + 1), ".xlsx", "") & "_database")
end sub
isomericharsh
  • 83
  • 7
  • Your question is answered [here](https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) but I suggest you also read the [documentation on scope](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility). – BigBen May 11 '19 at 22:29
  • @BigBen but filename get created within "conversion_importexcel" and will keep on changing basis the loop values in "database_create". Filename thus generated will be used globally across other modules being called in "Conversion_importexcel".I tried declaring it outside with Public. New to VBA, here. How do we deal in this case – isomericharsh May 11 '19 at 22:39
  • It's not exactly clear why you need a variable with module-level scope, but the article explains what you need to do, and what scope is. – BigBen May 11 '19 at 22:45

1 Answers1

1

You can declare them at the top of the module that way you can use them through all your procedures in that module.

Option Explicit
Global FileName as String
Global i as Long

Sub database_create()
'some code here
End Sub
QuickSilver
  • 730
  • 5
  • 28