CODEWITHSUNDEEP
vbavariablesmodulepublic
Jeff
Jeff

Reputation: 43

Calling a global/public variable in moduleB whose value was defined in moduleA

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.

Upvotes: 1

Views: 82

Answers (2)

Jeff
Jeff

Reputation: 43

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

Upvotes: 2

the VBE - it's right for me
the VBE - it's right for me

Reputation: 168

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.

Upvotes: 3

Related Questions