Questions about starting with macros

Hi,

I'm a relatively knowledgeable Excel user and I have many repetitive, but complex, tasks that I do on a regular basis within Excel. Thus, I was wondering if I can look into recording/using macros in my situation. Since I'm a business owner and I "live" in Excel, I need to be 100% sure that in looking at this option will not cause any harm to my business-critical Excel files...I run my business from Excel and need to be in full control of everything that happens within the workbook(s) and have the ability to find issue (in macros) if they arise. Let me briefly explain my situation:

- I currently use a PC and use Excel 2010 and use 1 primary file (with many data points referencing various parts of multiple tabs). This "master" file is saved as .xlsx (and I want to keep that file extension for sharing purposes). I create this "master" file from a weekly template that I change with updated data from other information sources.
- I email parts of this file (saved independently) to: (1) a co-worker that uses Office for Mac 2011 (she does NOT want to use macros, (2) other people in the company that are novices in Excel (and I don't want to confuse them by saving the file as anything BUT .xlsx, and (3) suppliers and other recipients outside of our company that wouldn't have any idea about macros.

Here are my questions/concerns:
1. I want to keep the "master" file extension as .xlsx (not have it as .xlsm). Can I do this?
2. As a follow up question, I think I would do this with a Personal.xlsb workbook (in which I think would save all the macros for me to use across multiple workbooks, without saving macros to a specific workbook). I want to have macros available across workbooks. Is this the correct way of accomplishing what I'm looking to do?
3. I want to make sure that any macros that I save on my computer could only by accessed/used by me. In other words, I don't want anybody that I email "independently saved" parts of my "master" file to (co-workers, suppliers, etc.) see what macros I created for my own personal use. How can I do this?
4. Any other advice to someone that knows Excel pretty well, but is new to macros?

Any thoughts about starting with macros would be greatly appreciated. Thank you very much in advance.

Yury

1. Yes.
2. You can have a personal workbook or a free standing macro enabled workbook have a "toolkit" of macros that manipulate and extract data.
3. Your macros would be in a file/workbook on your computer and need never be shared with anyone else.
4. Start by switching on the macro recorder while you carry out tasks manually. This will give you the basic code. It will need tweaking to make it generic and more efficient.

Make daily copies of your Master workbook. Macros enable you to complete repetitive tasks quickly. However, if you **** up, it will do that quickly too.

When you record a macro, you have two choices; save in the current workbook, or save in your Personal macro workbook. If you save them in your personal file, that is saved in your profile and only accessible to you. If you save it in the current workbook and then try to save it, you will need to save it as an .xlsm file.

However, it makes sense to develop your macros in a copy of your master workbook. You will need to tweak the macros to make them generic and efficient so, when you do that, you can make them refer to another workbook ... in this case, the real master workbook ( or another copy while you are developing and testing ).

Once you have the macros in their final state, you can delete the data and most of the worksheets from your "tool box".

Hi InvisibleMan,

Thank you for your advice. I just tried out some of the things that you said and my initial macros were able to be saved in my Personal.xlsb file (that I kept open in the background) and were, in fact, able to access the initial macros in new (different) workbooks that I created.

I obviously made some initial mistakes in the macros (which I have to learn how to debug), but using macros certainly looks promising for my purposes. Thank you very much for the guidance,

Yury

You're welcome. Thanks for the rep.

I'm not really a fan of Personal workbooks, but each to their own.

The key thing to bear in mind when you are using a macro that refers to another workbook, is to create object variable(s) to refer to the workbook(s) being operated on. In this case, your Master workbook and any workbooks you create for distribution. If you do that, you shouldn't have any problems making changes to the wrong workbook, worksheet, range, or cell.

Hi InvisibleMan,

Thank you for your help. Just a couple of follow up questions...

1. Why aren't you a fan of Personal workbooks? Do you mean this just for yourself or in my situation? It seems like this is the right path for me, but maybe I'm not seeing something that you are.
2. When you mean an "object variable", do you mean like a name of a range in Excel or something a bit more "VBA-programming-like"?
3. I think I can name the range in my "Master" file and, if they go along for distribution, that's OK, but irrelevant to recipients. Am I on the right track?

Here is my actual weekly process:
1. I have a weekly template with many lookup formulas and many tabs of information that I gather from various sources.
2. I open up the last week's (LWTemplate) template file (as a Read-Only), over-write any old information with new information (sales and other data) and then save that newly edited file as the new week's template (NWTemplate). I close that file.
3. I then open the new week's (NWTemplate) file (as a Read-Only), copy and paste as text any data that is static (doesn't need to be changed/looked up), get rid of any tabs that are irrelevant to the analysis that I have to do and name that file "CurrentAnalysis". BTW, the "NWTemplate" will become "LWTemplate" next week and so, on.
4. I send the relevant tabs from "CurrentAnalysis" to my co-workers for their analysis and keep the new week's file for me to analyze.
5. I use "CurrentAnalysis" file to do all of my analysis, place orders, set up processes, etc.
6. It's in this "CurrentAnalysis" file that I actually do all of my work. This is the file that I want to use macros in (I'm sure that I can use macros in other files, but my initial intention is in this one). Thus, I think if I (a) name various ranges in the "CurrentAnalysis" file and refer to them by the range name and (b) use a Personal Workbook for those macros, then this should work for me. Do you agree?

Thank you again, in advance, for your help,

Yury

1. Why aren't you a fan of Personal workbooks? Do you mean this just for yourself or in my situation? It seems like this is the right path for me, but maybe I'm not seeing something that you are.

Just personal choice. I've seen problems reported about not being able to hide PWBs and it does kind of lock the toolkit to your user ID. That means that, when you are on holiday, or sick, the toolkit isn't readily available for anyone else to use. I also find it easier to develop and test code in a free standing workbook. Even though I can code from scratch, I still "record and tweak" in a lot of situations.

2. When you mean an "object variable", do you mean like a name of a range in Excel or something a bit more "VBA-programming-like"?

Yes, an object variable refers to a workbook, worksheet, range, cell, or whatever, amongst other things. So, for example, ...

Dim wbM As Workbook
' a currently open workbook
Set wbM = Workbooks("Master.xlsx")
' a variable to refer to a worksheet
Dim ws As Worksheet
For Each ws In wbM.Worksheets
    ' print the worksheet name
    Debug.Print ws.Name
Next 'ws
' destroy the object variables
Set ws = Nothing
Set wbM = Nothing

3. I think I can name the range in my "Master" file and, if they go along for distribution, that's OK, but irrelevant to recipients. Am I on the right track?

Sure: a named range isn't necessarily a source for concern. But you can always hide Named Ranges, if you wish.

Dim wbM As Workbook
' a currently open workbook
Set wbM = Workbooks("Master.xlsx")
Dim n As Name
For Each n In wbM.Names
    n.Visible = False
Next 'n

If you only want people to see the data in the worksheet(s), have you thought of saving it/them as .pdf files?

Using Named Ranges is always preferable to using hard coded ranges, as they will remain true even if rows are added or deleted, and if columns are added or deleted. You would need to create them as Dynamic Named Ranges in order to cope with an increasing or decreasing row count. For example:

Formula:

='1'!$A$2:INDEX('1'!$A:$A, COUNTA('1'!$A:$A))

Note, '1' is the sheet name.

Good luck