Category Archives: Excel Macros

Posts relating to Excel Macros and/or VBA

Creating Scroll Bars in Excel

Excel Files, Excel Macros, Excel Techniques, MathsFiles.com Resources, ,

I remember the first time I saw a scroll bar in Excel. ‘Wow!’ I thought, ‘I can think of so many things I could do with that’.

Scroll bars can be used to control the value of a cell so that we can see the effect of changing its value on a graph or calculation. They are great for creating dynamic demonstrations or explorations.

However the basic scroll bar that Excel creates for you is a bit limited and needs some work to get it to do everything we might want from it.

This excel file available in the Excel techniques section of www.mathsfiles.com/Excel_Files shows how to create and configure scroll bars to achieve our goals. We start with the most basic and work through to a sophisticated macro controlled version.

ScrollBars
Scroll Bars in Excel

Direct link:
Scroll Bars Excel file

Setting Up Excel for Advanced Work

Excel Files, Excel Macros, Excel Techniques, MathsFiles.com Resources, , ,

This covers Customizing the Quick Access Toolbar and enabling the Developer Tab for Excel 2007 onwards:

If you want to work with Macros and other Advanced features in Excel you need to show the Developer Tab and it’s also a good idea to customize the Quick Access Toolbar to give you easy access to the key tools.

Excel after customization
Excel after customization

There are two files on www.mathsfiles.com/Excel_Files in the techniques section that show how to do this.

Direct links:
Instructions pdf file

Watch a Demo (based on Excel 2007)
Note: The process is much the same for later versions of Excel but the way to show the Developer Tab is found by going to Excel Options and then the section for Customizing the Ribbon. Just  check the box for the Developer Tab.

 

What is an Excel Macro?

Excel Macros, Excel Techniques, MathsFiles.com Resources, , , ,

An Introduction to Recording and Modifying an Excel Macro.

On a basic level one can use the Macro recorder to record some actions in Excel without looking at the code generated.
The macro can then be attached to a button or run with a keyboard shortcut to replay those actions.
At the next level one can look at the generated code in the Visual Basic Editor and start to understand what it does and begin to be able to tweak it to achieve something a bit more sophisticated.
As one learns more and with the aid of internet forums and tutorials, it becomes possible to write code oneself and/or to use and adapt other people’s code. Looping structures and other computer programming forms can be used.
There are also special Excel Macros that are triggered by events such as when the worksheet recalculates. These can be used to create impressive effects.

I have created a demo screen recording which shows how we can begin to use the Macro Recorder to learn about and modify VBA code.

You can find the demo on the Excel techniques section of the Excel page at MathsFiles.com or click on this direct link :
Macro Recorder demo.