Variable value not passing from function

cmf30

New Member
Joined
Apr 27, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
Hi,

I am a complete VBA beginner so I expect this a very easy question. In the below am trying to check if a file exists. In the below when I check the value of bExists using a Msgbox at the end of the function, the value is true and this is correct. However, if I at the end of the subprocedure as below it returns false. Why is the value of the variable not being passed back to sub?

Thank you in advance for your help.


VBA Code:
Sub CheckandOpen()

Dim bExists As Boolean
Dim strFullPath As String
Dim strFileName As String

    strFullPath = "Desktop/Excel Test Folder/HRIS.xls."
    strFileName = Dir(strFullPath)
    bExists = CheckExists(strFileName)
    MsgBox (bExists)

End Sub

Private Function CheckExists(strFileName) As Boolean

    If strFileName = "" Then
        bExists = False
    Else
        bExists = True
    End If

End Function
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
How about something like:

VBA Code:
Sub CheckandOpen()

Dim bExists As Boolean
Dim strFullPath As String

strFullPath = "C:\Users\Steve\Desktop\Book1.xlsx"
bExists = CheckExists(strFullPath)
MsgBox bExists

End Sub

Private Function CheckExists(strFullPath As String) As Boolean

If Dir(strFullPath) = "" Then
    CheckExists = False
Else
    CheckExists = True
End If

End Function
 

cmf30

New Member
Joined
Apr 27, 2020
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
Hi Steve,

Thank you very much for that - it worked.

Kind regards,
Claire
 

Watch MrExcel Video

Forum statistics

Threads
1,130,208
Messages
5,640,853
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top