1

I wrote 4 macros to do things, but it requires 2 inputs from the user to make sure the right file is being used because some of the macros switch back and between 2 workbooks. I only had access to a few of the files, but I knew that eventually I would have access to the rest of the 35 files. If I didn't have the inputs, I would have to manually change the filename in the macro code, but I don't want to do that, so I used inputs. But now that I have all the files in the right format, I am trying to a separate macro that has a list of the other files in a separate workbook, and then opens those files and does the macros, but it would require the inputs a lot. So now, I'm trying to remove that need for the inputs. But I'm unfamiliar with public variables and somewhat familiar with the calling of other subroutines.

My setup is this: 

option explicit
public current as string

Sub master_macro
dim i as integer
dim path as string
dim wb as workbook
dim sht as worksheet

set wb = workbooks("name.xlsx")
set sht = wb.worksheets(1)
path = "C:\xxx\"
wb.activate
for i = 1 to 20
    currun = sht.cells(i,1).value 'this takes the value from the separate workbooks that has the file names
    full_currun = currun & ".xlsx"
    with workbooks.open(path & full_currun)
        .activate
        call blanks
        call lookup
        call transfer
        call combine
        .save
        .close
    end with
next i

The last 2 macros switch between 2 sheets. So in those macros, the currun is generated the an inputbox, albeit a different name.

nam = inputbox("yadda yadda")
set wb = workbooks(nam & ".xlsx")

I'm trying to get the currun vaue that is defined in the master macro to macro3 and macro4.

Improve this question
2
  • 2
    You don't need any globals if you pass parameters to between your procedures. Commented Dec 11, 2019 at 20:10
  • 1
    Don't .Activate a workbook that you opened. Don't have a With block hold that reference either - you want to Set book = Workbooks.Open(...) so that you have a reference to the workbook that you can pass around and that other code can work with, instead of relying on the correct workbook/worksheet being active. Commented Dec 11, 2019 at 20:13

2 Answers 2

Reset to default
3

You see the part where it says Sub master_macro? What you are doing there is declaring a procedure, which is a basically a general term to describe "a block of self-contained code that does something when it is run." Procedure declarations have three major components:

  • type - this is what you are doing with Sub; you are saying it is a subroutine, which is distinct from a function Function in that it does not return a value
  • name - this is the identifier you use to refer to the procedure elsewhere in your code. it is supposed to be descriptive since that enhances the readability. "master_macro" is not bad, but as a general rule you don't want to use underscores when naming procedures in VBA.
  • parameters - this is where you define the set of variable values that can be passed to the procedure when it is run. each parameter is separated by a comma and declared using the syntax [pass type] + [variable name] + [variable type]. [pass type] is either ByRef or ByVal; the basic distinction is that ByRef sends a direct reference to the variable, while ByVal sends a copy of the value.

The last part is what you are missing to solve this problem. Both macro3 and macro4 are declared (in module B) like master_macro is here. If they need to know what the currun value is then simply add (ByVal currun As String) to their declarations. When they are called from another procedure, as they are in master macro, they will expect to receive a string. Change the two lines in master macro from:

Call macro3
Call macro4

to

Call macro3(full_currun)
Call macro4(full_currun)

and macro3 and macro4 will have the value of full_currun stored in their own internal variable currun for use as they need.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Or better, drop the superfluous Call keyword and do macro3 full_currun and macro4 full_currun
@MathieuGuindon Does using Call incur a performance/reliability hit? I know it is unnecessary but I think it can be a useful tool, particularly when someone is learning programming with VBA, because it makes the code read more like natural language. Easier and more intuitive to parse visually helps direct mental energy/focus toward gaining conceptual understanding.
What makes the code read more like natural language, is using descriptive procedure names that start with a verb, instead of using obsolete keywords to invoke procedures with meaningless nouns like Macro2 ;-)
changing it from call macro3 to call macro3(full_currun) doesn't work. It says wrong number of arguments or invalid property assignment.
@MathieuGuindon Of course. But that isn't necessarily easy to do, even (as I understand it) for people who are paid to write code. Also when the concept of invoking a procedure is not so well understood, having the explicit indication of what you are doing mechanically is helpful. Definitely not a habit to keep when you get competent but I feel there are more important things to focus on when you aren't there.
|
2

Thanks guys. managed to get it to work. Here's the finished work below

sub master()

dim i as integer
dim path, currun, fullcurrun as string
dim wb as workbook
dim sht as worksheet

set wb = workbooks("Name.xlsx")
set sht = wh.worksheets(1)
path = "C:\xxx\"
wb.activate
for i = 1 to ?
    currun = sht.cells(i,1).value
   fullcurrun = currun & ".xlsx"
   workbooks.open(path & fullcurrun)
   call blank(currun)
   call lookup(currun)
   call transfer(currun)
   activeworkbook.save
   activeworkbook.close
   call transfer(currun)
next i
end sub

public sub blank/lookup/transfer(byval currun as string)
blah blah blah
end sub
Improve this answer

3 Comments

glad it works. and props for incorporating mats suggestion to use meaningful procedure names without explicitly being told. :)
a couple suggestions. declare another workbook variable so you can assign that to the workbook opened in the loop instead of using activeworkbook. and you can combine the workbook close and save into one line with .Close SaveChanges:=True
I always name my modules/macros, but for the sake of simplicity, figured I would just call the macro 1/2/3/4 in here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.