Pass between modules

[Rob]

Hi, I have tried to split a piece of code into separate modules for ease of
knowing what each element does, however, having done this, not all the code
works as expected. I'm work through the issues one by one but I'm stuck on
transferring a file's name that's been saved in module 1 to module 2.

Module 1 creates a new file and saves it with a unique name derived from the
date and time, however, when I then run code in module 2, I'd like to
display after saving this file again its name. I have declared Public
FileName As String in module 1 but regardless what I do in module 2, I can't
capture FileName declare in module 1. I have Option Explicit set in all
modules and subs aren't set to Private.

Any pointers welcome. Thanks, Rob

 

[Per Jessen]

Hi Rob

FileName is used as a property in VBA.

Change the variable name to something else (ie MyFileName), and run your
macro.

Regards,

Per

 

[Rob]

Per, tried changing the name to MyFileName in both modules but no joy. I
run Debug.Print MyFileName in module 1 before the line Call Module 2, and
then run Debug Print MyFileName in module 2 which was then empty.

I don't seem to be able to hold the value of MyFileName between modules.

Thanks, Rob

 

[Dave Peterson]

Do you have another variable declared in module 2 (either public or within the
procedure)?

 

[Dave Peterson]

And I meant another variable that had the same name.

 

[Rob]

Dave,

I have shown below an extract of the code, which for whatever reason doesn't
seem to retain the string XLSFileNAme from Module1 into Module2.

Don't seem to be able to figure this one out. Regards, Rob

Option Explicit

'Declare for use throughout project
Public XLSFileName As String

Sub Module1()

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String

DefPath = "C:\My Documents\Development\Reports"

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "NewFileTest " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Prints name of file with full path and name
Debug.Print XLSFileName

Call Module2

End Sub

Option Explicit

Sub Module2()

Debug.Print XLSFileName

'Here I want to use the variable XLSFileName but it's null
MsgBox "This file has been saved to: " & vbNewLine & XLSFileName
End Sub

 

[Dave Peterson]

First, change the name of your subroutines. It's not a good idea to name them
the same as the module name--it can confuse excel.

After I changed the names of the macros (macro1 and macro2 and the call
statement), it worked fine for me.

Dave,

I have shown below an extract of the code, which for whatever reason doesn't
seem to retain the string XLSFileNAme from Module1 into Module2.

Don't seem to be able to figure this one out. Regards, Rob

Option Explicit

'Declare for use throughout project
Public XLSFileName As String

Sub Module1()

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String

DefPath = "C:\My Documents\Development\Reports"

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "NewFileTest " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Prints name of file with full path and name
Debug.Print XLSFileName

Call Module2

End Sub

Option Explicit

Sub Module2()

Debug.Print XLSFileName

'Here I want to use the variable XLSFileName but it's null
MsgBox "This file has been saved to: " & vbNewLine & XLSFileName
End Sub

 

[Rob]

Thanks Dave, I'll try your suggestions. Rob

First, change the name of your subroutines. It's not a good idea to name
them
the same as the module name--it can confuse excel.

After I changed the names of the macros (macro1 and macro2 and the call
statement), it worked fine for me.