define variables in an Excel macro
Posted on 2014-03-18
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\Documents\Pictures\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\Documents\Agro-K\Expense Reports\14 reports\" 'inserts the path where you want to save this file into line 296
'End define variables for your expense report
Application.DisplayAlerts = False
Application.DisplayAlerts = True
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.RightHeaderPicture.Filename = "C:\Users\Jeff\Dropbox\Documents\Pictures\Agro-K\agro-k logo.JPG"
Line 296: ActiveWorkbook.SaveAs Filename:="C:\Users\Jeff\Dropbox\Documents\Agro-K\Expense Reports\14 reports\" & Format(Date, "YYMMDD") & "_JTM_expense_report", FileFormat:=xlNormal