Tag Archive for 'Microsoft VBA'

Visual Basics Tip: Future Proofing Code

There are several ways you can future proof your code in Visual Basic. By using the Macro Dialogue Box in Excel (as well as Word) you can make sure your colleagues can understand the purpose and general use of the Macro you record or write.  To use the Macro Dialogue Box to is advantage. Here the best ways on how to do so:

 

Give your macro a user friendly name.

Always give your macros a user-friendly name, or agree a naming procedure for your Macros so that everyone knows what they are for. This may seem a bit like stating the obvious, but if your Macro is there to format a table, then give it a name that would summarize what it formats and keep it memorable and concise. Naming a Macro No.14 isn’t really going yourself or others any favors when debugging any problems.

 

Add the shift button to any key short cuts.

Always add the Shift button to any key shortcuts. A CTRL+Key shortcut will disable its MS predefined purpose, so If you add CTRL+C shortcut to your macro and run it, you will not be able to use that short key to copy text or cells.

 

Use the description box to explain the Macro.

Always use the description box to add as much info about the Macro as possible and dates when it was edited. This way you can always know what went wrong and when and by who, not to point blame but to understand what happened and minimize time spent on investigating the problem.

 

future

 

Use comments when you write a code.

Commenting can also be very handy when writing new code, recycling code or adding more details to your existing code to help others understand the purpose of your code.

 

Adding a comment is fairly easy. Go to your line of code and add an apostrophe (‘) in front of it. Anything on that line will turn green indicating that VBA is not recognizing it as a line of code, but seeing it a comment or note.

 

marco

 

 

Always use commenting creatively to lines of code you are unsure of, as well as adding notes to your code for people to see what that block of code is meant to do. You could also use the apostrophe to debug the code if it is not working correctly or clashing with another line of coding. Or instead of deleting code, you can use commenting to leave the code where it is and come back to it at a later stage.             

 

fg

 

If you want any other tips on Microsoft VBA then have a look at our other tips on Recycling Code and The Macro feature.

 

Want more information on Visual Basic? Then look no further, check out our VBA Courses and VBA Hints and Tips. 

 

Need more help? Then we’re here –> Message us on Facebook and Twitter 

 

 

 

Visual Basic Tip: The Macro Feature

Recording Macros is brilliant! Especially if you are not sure on how to word something initially. If you can’t decide what to use in your code, try recording macros that similar to what you are trying to do and see what is entailed in the code. The obvious next step would be to remove any unwanted or redundant code.

 

The first step is record a Macro. The best way to do this is click the Record Macro Button which is found on the Status Bar (the green bar at the bottom of your excel sheet).

 

njk

 

 

Then, the Record Macro Dialogue box appears.

 

 

huh

 

Be sure to fill out the Name and Description boxes with as much details as you can to future proof your Macro, as per below.

 

 

future

 

 

Click OK.  Now you will have to format the table as you would normally in Excel. Excel will record your steps as a Macro. Click the Record Macro button again to stop recording.

 

Next would be to view the Macros. To do this, Find the View Tab on the Ribbon, go to the Macro command group, and select View Macros.

 

ot

 

 

Click on the Edit button and this will bring up your Macro in Visual Basic. If you look at the screen grab below, you will see a simple Macro for selecting a range of cells and formatting it with bold, italic and adding a background colour.

 
fit

 

 

This should now give you an idea of the sentencing required to create a macro that grabs a group cells, formats it and adds background colour. Now all you have to do is use your VB know how and adapt the code to something more practical and universal that applies to all cells. Just remember to remove any redundant lines (for example .tintandShade = 0 isn’t needed so you can delete it).

 

Once your happy with your Macro, then add the code you think will work best (like looping or IF statements). By using this simple way of recording Macros and then editing them to your needs you can save yourself a huge amount of time writing code from scratch.

 

If found this hint and tip useful, you can always check out our Recycle Code tip for more ideas.

 

Want more information on Visual Basic? Then look no further, check out our VBA Courses and VBA Hints and Tips. 

 

For more hints and tips on other Microsoft Office applications sign up to our Mailing List.

 

 

 

 

 

Visual Basic Tip: Recycling Code

If you are new to Visual Basic, and want to start writing Macros for various MS application (most popular are Word and Excel), there are several ways in which you can utilize VB code without the need to spend time writing it from scratch. As with anything code and software related, while following our tips remember to test, test and test again to eliminate any problems, errors and to ensure quality and code is not causing any further problems or clashes when applied.

 

This is the No.1 tip I give whenever I conduct one of our Visual Basic Courses for Excel. Use code that has been written before and adapt it to your own uses. Google is great for such things, but you may spend a bit of time looking for the right code or website.

 

I always suggest checking out the Microsoft Developers Network (MSDN.com) for ideas on code when conducting of one our VBA courses (MSDN). You can always use their forums or Facebook and Twitter pages for a few hints and tips and exchanging code. You can also use the MSDN’s extensive knowledge base, documentation and help pages on a variety of VB related stuff as well as further your own knowledge. A brilliant tool for developers and programmers at any stage of experience.

 

recycle

 

 

We hope you found this tip helpful. If you want more information, check out our VBA Courses or our other Visual Basic Application Hints and Tips.

 

Was this tip helpful? Let us know, we would love to hear. Find us on Facebook and Twitter

Microsoft Excel VBA Tips: How to make a message box

Using Visual Basic of Applications can make your Excel workbooks a lot more user friendly and functional. In this blog post we will be looking how to create a message box in VBA for Excel 2007, 2010, and 2013.

 

The first message box we will be creating will be a small message box which shows the text “Hello World”. Before we can do this, we need to enable the developer tab on the ribbon. To do this you need to Click on File > Options > Customise Ribbon > and then check the developer tab like in the image below.

 How to make a message box 1

 

So on the developer tab; on the left hand side you can see a coding section. This is where you can easily access the Visual Basic Software, Macros and other options.

 

How to make a message box 2

 

Clicking the Visual Basic button will open up the Visual Basic Software. When making a message box, you are unable to record a macro to make this for you. You will need to type code to do it.

 

Here is what the Visual Basic Software looks like.

 

How to make a message box 3

 

To write some code we need to create a module to hold the code. To do this go to the top bar, click on Insert > Module

 

How to make a message box 4

 

Once you have done that you need then to create a Procedure to hold the Message Box.

Click Insert > Procedure > Type a name > Leave it set to Sub and Public > Click OK.

Note: The name cannot have a space in it.

How to make a message box 5


In the Coding window you can then type the following to create a message box

 

 How to make a message box 6

 

To run the Procedure/Macro, go back to your Excel window. This can be done by either closing the VBA window or just minimising it.

 

On the Developer tab > Click on Macros and the new HelloWorld Procedure/macro will be in here.

 

How to make a message box 7

 

Highlight the HelloWorld Macro and click run. You will get a little message appear with your message inside!

 

How to make a message box 8