Technology Programming

Improve Your Excel Knowledge With VBA

Most users of MS Excel are unaware of the power and functionality of macros and the in-built Visual Basic for Applications (VBA) programming language.
Macros are good for recording repetitive tasks but many people become frustrated with them because they don't quite solve the problem being addressed.
For example, you can use a macro to create a chart but what happens if the data set is added to? You could record a macro which would select the entire column of data before creating the chart, but you might want to keep some explanatory text at the bottom of the column which might mean you need to manually select the data before using the macro to create the chart.
Use VBA To Fine Tune Your Macros You can use VBA to adapt a macro to better suit your needs.
Once your macro has been saved, you can go into the code window and edit the macro code, which just happens to be written in VBA.
If this sounds a little daunting, you're in for a nice surprise.
Because you're creating your own macro, you'll know already what the VBA code is trying to achieve.
You can experiment with the code without doing any damage - save a copy of the macro if you're concerned.
Let's try a simple example to get started on using macros and VBA together.
  1. Type some text into the first cell of a worksheet.
  2. Record a macro which makes the text bold
Now, access the VBA code behind the macro and see if it makes sense to you.
Don't worry about the syntax or the commands at this stage, it's just important you understand the basics.
The generated code should look something like this:
Range("a1").
select Selection.
Font.
Bold=true Because you know what you're trying to achieve with the macro, the code should appear reasonably self-explanatory.
The macro selects the cell, and then makes the cell contents bold.
Now, play around with the code see what you can change.
You could make the text italic as well by adding to the code:
Selection.
Font.
Italic=true What about something you can't easily do with a macro? Perhaps you're writing a report with tomorrow's date required in the first cell, bold and underlined.
You can create a function to do this, but this example is designed to show you that what Excel can do, you can do too.
Just add this into the code
Selection.
Font.
Underline = True Selection.
Value = Date + 1 Although this is a simple example, it should give you a few ideas as to how Excel macros and VBA can help your efficiency, perhaps even giving you time for an extra espresso throughout the day! It's good to be a little ambitious when you think about how VBA can help you; for example, instead of simply making the date bold, why not make the date red, but only on a Monday? And could you use Excel to email that report once the VBA code has completed it? Most of us use Excel every week, so what are some of the things about using Excel that frustrate you? There's a good chance that macros and VBA can provide the solution.
SHARE
RELATED POSTS on "Technology"
WordPress - How to Set up a New Theme to WordPress 3.
WordPress - How to Set up a New Theme to WordPress 3.
Solution of Creative Web Design
Solution of Creative Web Design
The three disciplines of User Experience
The three disciplines of User Experience
Web Design Sheffield Options For Professional Enterprises
Web Design Sheffield Options For Professional Enterprises
Do you have what it takes?
Do you have what it takes?
Segway Cost
Segway Cost
Microsoft Access 2010: What's Coming with Office 2010?
Microsoft Access 2010: What's Coming with Office 2010?
Companies of Web Development in Ireland Provide Designs that Work
Companies of Web Development in Ireland Provide Designs that Work
Penguin Update to Put Red-Flags on Negative SEO
Penguin Update to Put Red-Flags on Negative SEO
Innovative web 2 design templates can make your business famous quickly
Innovative web 2 design templates can make your business famous quickly
Building A Search Engine Friendly Website
Building A Search Engine Friendly Website
Exceptional Advice To Build Up Your Internet Marketing
Exceptional Advice To Build Up Your Internet Marketing
The Benefits of Selecting The Right Hosting Company
The Benefits of Selecting The Right Hosting Company
Is There a Methodology for Making Successful Logos
Is There a Methodology for Making Successful Logos
Benefits of Ruby On Rails Development
Benefits of Ruby On Rails Development
The Power of Colour and Shapes in Your Infant's Life.
The Power of Colour and Shapes in Your Infant's Life.
Advantages of Hiring PSD To HTML Service Providers
Advantages of Hiring PSD To HTML Service Providers
How to Make Responsive Web Design Attractive?
How to Make Responsive Web Design Attractive?
Converting PSD to Responsive HTML
Converting PSD to Responsive HTML
Just a few realy really hints and tips when it comes to website design but look for.
Just a few realy really hints and tips when it comes to website design but look for.

Leave Your Reply

*