Macro & Form Button

For those who are running older versions of Excel, now is a great time to consider upgrading to the 2010 version demonstrated in this video.  Now available on Amazon.com!

Rapid Fire Excel – Using the Ribbon Efficiently

Buttons

Are you growing tired of locating your macro?  If you are like me, you have generated so many small macros that work together or work well on their own.  The problem is, when you have too many of them and a select few you consider your favorites.  Here is where a button comes in real handy.

In Excel 2010 you generate a button by clicking the down arrow near the office button here:

  

Choose customize, macros then select the macro you wish to have represented as a button:

Just like that and your button will appear as follows:

Right click on this button and you can now change the face to one of the available images.  Making this a customized button is a bit more involved but you can read further on this at http://blogs.msdn.com/b/excel/archive/2009/07/17/add-buttons-to-the-quick-access-toolbar-and-customize-button-images.aspx

Excel 2007 Data Entry Form

http://spreadsheets.about.com/od/datamanagementinexcel/ss/090717_data_form.htm

d French

Conditioning

Of course, we are not talking about strength training for your body.  What we are talking about today is conditional formatting where you apply a format (i.e. cell color, bold font, etc) based on what the cell contains.

The easiest (and highly effective) method of conditional formats is through the built in function provided in Excel.  You select a series of cells then apply the conditions the where a cell in that range will receive that formatting.  In Excel 2010 you have the added luxury of shades of colors to demonstrate where the value falls within that range of cells (among your many choices).   This adds great value when you are dealing with large data sets and you wish to visually demonstrate the values.  When many formulas exist with varying degrees of complexity (maybe even feeding one into the next) you can use this to alter your variables to visualize the affect of those changes throughout the rest of your sheet.

Let’s say you want further flexibility in your formats?  For instance, you may want the user to define what range to use or you need to limit the file size for ease in sending.  You may want to identify your conditions through a code like that found in my last post:

---

Sub TrueFormat()

    With Selection

        .Font.Bold = True

        .Font.TintAndShade = 0.349986266670736

        .Interior.Color = 65535

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

    End With

End Sub

---

You can now reference this code within a set of conditions in VBA as follows:

---

Sub TrueFormat()

    With Selection

        .Font.Bold = True

        .Font.TintAndShade = 0.349986266670736

        .Interior.Color = 65535

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

    End With

End Sub

Sub FindTrue()

    For I = 1 To 100

‘if the value is more than 1 apply the true format to that cell

        If Cells(I, 2) > 1 Then

            Application.Run ("TrueFormat")

‘if the value is more than 1 apply another format to that cell

        ElseIf Cells(I, 2) < -100 Then

            Application.Run ("AnotherFormat")

.

.

.

        End If

    Next I

End Sub

---

The result is a conditionally formatted sheet without the excessive file size!  In addition, you can add as many notes as you like to your code with a leading apostrophe to help you follow the (sometimes larger than life) series of if/then statements.  Remember, it may make perfect sense to you now but if you have to revisit it years from now these notes will bring you up to speed far faster than without.

Pruning Macros

We may be a ways off from spring pruning but now is a great time to start pruning your macros.  When you record a macro you may notice that, when formatting a cell or group of cells, the resulting code to your recorded macro becomes quite long.  

---

Take the following recorded macro for example:

---

If you were to nest this within a loop the user could find themselves watching (and waiting) as Excel slowly chugs along.  Let’s take a look at how these 25 lines of script can be reduced to a more manageable number.

1.       First it asks to change the font of the cell to bold and to a different shade.

2.      Next it asks to change the color of that same cells interior.

3.       Finally, it draws a bottom border

Why then should a few steps turn into 25?  It should not when the same formatting can be accomplished through the following:

---

Sub Macro1()

' 

    With Selection

        .Font.Bold = True

        .Font.TintAndShade = 0.349986266670736

        .Interior.Color = 65535

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

    End With

End Sub

---

Since the selection remains the same throughout the script there is never a reason to re-use the selection statement.  Also, when items are being set to 0, xlAutomatic, xlNone you are asking Excel to revert back to its default.  Unless you have an already active sheet with formats altered from the default settings these are useless lines of code. 

With cleaner code, not only does Excel perform faster, but you are better able to apply this code within other scripts.  In addition, it becomes a lot easier to follow which makes for easy adaptation down the road especially when others must step in to maintain it without your support.

Keeping trim with Excel spreadsheets!

Ok, so it may not meet your new year's resolution, but it does have some great techniques!  Click here to see this latest entry from the Contestures blog ---> 30 Excel Functions in 30 Days: 03 - TRIM

Also, take a peak at the following guide to pivot tables in Excel available on Amazon.com:

Creating Easy to Use Functions Customized through Excel VBA

Have you ever encountered a ridiculously long function where it is way too difficult to figure out?  Take the following:

=IF(B3>1,IF(D3<0,IF(E3="",G3+1,0),G3-1),H3)

You can walk through the steps of the above formula but then the next time you wish to adapt it you will have to decipher this once more.  .

User-defined functions can simplify things for you by creating the labels that make sense for the formula while leaving the complexities of all the if/then logic behind the scenes.  An example would be as follows:

---

Function Order(Confirmed, Qty, Price, TaxRate)

If Confirmed = Yes then

                        Order = Qty * Price * TaxRate

            Else

                        Order = 0

End If

End Function

---

When the user clicks on the fx from the formula bar and selects from the category User Defined and choose Order from the list of functions below they will see the following:

After hitting ok, the formula will appear as follows: 

Imagine how you can incorporate user-defined functions within a VBA script that defines which fields it references or what values to use.

Search Commands Finds Functions Fast in Microsoft Office

When you cannot find a function on the 2007 ribbon this could come in handy:

Search Commands Finds Functions Fast in Microsoft Office

Spreadsheets: Protecting All Worksheets

This is a great article from Bill Jelen - CFO.com on using VBA to protect all sheets within a workbook:

Spreadsheets: Protecting All Worksheets

Visual Basic User's Guide

For programming in VBA script for Excel, this is a very inexpensive guide to help you get started:

Visual Basic User's Guide: Microsoft Excel Version 5.0: Automating Customizing and Programming in Microsoft Excel with the Microsoft Visual Basic Programming System Applications Edition

Speeding up your macros with VBA script

Removing auto-calc – You can switch off auto calculations in Excel manually by switching your calculation options to manual (found under tools menu in Excel 1997).  Another strategy would be to switch this off within your code through the command Application.Calculation = xlCalculationManual.  Be sure to include the command Application.Calculation = xlCalculationAutomatic at the end of your code to avoid user confusion when the calculations do not perform after the script is run.  When testing you need to remember this will switch to manual calculations will remain in affect long after you have finished your testing.

Removing refresh – Excel is constantly refreshing your screen.  When running a macro, it is not necessary to display your results after each step that is taken.  Every second that Excel attempts to refresh your screen to show your latest results is a second taken away from processing your script.  To remove this anchor you will need to switch this off through the command Application.ScreenUpdating = False and switch it back on at the end of your code with the command Application.ScreenUpdating = True.

Declaring Variables - When using scripts that contain variables such as for I = 1 to 100 you can improve your speed by declaring that variable first.  Declaring I as an integer will tell your computer what to expect when it encounters this variable I and wont reserve too much memory by treating it as a potential 256 character variable.