Excel Macros (part 6)- Analyzing Macros and Identifying Errors

Excel Macros (part 5)- Using Ranges, Rows and Columns

Win an Xbox & Kinect Package

Win an Xbox & Kinect Package!

Excel Macros (part 004) - Loops and Conditions

Wrapping text within a cell in Excel using VBA

---

The most simplest way to make a cell word wrap is to right click and format the cell alignment to wrap text.  This makes the text begin on the next line within that cell when it reaches the right cell wall.  You increase the size of the cell and more text appears on the first line with less on the next.


There may, from time to time, be a case where you need to begin a new line regardless of the size of that cell.  VB needs to see a carriage return in order to accomplish this.  A carriage return is represented by Chr(10) and may be inserted into the cell directly as follows:

---

ActiveCell.FormulaR1C1 = "ABCD" & Chr(10) & "EFGH"

---

Of course, you may want to incorporate this several times based on a specific length.  Here is one way this might be accomplished:

---

Sub AddCarriageReturns()
'
' Macro created 1/13/2011 by Steve Wynne
'
Dim CarriageInsert
Dim FullString As String
Dim I As Integer
'
I = Len(ActiveCell)
Do Until I = 5
    I = I - 5
    CarriageInsert = Mid(ActiveCell, I, 5)
    FullString = CarriageInsert & Chr(10) & FullString
Loop
    ActiveCell = FullString
End Sub

---

Try to nest this within a for/next or loop statement and it can be applied to an entire range of cells!

Different Types of Macro

---

Basic Macro Math

---

Introduction to Macros

---

 

Win an Xbox & Kinect Package

For more go to the Excel contest tab on the Excel Group in Facebook

Calculations that slow you down

When you are working in a workbook you may find yourself frustrated by how slow it is performing.  Often the computer is the first to receive the blame but here are some areas to consider before throwing your pc out the window.

---

First, if you have a lot of formulas, ask yourself do I need them to constantly refreshing?  If the answer is no then try switching your calculation options to manual.  When you are done with all your set-up you can switch back to automatic or even hit F9 whenever you are ready to see your results.  

---

If the values provided by your formulas are all you need, and the values used in the calculations are no longer changing, try to copy| paste special then selecting values.

---

Using VBA you can use set the cell equal to the value of a given formula or function as follows:

• Cells(1, 2) = Application.VLookup(Cells(1, 1), Range(Columns(4), Columns(5)), 2, False)

Notice how I reference the cells with numbers rather than letters? The first is the row reference and the second a column reference (opposite from the A1 you may be accustomed to).  These numbers can now be substituted by variables which is very handy when nesting formulas within loops or if/then statements.  

---

To learn more about the full range of formulas and functions available in Excel 2007:

Create an Excel UserForm, Part 3 of 3

Create an Excel UserForm, Part 2 of 3

Creating a simple Excel User Form

Get a jump start on Excel VBA with the following book available now at Amazon.com!

Scroll Bars - Financial Modelling

What do you do when you are given a set of targets to meet with a forecast that is not quite meeting them?  You may have several variables involved and you need to find what affect each change has to your model.  As they say, a picture is worth a thousand words, and when dealing with data it could be no further from the truth.

---

Enter the scroll bars pared with charting (click on image for full view):

Did you notice how the chart to the lower right is impacted by the change in variables?

---

For the user, you simply one or more of the scroll bars up/down to increase or reduce these input values to see (in real time) the impact within the chart to the lower right.  To create these scroll bars, you need to follow these 5 simple steps:

1. From the developer ribbon select insert then choose the following:
2. From the location you wish to locate this control click and drag to the size you wish this to be.
3. Right click and select format control.
4. From the control tab you can set your minimum and maximum values for this control and by what increment each click will change it.
5. In cell link choose the destination for the value present in the scroll bar.

---

For a more about modeling with Excel 2007

 

Scenario Analysis in Excel

Buttons that Pop-Up

Create Pop Up User Message Boxes

This clip demonstrates how easy it is to generate pop-up windows with buttons where you can incorporate user feedback as variables within your code.  Using these options allow you to expand your macros beyond simply automating repetitive tasks to incorporating differing scenarios to drive what macro or function to use next.