Automate Excel

You may need a workbook to always open to a specific view or location on a sheet, or possibly just change the view from a macro. You can do this by using the ScrollRow and ScrollColumn.

ScrollRow:
Used to programmatically scroll a spreadsheet vertically. This example will scroll a spreadsheet to row 5.

ActiveWindow.ScrollRow = 5

ScrollColumn:
Used to programmatically scroll a spreadsheet horizontally. This example will scroll a spreadsheet to column 5.

ActiveWindow.ScrollColumn = 5

And placing the following code in a module will always scroll a workbook’s Sheet1 to row 5 and column 5 if macros are enabled upon opening:

Sub auto_open()

    Sheet1.Activate
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollRow = 5

End Sub

This one is not very practical, especially since you need to alter each cell comment(or copy and paste), but still worth noting.

To modify a cell comments autoshape:

Insert a comment
1. Right Click a cell and select “Insert Comment”, type the text you’d like as your comment.

Edit the comments autoshape
1. Right click the cell and select “Edit Comment”
2. Click on the gray square border surrounding the comment, this takes it out of edit mode.
3. On the drawing toolbar select the word “Draw”, this should be on the left most part of the drawing toolbar for most.

4. From the popup select “Change Autoshape”, and pick the new autoshape for your comment.

A comment can be added to a formula by adding an N function. Or
=Formula+N(”YourTextHere”)

The =N() function returns the number for a given cell. For instance if 1% is in a percentage formatted cell A1, putting =N(A1) in another cell will return the number .01

However when the N function can’t convert the given information it returns 0. So to add a comment to a formula we simply add an N function to the formula, which in turn returns zero, and doesn’t alter the formula in most cases.

This picture demonstrates the use of the N function, added to an existing formula to provide a comment.

The ability to use Excel’s built in functions when programming with VBA can be a great time saver.

In this example I’ll use the Combin function. Remember Combin is used to return the maximum combinations when given the number in the pool and the number drawn. The following code demonstrates a worksheet function used in VBA to find the combinations when given a pool of 42 with a draw of 6:

Sub UseFunction()

MsgBox Application.WorksheetFunction.Combin(42, 6)

End Sub

Running this macro tells us in a message box the result of the Combin function, and we’ve successfully used a worksheet function with VBA.

There are many reasons you may want to be able to programmatically draw boxes, an automated gantt chart would be cool? Here is one way to do it.

This is the basic code to draw a box:

ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, 0, 0, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

It’s really not that daunting, there are only two notable things to look at; Schemecolor is obviously the color and the 0,0,48,12.75.

So what’s the 0,0,48,12.75? Simply the Left Position, Top Position, Width, and Height. You can adjust these however you would like, adjusting the first two to change the boxes position on the screen, and the latter two to change the size of the box. I used these dimensions becuase they were a good guess at making a box the same size as a cell.

And here is the code used to dynamically draw the green boxes in the above picture:

Sub DynamicBoxes()

   Dim x As Double

   'This makes horizontal boxes
   For x = 0 To 240 Step 48

    'reference to the 4 numbers left,top,width,height
 ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, x, 0, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

    Next x

    'This makes vertical boxes
    For x = 0 To 127.5 Step 12.75

ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, 0, x, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

    Next x

End Sub

So you want to sum a range based on a condition eh? For instance you want to sum the total for just one person in a range of names? Like this picture sums the range A2:A10 on the condition the cell equals “Mark”, and does this in cell D3: The easiest way is with Sumif, but here’s another just for fun.

The syntax to do this:

=Sum(If(RangeWithCondition=”condition”,RangeToGetSum,0))

And hit Control, Shift and Enter to make it an array formula, it won’t work without doing this. (this adds the Braces {} around your formula)

In a cell you want a conditional sum:

1. Type =Sum(If(
2. Click and Drag the Range with the condition, in this example I click and dragged A2 to A10, this automatically filled my formula
3. Type equals (=)
4. In quotation marks type the condition you want to test for, in this example I typed “Mark”, because I want to sum if this condition is true
5. Type comma (,)

Now if it finds the specified range has a condition equal to “Mark”, we need to tell it where to get the numbers to sum.

6. Click and Drag the Range with the numbers to sum if the condition is true
7. Type ,0)) (this will display zero if nothing is found and closes the formula)
8. And most importantly, hit Control, Shift and Enter to make it an array formula

When using VBA to code the Colorindex (or background color) of a cell it is useful to know what integer will equal what color. Below is a reference picture which shows the color and lists it’s respective Colorindex. aka VBA Color Palette

Sub ColorRef()
Dim x As Integer

For x = 1 To 56

If x < 29 Then
Cells(x, 1).Interior.ColorIndex = x
Cells(x, 2) = x
Else
Cells(x - 28, 3).Interior.ColorIndex = x
Cells(x - 28, 4) = x
End If

Next x

End Sub

It’s pretty easy to cut and paste from a macro. Here are a few examples. The code works identical for copy, just replace the word cut with copy!

This one cuts and pastes a single cell, a1 over to b1:

Sub OneCell()

    Range("A1").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire column, A over to B:

Sub OneColumn()

    Range("A:A").Select
    Selection.Cut
    Range("B:B").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire row, 1 over to 2:

Sub OneRow()

    Range("1:1").Select
    Selection.Cut
    Range("2:2").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

Using VBA you may need to write to the first empty cell, or after the last row used in a column. There is no need to loop to find this, the following code does it for you.

In this example the code will write “FirstEmpty” in the first empty cell in column “d”

Public Sub AfterLast()

ActiveSheet.Range("d" & ActiveSheet.Rows.Count) _
    .End(xlUp).Offset(1, 0).Value = "FirstEmpty"

End Sub

How do you count the blank spaces in a range? With the COUNTBLANK function of course.

1. In a cell you want the result type =COUNTBLANK(
2. Select the range you want to count the blanks in (this automatically fills your formula)
3. Hit enter

In the picture cells B3 and D2 are blank, the result of the COUNTBLANK function is in cell D6