Supercharging Your Day-to-Day Work
M is for Macros

M is for Macros - Supercharging Your Day-to-Day Work

There are handy add-ons when it comes to reducing the daily workload and to streamlining the user’s own handling of MS Office: Macros . With this article we explain the right way to activate and use MS Office macros. The examples shown here were implemented in MS Word. But once you have grasped the concept, you will be able to automate frequently used tasks in Excel or PowerPoint and complete your tasks that little bit faster than before.

Macros can be programmed in Visual Basic for Applications (VBA) or recorded in the individual programs themselves. Here we will use the example of a document header to briefly outline both options.

1. Enabling Macros

Macros are nothing other than program codes and can therefore present a vulnerability. That’s why all macros are disabled in the default setting. They need to be enabled in order to define automatic tasks.

First go to File > Options > Customize Ribbon and select the tab Developer. You will now see a new tab with the same name in the program window. On the right of this menu, you will see the column Code and the button Macro Security. You will be able to create and use your own macros if you now select the button Disable all macros except digitally signed macros in this menu. Read more

2. Recording Macros

Select the option Developer > Record Macro to record an activity in a new blank document. Assign a name to your macro and then click on Button to insert a button in your menu. It’s used to access the macro. In the next window you must check Normal.NewMacros"YourMacroName", click on Add to insert it into the bar and then on Modify to give the macro a button with which it is retrieved. Finally, click on OK to confirm. Read more

A good example of a macro

All of the following activities are then recorded.

The idea is to create a document header within 4 steps:

  1. You want to integrate the company logo left-justified in the header.
  2. Below that you want to show your own name, address and contact information.
  3. On the right you want placeholders for the recipient, the address and the company.
  4. Finally you want to insert the current date above the letter itself.

Once you have created the content, click on the button "Stop Recording". Well done, you have just created your own macro. You can run the macro as often as you like by clicking on the selected button in the upper menu bar.

3. Customizing macros

If you want the macro to be available for any number of users, the name in the address will have to change, depending on the particular person. You can edit the macro in Visual Basic for Applications to make that kind of change. To open the menu, go to Macros > YourMacroName > Edit. This window displays the code for the macro you recorded. The code contains the line Selection.TypeText Text="…" with the name that was entered during recording.

If you want to be able to insert the respective name of any given user at this point, you will need to replace the name and the inverted commas with the entry Environ(“USERNAME“). In most cases, user names will not contain any spaces. Often they are replaced with a period or something similar, e.g. “John.Doe”.

To reinsert a space instead of the separating character, enter the following code in the line below Selection.TypeText :

With Selection.Find

.ClearFormatting
.Text = "."
.Replacement.ClearFormatting
.Replacement.Text = " "
.Execute Replace:=wdReplaceAll, Forward:=True, _
Wrap:=wdFindContinue

End With

Save the changes and then start using the macro. Your name should now appear automatically at the designated position. It’s worth reading the rest of the code. Visual Basic for Applications is easy to understand. You may even be able to tidy up the code for your macro if you corrected yourself during recording. Read more

VBA and macros in MS Office allow you to streamline the workflow to perfection and therefore to save time and nerves. By the way, you can also share your macros with other people. You will find the function in VBA under File > Export File.

Need help with Office?

Get in touch with our Office 365 experts.

Read more
  • Friday 28 April 2017

Comment on this article

Leave a comment to let us know what you think about this topic!

Leave a comment

Author

SoftwareONE Blog Team

Blog Editorial Team Trend Scouts

IT Trends and industry-relevant Novelties

Related Articles

does-office-365-offer-a-complete-backup-recovery-solution
  • 11 June 2019
  • Thino Ullmann
  • User Productivity
  • Backup Recovery, Office 365

Does Office 365 Offer a Complete Backup & Recovery Solution?

Increasingly many business relevant activities are handled through Office 365. However, with just one click important files might get lost or deleted by accident - no problem when you have a backup and recovery feature in place. But does…

Build a Business App with Microsoft PowerApps
  • 16 May 2019
  • Christoph Vollmann
  • User Productivity
  • Apps, PowerApps, Microsoft

How to Build a Business App with Microsoft PowerApps

PowerApps is a service provided by Microsoft for creating business apps in your company. In this blog you’ll learn everything there is to know about PowerApps, its advantages and its benefits.

How BYOD Affects Your Productivity
  • 23 March 2016
  • Blog Editorial Team
  • User Productivity
  • Windows 10, BYOD

Bring Your Own Device “Not Under my Roof”?

We find ourselves at the next truly revolutionary phase in the IT landscape. There are two game changing trends, Client Mobility and Cloud technologies.