Friday, January 14, 2011

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
    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!

Wednesday, January 12, 2011

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:

Monday, January 10, 2011

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

Sunday, January 9, 2011

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.