Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
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" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
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 "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
Do you have another variable declared in module 2 (either public or within the
procedure)? Rob wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
And I meant another variable that had the same name.
Dave Peterson wrote: Do you have another variable declared in module 2 (either public or within the procedure)? Rob wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
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" wrote in message ... And I meant another variable that had the same name. Dave Peterson wrote: Do you have another variable declared in module 2 (either public or within the procedure)? Rob wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
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. Rob wrote: 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" wrote in message ... And I meant another variable that had the same name. Dave Peterson wrote: Do you have another variable declared in module 2 (either public or within the procedure)? Rob wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass between modules
Thanks Dave, I'll try your suggestions. Rob
"Dave Peterson" wrote in message ... 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. Rob wrote: 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" wrote in message ... And I meant another variable that had the same name. Dave Peterson wrote: Do you have another variable declared in module 2 (either public or within the procedure)? Rob wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass value to excel userform - is there a constructor? how to pass | Excel Programming | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming |