Jeff McClellan
asked on
define variables in an Excel macro
I want to define 3 variables (global variables) at the beginning of a procedure to use in several macros that run as part of that procedure. Here's what I have written, but it is incorrect (using the wrong syntax or something). How should this be written so each variable works in the appropriate macro when that macro is called in Sub iXpenseIt2?
'Begin - define these 3 variables for your expense report
Dim name As String
name = "My Name" 'inserts your name into line 84
Dim logo As String
logo = "C:\Users\Jeff\Dropbox\Doc uments\Pic tures\Agro -K\agro-k logo.JPG" 'inserts the path for the AGRO-K logo into line 209
Dim path As String
path = "C:\Users\Jeff\Dropbox\Doc uments\Agr o-K\Expens e Reports\14 reports\" 'inserts the path where you want to save this file into line 296
'End define variables for your expense report
Sub iXpenseIt2()
Application.DisplayAlerts = False
Macro6_filename_saveas
Macro1_create_pivot_table
Macro2_format_data
Macro3_format_print_layout
Macro4_delete_pivot_sheet
Macro5_format_page_for_pri nting
Macro6_filename_saveas
Application.DisplayAlerts = True
End Sub
-------------------------- ---------- --------
following are the lines of code that do work if I type the variable information into the code directly.
Line 84: ActiveCell.FormulaR1C1 = "My Name"
Line 209: ActiveSheet.PageSetup.Righ tHeaderPic ture.Filen ame = "C:\Users\Jeff\Dropbox\Doc uments\Pic tures\Agro -K\agro-k logo.JPG"
Line 296: ActiveWorkbook.SaveAs Filename:="C:\Users\Jeff\D ropbox\Doc uments\Agr o-K\Expens e Reports\14 reports\" & Format(Date, "YYMMDD") & "_JTM_expense_report", FileFormat:=xlNormal
'Begin - define these 3 variables for your expense report
Dim name As String
name = "My Name" 'inserts your name into line 84
Dim logo As String
logo = "C:\Users\Jeff\Dropbox\Doc
Dim path As String
path = "C:\Users\Jeff\Dropbox\Doc
'End define variables for your expense report
Sub iXpenseIt2()
Application.DisplayAlerts = False
Macro6_filename_saveas
Macro1_create_pivot_table
Macro2_format_data
Macro3_format_print_layout
Macro4_delete_pivot_sheet
Macro5_format_page_for_pri
Macro6_filename_saveas
Application.DisplayAlerts = True
End Sub
--------------------------
following are the lines of code that do work if I type the variable information into the code directly.
Line 84: ActiveCell.FormulaR1C1 = "My Name"
Line 209: ActiveSheet.PageSetup.Righ
Line 296: ActiveWorkbook.SaveAs Filename:="C:\Users\Jeff\D
If you write only the DIMs into the module code, and then run a "macro" (read: procedure) to set those vars, it should work.
Dim name As String
Dim logo As String
Dim path As String
Sub iXpenseIt2()
Application.DisplayAlerts = False
name = "My Name" 'inserts your name into line 84
logo = "C:\Users\Jeff\Dropbox\Documents\Pictures\Agro-K\agro-k logo.JPG" 'inserts the path for the AGRO-K logo into line 209
path = "C:\Users\Jeff\Dropbox\Documents\Agro-K\Expense Reports\14 reports\" 'inserts the path where you want to save this file into line 296
Macro6_filename_saveas
Macro1_create_pivot_table
Macro2_format_data
Macro3_format_print_layout
Macro4_delete_pivot_sheet
Macro5_format_page_for_printing
Macro6_filename_saveas
Application.DisplayAlerts = True
End Sub
The macros need to be in the same scope (read: module file).
Tommy's solution answers the question as written but requires you to modify the code and update the constants each time an item changes
If you want to modify them at runtime (ie: in the macro) you need to declare them as publilc without setting a value:-
- note these are before the 'sub' line in the main module
Public myName as String
Public myLogo as String
Public my Path as String
'i've added the 'my' prefix to avoid reserved words or confusion on things like name and path
After the sub you then have a form (or read a spreadsheet cell) to set them to the values you want
If you want to modify them at runtime (ie: in the macro) you need to declare them as publilc without setting a value:-
- note these are before the 'sub' line in the main module
Public myName as String
Public myLogo as String
Public my Path as String
'i've added the 'my' prefix to avoid reserved words or confusion on things like name and path
After the sub you then have a form (or read a spreadsheet cell) to set them to the values you want
crossed with qelmo
I think you misread the question. He doesn't want to change them at run time. He wants to set them at the top so the user doesn't have to go find them all every the user needs to change them.
That is one of the many good reasons to use constants.
That is one of the many good reasons to use constants.
ASKER
I apologize for my choice of words. I do believe i want to define constants so the user only modifies them once at the top.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so your users will be editing the vba? that seems...... optimistic but to each his own
ASKER
Do you have a suggestion how to do it differently that does not require collecting the constants every time? Could we use a form to do that the first time the macro is run?
You can use cells in a sheet for the values and a button to trigger the macro/sub.
The most straightforward way would be to use a worksheet_activate macro to allow data entry into defined cells and when confirmed change the cells to 'locked' and copy the values into the (publicly defined) variables (I assume you are using 'protect sheet'). To prevent update when the sheet is re-opened it should check for the 'lock', the one downside to this is if they make a mistake it will be necessary to unlock them again.
The other alternative is a form that opens on entry and perhaps allows an option to update.
The other alternative is a form that opens on entry and perhaps allows an option to update.
Public Const name As String = "Your name"