String problem

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306
Hi All,

I have the following piece of code which assigns a file name:-

Code:
Dim new_file As String
Dim direc As String
direc = "H:\"
new_file = "Report_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx"

I currently have it stored behind a button on a form (part of a procedure).

There are many different modules, but whenever a different module executes “new_file” it returns a blank string.

Where is the best place to store the code so that all modules recognises the string “new_file”.

Thanks in advance,
Paul.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Personally I would store the string in a cell in a worksheet somewhere and refer to that cell when you want it back. However, the more “proper” way to do this would be to declare the variable “new_file” as a public variable. To do this, simply declare the variable as normal but in one of the standard modules and before any sub routines, i.e:
Code:
Dim new_file as String

Sub MySub()
     Bits of code
End Sub

Sub MySecondSub()
     Bits of code
End Sub

Hope that helps!
 

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306
Hi Lewiy and thanks very much for replying.

Unfortunately, I still can't get it to work. Basically I have the following projects:-

Userform1
Module1
Module2
Module3
Module4
Module5

My problematic code is behind Userform1. I am trying to call "new_file" from each of the other 5 modules. As suggested, I have placed the Dim statements at the beginning of Module1 but it still will not work. I have also tried putting it at the beinninng of each module, but no luck.

Thanks again.

Paul.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Perhaps try putting the code which creates the new file name within its own sub in, let’s say Module1 (note: still putting the variable declaration at the very top of the Module):
Code:
Dim new_file As String

Sub NewFileName()
Dim direc As String 
direc = "H:\" 
new_file = "Report_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx" 
End Sub

Then in the place in the UserForm1 code where you have this at the moment, just call this Sub Routine with the line:
Code:
NewFileName

See if that helps.
 

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306

ADVERTISEMENT

Hi,

Funnily enough, I have just tried exactly what you have said, but no luck. I created a separate module for the code (with the Dim statement at the top).

It runs as follows:
- procedure starts in Userform1
- halfway through it calls the sub routine in the module I have just created (the Dim has a value as expected)
- the procedure returns to Userform1
- a few lines down it calls another module
- the Dims and "new_file" are empty

It doesn't seem to store it. I must be missing something really obvious here.

Cheers,
Paul.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Are you able to post the UserForm1 code and a couple of the Module codes so I can see what you have?
 

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306

ADVERTISEMENT

Okay, I have created this simple routine which creates the issue I an having.

Userform code:-
Code:
Private Sub CommandButton1_Click()
Run "CREATE_NEW_FILE"
MsgBox new_file
End Sub

Module1 contains the following code:
Code:
    Dim new_file As String
    Dim direc As String
Private Sub create_new_file()
    direc = "H:\"
    new_file = "Reports_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx"
End Sub


When the code then returns to the Userform, the msgbox which is activated is empty, thus not retaining the "new_file" details.

Rgds,
Paul.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Hmmm, I see what you mean, I’m having problems with this too. Umm, personally I’d go back to my original method and store the string in a cell in one of the worksheets and refer back to it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
Paul

What exactly is new_file meant to be/do?

Where is the code and userform located?
 

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306
Lewiy: Thanks for all your help. I took your advice and slightly adapted it. I stored the string in a field on the form itself and it works fine. Can't see why it wouldn't pick it up before. Maybe you can't cross-reference the Dim statement bewteen modules (?) Cheers.

Hi Norie,
"New_file" is the name of a file I am creating/saving. I then drag into it many text files from different sources. I created separate modules for each different text import.

Rgds,
Paul.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

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