Solved

define variables in an Excel macro

Posted on 2014-03-18
11
387 Views
Last Modified: 2014-03-23
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

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_printing
    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.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
0
Comment
Question by:mycomac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39937103
Are you wanting them to be global constants? Then you want to do it like this:
Public Const name As String = "Your name"
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39937122
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

Open in new window

The macros need to be in the same scope (read: module file).
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39937134
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:regmigrant
ID: 39937136
crossed with qelmo
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39937274
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.
0
 

Author Comment

by:mycomac
ID: 39937365
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.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 39937503
Yes. So this is the syntax you want
Public Const name As String = "Your name"

So it would look like this
'Begin - define these 3 variables for your expense report
Public Const name As String = "My Name"  'inserts your name into line 84
Public Const logo As String = "C:\Users\Jeff\Dropbox\Documents\Pictures\Agro-K\agro-k logo.JPG"  'inserts the path for the AGRO-K logo into line 209
Public Const path As String = "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

Open in new window

Of course, you shouldn't refer to line numbers in the comments. Just the purpose (which you have already done for most of them).
The line numbers will change as you add code (which is most likely one reason you wisely decided to use constants in the first place).
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39938125
so your users will be editing the vba? that seems...... optimistic but to each his own
0
 

Author Comment

by:mycomac
ID: 39938286
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?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39938335
You can use cells in a sheet for the values and a button to trigger the macro/sub.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39938336
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel formula with SUMIF and SUBTOTAL 13 44
can this macro be converted to sub procedure 4 30
JVM error from eclipse 1 22
VB.NET Parsing UDP Bytes 15 27
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question