How to record a macro in Excel 2007

If you have a task that you perform repeatedly, then you can record all the events to achieve that task (keystrokes, mouse clicks … etc.) in a macro.

To record a macro in Excel 2007 follow these steps.

1. Click on the Developer tab. If the Developer tab is not visible, then check this post on how to show the Developer tab:

2. In the Code group Click Record Macro.

xl7macros1

3. The Record Macro window will open.

xl7macros2

4. Give a name to your macro.

5. Specify a shortcut key(optional).

6. Specify where you want to save the macro.

7. Click OK.

8. Now start performing the task which you want to record. When you are done click Stop Recording.

xl7macros3

9. Your macro is now created and is ready to be used. To run the macro:

  1. Click Macros.
  2. xl7macros4

  3. The Macros window will open.
  4. xl7macros5

  5. Select the macro then click run.

10. You can also run the macro by simply clicking the shortcut key combination.

How to write a VBA macro

If you know Visual Basic programming then you can create your own VBA macro and run from within Excel.

Follow the steps below:

1. On the Tools menu select Macros.

2. On the Macro submenu select Visual Basic Editor.

User defined1

3. You will be switched to Visual Basic Editor Menu.

User defined 2

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

User defined 3

6. Type the Visual Basic code for your macro.

Here is a simple example. A macro to increment the variable i by 5 and write it to column 2 (Column B) in the next row :

Sub TestMacro()
Dim i As Integer, Row As Integer, Col As Integer
i = 0
Col = 2
For Row = 1 To 10
i = i + 5
ActiveSheet.Cells(Row, Col).Value = i
Next Row
End Sub

7. On the File menu click Close and return to Microsoft Excel.

8. Your new macro is now ready to be run.

To run your macro:

1. On the Tools menu select Macros. A list of available macros will be displayed.

2. Select your macro and click Run.

Applies to: Excel 2003

How to make your macros and functions available to all workbooks

When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save it in Excel startup folder.

How to create the file:

1. Open a new workbook.
2. Save As Personal.xls in folder:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
3. Copy your macros from their original workbooks to personal.xls.
4. Click Window –> Hide to hide the file.

The path may differ slightly depending on which version of office you are using.

How to use the file:

1. Whenever you start Excel personal.xls will be opened automatically, and all your macros will be available for use in any workbook.

2. Keep personal.xls hidden unless you want to add macros to it.

3. When you want to add new macros Click Window –> Unhide and select “personal.xls”.

4. To add a new macro click Tools –> Macro –> Record new macro. You will get the following dialogue box.

savemacro

5. Under “Store macro in:” select “Personla.xls”.

6. When you are done hide the file again.

If you are interested in more details please see the following Microsoft office link:

Deploy your Excel macros from a central file

Applies to: Excel 2003

A macro to generate a list of random numbers

Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().

However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.

This is OK if you want a small list, but what if you have to generate say a list of 1000 random numbers?

The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.


Sub GenNumbers()
Dim i As Integer, Row As Integer, Col As Integer
Dim NoRows As Integer, NoDigits As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 ‘ Write random numbers on column A
‘Get the number of rows and number of digits entered by the user
NoRows = ActiveSheet.Range(“B1”).Value
NoDigits = ActiveSheet.Range(“B2”).Value
‘Calculate minimum and maximum numbers to be generated
MinNum = WorksheetFunction.Power(10, (NoDigits – 1))
MaxNum = WorksheetFunction.Power(10, NoDigits) – 1
‘Construct the formula to be entered in the cells.
NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)”
‘ The loop to generate the numbers
i = 0
For Row = 3 To NoRows + 2
i = i + 1
ActiveSheet.Cells(Row, Col).Value = i
ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula
Next Row
End Sub

How to use the macro

1. Switch to Visual Basic Editor and Insert a new module.
2. Copy the code into the new module.
3. Close Visual Basic Editor and return to your worksheet.
4. In cell B1 of your worksheet enter the number entries you want.
5. In cell B2 of your worksheet enter the number digits you want.
6. Go to Tools -> Macro -> Macros, you will see the list of available macros.
7. Select GenRandomNum and run it.
8. You will get an output like this:

genrandnum.gif

Applies to: Excel 2003