Passing variables between routines

[tmyers]

This is something I have read up on but still have not yet figured out how to correctly implement.

In this particular case, I am trying to make a module for my file dialog window that I can just call whenever I want a file picked in a routine.

So if my module is:

Public Sub FilePicker()
Dim fd        as Object
Dim strfile  as String
Set fd = Application.FileDialog(3)
  
With fd
    .allowmultiselect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"
    If .Show Then
        strfile = .selecteditems(1)
    End If
End With

How do I handle passing strfile back to the main procedure?

 

[CJ_London]

Change from sub to function and declare it as string

before exiting assign strfile to file picker

function filepicker() as string
…
…
….
filepicker=strfile
End function

and to call

myfilepath=filepicker

If you want to keep as a sub pass strfile as a parameter

sub filepicker (strfile as string)

and to call

fiilepicker(myfilepath)

 

[MarkK]

Public Function GetFilePickerExcelFile() As String
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*"
        If .Show Then GetFilePickerFile = .SelectedItems(1)
    End With
End With

 

[Gasman]

Public Function GetFilePickerExcelFile() As String
    With Application.FileDialog(3)
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*"
        If .Show Then GetFilePickerFile = .SelectedItems(1)
    End With
End With

@MarkK
You normally see code for a function as per post #2, a local variable is assigned the final result, and then the function name is assigned to that local variable.

You have not done that here, and I have always wondered why it was done that way I have just mentioned?

 

[MarkK]

@Gasman, Do you mean you've wondered why people typically declare the variable? I don't know, style, history. Mostly if you see VBA other people wrote, they declare the variables, and so it becomes a convention.

But in this case, eliminating the declarations and one-lining the if block we go from 10 to 5 lines. That's pretty good compression, and, I think, worth noting the option to do it that way. Maybe helps people see other choices they have in code.

And this line should be....

If .Show Then GetFilePickerExcelFile = .SelectedItems(1)

 

[Gasman]

@Gasman, Do you mean you've wondered why people typically declare the variable? I don't know, style, history. Mostly if you see VBA other people wrote, they declare the variables, and so it becomes a convention.

But in this case, eliminating the declarations and one-lining the if block we go from 10 to 5 lines. That's pretty good compression, and, I think, worth noting the option to do it that way. Maybe helps people see other choices they have in code.

And this line should be....

If .Show Then GetFilePickerExcelFile = .SelectedItems(1)

Yes, exactly like that.
I know there is a reason where you set db = CurrentDB() and not use CurrentDB() instead everywhere (other than less typing ), but cannot remember it now.

 

[CJ_London]

have always wondered why it was done that way I have just mentioned?

I do use both methods - I also use the sub method both ways as well. It just depends on the requirement. If you are iterating through something so the value might change and you hit an error, you can end up passing back a value which is not correct. So assigning to a variable and then at the end assigning it to the function is probably a bit safer. But agree with Markk probably a relic from history.

Bit like using abc$ and 123# to state the values are strings or longs respectively, can still use them but not required

is a reason where you set db = CurrentDB() and not use CurrentDB()

definitely yes. Currentdb refreshes every time you use it, db doesn't. Which is why you can return a .recordsaffected value with db and not currentdb - the refresh kills the return value

 

[Galaxiom]

For the sake of the discussion I'll just mention it is possible to pass values back to the call via the parameters in a Sub or Function.
In the most simplistic case.

Sub Whatever(ByRef intN As Integer)
intN = 2
End Sub

In this case, any variable passed to the sub as the argument will end up having the value 2 after the sub is called.

ByRef means the parameter is passed to the sub as a reference rather than the actual value as in ByVal, so any change to the parameter inside the sub will be reflected in the argument variable.

Also note that in VBA, ByRef is the default if not declared in a Sub or Function.

BTW As a Sub it must be called:

 Whatever intVariable

not

Whatever (intVariable)

In the second case, the presence of the parentheses would cause the parameter to behave as ByVal.

This is different from the parentheses in a Function call.
That difference can be very confusing to new developers, especially the first time they pass an object to a Sub.

 

[tmyers]

Thank you for your help guys! I got it working. I dont know what I struggle so much trying to handle passing things around.