Using variables across workbooks

[teachman]

Hello,

I'm a stuckee for an excel project and I have limited experience with VBA/Macros. I have a question on using variables across workbooks.

I have set variables to something in a function and would like to call that variable from another function in another workbook. I think I understand how to set public variables for use in the same workbook, but I want to set the variable so I can use its value in another workbook.

Any help/guidance would help this newbie, a lot.

Thanks,

George Teachman

 

[Jerry Sullivan]

Hello George Teachman,

If you are new to VBA programming, I'd suggest that you avoid using Public variables
unless there are no practical alternatives.

There are good reasons to use Public variables in certain cases, but if used unwisely,
IMHO they can lead to bad coding habits and code that might be harder to maintain.

If you would like to consider some alternatives, please describe what you are trying to do.

If you want to learn more about an approach to using global variables across workbooks
(despite my warning ), here is a great source.
http://www.cpearson.com/excel/trulyglobalvariables.htm

 

[teachman]

Jerry,

Thanks for the advice. I will try to keep it simple (KIS). I will scale back my efforts by keeping everything in one workbook. So, the title now becomes "Using variables across procedures".

Here is what I find myself up against. I have a multi-sub procedure macro. In the first sub-procedure I have the user select a file which I immediately parse into its bits and pieces for later use, i.e. when it comes time to save the file. I copy the original file into a file named temp.xlsx and do all the stuff on the temp file.

Here is the first step of the code: Opening the user requested file.

SelectedFile = Application.GetOpenFilename("Excel files, *.xlsx; *.xlsm")
Workbooks.Open SelectedFile
fname_full = Mid(SelectedFile, InStrRev(SelectedFile, "\") + 1)
fname_short = Left(fname_full, Len(fname_full) - 5)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:="temp.xlsx", FileFormat:=51
ActiveWorkbook.Unprotect Password:="raca"

I do a bunch of stuff to this file and want to save it as the very last step. Here is the code for that.

ActiveWorkbook.Protect Password:="raca"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=(fname_short & ".xlsx"), FileFormat:=51
Kill "temp.xlsx"

The error comes in the second to last line where I try to save the ActiveWorbkoo.SaveAs Filename:=(fname_short....

The fname_short was originally set to:

fname_short = Left(fname_full, Len(fname_full) - 5)

In the file opening stage. Basically, it is everything to the left of the 'dot'. e.g. FortSam.xlsx fname_short is FortSam

When I put a watch on this variable, the procedure stops before it can display the second occasion of fname_short.

I originally had all the stuff that was being done in one procedure and it worked. Then I thought I'd tiddy the code up and put code I used more than once into their own sub procedure.

What I'd like to find out is how to retain the value of a variable set in one sub procedure to be used in another?

Thanks for your help.

George

 

[Norie]

George

Why not keep one sub and give that a good tiddy up?

If you do want to create separate subs for particular options you pass arguments/parameters.

Then you wouldn't need to worry about scope etc of variables.

What procedures were you thinking of separating out anyway?

Another thing you could look at is creating references to workbooks etc.

Then rather than referring to them by name you can use the reference.

PS Why are you copying the original file?

 

[teachman]

Norie,

I'll try to explain more clearly what I'm doing. The first thing is probably related to semantics. i.e. my misusing important words, like procedure.

Anyway, here goes.
Your first point about keeping everything in one sub and tidy that up.
That was my first try, but I kept getting an error telling me the sub was too long. It was a little over 1900 lines.


If you do want to create separate subs for particular options you pass arguments/parameters.
I'm not sure how this would affect me.

What procedures were you thinking of separating out anyway?
This is where semantics probably got in the way of a clear explanation. I have a sub that copies about 300 non-contiguous cells from one sheet to another. It takes 3 lines to perform one copy/paste operation. I have a sub that moves a sheet from one workbook to another. I have a sub that reformats about 1500 cells on a couple of worksheets in one workbook. I have a couple of other subs. Are these procedures? That's what I've been calling a procedure.

Another thing you could look at is creating references to workbooks etc.
That might be a possibility. I look into that.

PS Why are you copying the original file?
I maintain the original file and work on a file called temp.xlsx that is a copy of the original. That way when things don't go as planned I can retrieve the original. I should have mentioned that this set of macros is to be used by roughly 40 people at 40 different locations on about 6500 different workbooks, of different vintages. I'm trying to bring all the different vintages up to the same version number.

This is probably more information that is normally given in this forum, but I am so new, that I'm not sure even what questions to ask or how to ask them intelligently.

Thanks for your time,

George

 

[Norie]

George

Splitting code can be a solution to that error but it's always a good idea to look at the original code first to see if something can be done with that.

Not sure what you mean by 'affect', I didn't really explain very well/at all.

What I was thinking is that if you are going to create separate subs you can pass variables/parameters to them.

Then you don't need to use public/global variables.

Perhaps if you posted some examples of the code you have?

PS You shouldn't need 3 lines of code to copy/paste, you usually only need one for
a straight copy/paste and 2 for a copy/paste special.

 

[teachman]

Norie,

Splitting code was the only way to make the error message go away, that I knew how to do. Remember, I am even newer than a newbie.

Here is a sample of what takes up most of the code. I should say that this was created by the Macro Recorder.

Sheets("VNIR").Select
Range("B4").Select
Selection.Copy
Sheets("VNIR (2)").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("VNIR").Select
Range("D4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VNIR (2)").Select
Range("D4").Select
ActiveSheet.Paste

I was wrong, it takes six lines of code for the macro recorder to do this. I have to do this 300 times.

That's the problem I'm having, passing variables from one sub to another. E.g.

fname_short = Left(fname_full, Len(fname_full) - 5)

If I stay within the sub where this is coded, everything is okay. When I try to use the variable fname_short in another sub I get an error. When I set up Watches on the first and second instance of this variable the first instance shows the correct value, the second instance shows the variable as null. How do I pass this variable around the table?

I would very much like to know two things at this stage.

1) How to pass a variable from one sub to another (and less importantly from one workbook to another)
2) How to shorten up the six lines below into less than six.

Sheets("VNIR").Select
Range("B4").Select
Selection.Copy
Sheets("VNIR (2)").Select
Range("B4").Select
ActiveSheet.Paste

Norie, I can't tell you how much I appreciate your taking the time to help me through this.

Thank you very much.

George

 

[Norie]

George

I think a good place to start would be just cleaning the code up a <s>lot</s> little bit.

To do that for what you've posted is quite straightforward, get rid of all the Selects

Sheets("VNIR").Range("B4").Copy Sheets("VNIR (2)").Range("B4")
Sheets("VNIR").Range("D4").Copy Sheets("VNIR (2)").Range("D4")

If you have a lot of code like this and the ranges do follow some sort of pattern then you could even introduce some sort of loop.

Perhaps even something like this for all 300 cells.

For I = 0 To 299
   Sheets("VNIR").Range("B4").Offset(I*2).Copy Sheets("VNIR (2)").Offset(I*2).Range("B4")
Next I

I've kind of made a bit of an assumption that the pattern is basically - start in B4 VNIR copy to B4 VNIR (2) , then D4, then F4, H4...and so on.

Probably totally wrong.

 

[teachman]

Norie,

I'll try out the first set of examples. Unfortunately, the cells being capture while are in a pattern, would be hard to fit into a loop structure. Maybe I'll work on the loop thing after I get rid of all the extra baggage I'm carrying around now.

THanks,

George

 

[Norie]

George

If there is a pattern then you never know, perhaps if you posted some more examples?

Actually, even if there isn't a pattern it could still be possible to do things with arrays and a loop.

Anyway, if by 'baggage' you mean all the Select stuff that's definitely a good place to start.

Then you could start looking at loops/arrays/whatever.