Creating a procedure that takes in a variable then returns another to the calling procedure in Excel VBA

Question

I want to create functionality that will allow me to pass a variable to a procedure, have it open a file based on that variable, and then return the filepath into calling procedure. I have the code for opening the file etc, but as there are multiple places in the procedure where it could be called, I don't want it to be there, but just to return the filepath into a variable (which can then be used to load fields from the opened file).

The code to load the file that I am using is below, how would I convert this to a procedure to do what I need?:

        'Open the file
        NameOfFile = ActiveWorkbook.Worksheets("Parameters").Cells(8, 2).Value
        PathToFile = Left$(NameOfFile, InStrRev(NameOfFile, "\") - 1)
        FileNameNoPath = Mid$(NameOfFile, InStrRev(NameOfFile, "\") + 1)
        NameOfFile = FileNameNoPath
        CompleteFilePath = PathToFile & "\" & NameOfFile

        On Error Resume Next
        Set File1 = Workbooks(NameOfFile)

        If Err.Number <> 0 Then
        'Open the workbook
          Err.Clear
          Set File1 = Workbooks.Open(CompleteFilePath, UpdateLinks:=False)
          CloseIt = True
        End If

        'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "File is missing, please check your path!" _
            & vbNewLine & NameOfFile
            Exit Sub
        End If
        On Error GoTo 0

score 2 · Accepted Answer · answered Feb 01 '13 at 09:04

2

You mean like this?

Option Explicit

Dim FilePath As String

Sub Sample()
    Dim FileToOpen As String

    FileToOpen = "C:\Temp\Sample.xlsx"

    OpenFile FileToOpen

    Debug.Print FilePath
End Sub

Sub OpenFile(strFile As String)
    FilePath = Left$(strFile, InStrRev(strFile, "\") - 1)

    '
    '~~> Rest of the code
    '
End Sub

answered Feb 01 '13 at 09:04

Siddharth Rout

147,039
17
206
250

Hmm, not sure if I am following your code- from the looks of it you are defining the flepath in the calling procedure, then opening it elsewhere and thus avoiding needign to pass the file back- or do I have it wrong? – Marc L Feb 01 '13 at 09:12
From what I understood you want the Filepath to be returned from another sub. Is my understanding correct? – Siddharth Rout Feb 01 '13 at 09:13
Umm, I must have explained myself badly. I want to pass the cell reference for the filepath to the calling subroutine- and then return the workbook variable if the file was opened. But looking at it now I see what you have done and it makes sense. Rather just set the workbook variable in the calling procedure and open the file remotely. – Marc L Feb 01 '13 at 09:26

Creating a procedure that takes in a variable then returns another to the calling procedure in Excel VBA

1 Answers1