I recently discovered how quick it is to create a simple Gantt chart in Excel.
The following example was created for Excel 2003. It should work the same for other versions.
Create a worksheet similar to the above table.
Note: the formula in column D2 is =C2-B2. You’ll need to set the Cell Format back to General, or column D will look like a date.
Select just the Activity and Start Date data (eg. A1:B5), then click the Chart Wizard.
Step 1 of the wizard:
Select the “Bar” Chart type, and the Stacked Bar sub-type, then click Next.
Step 2 of the wizard:
Click the Series tab, and Add another Series.
For Name, select the cell for the Duration Column Header (cell D1)
For Values, select the cells for for the Duration data (cells D2:D5)
Click Next
Steps 3 and 4 of the wizard can be skipped, though, on Step 3 you may want to hide the Legend.
The idea from here is to make the Start Date bar invisible, so only the Duration bar shows.
Double click on any one of the Bars for the Start Date data.
From the Patterns tab, set the Border and Area to None.
You may also notice the Activities are in reverse order.
Double click the Y axis (Category axis). A “Format Axis” window should appear.
From the Scale tab, tick the “Categories in reverse order” and “Value (Y) axis crosses at maximum category” are ticked.
You’re finished!
As an extra, you can force the chart to graph only between certain dates.
Double click the X axis (Value axis). A “Format Axis” window should appear.
From the Scale tab, override the Minimum and Maximum boxes with Dates (I didn’t know it could take dates, did you?!)
Sometimes I find myself copy-pasting between Excel and another application.
In this example, I have a table of three columns: First Name, Last Name, Birth Date.
My external application has 3 text boxes, one for each of those values.
I can't just copy the 3 cells from Excel and paste them to my App, because they would all end up in the first text box!
But, by running VBA SendKeys with a specially crafted string, I can send keystrokes for tabbing to the 2nd and 3rd text boxes.
I also need to activate the SendKeys procedure only when my cursor is positioned correctly, or things could get messy.
My approach is to run a macro that sits there listening for F6 before activating SendKeys.
I've also included listening for the Esc key, just in case I change my mind.
Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
' Virtual-Key Codes http://msdn2.microsoft.com/en-us/library/ms645540(VS.85).aspx
Const VK_F6 = &H75
Const VK_ESC = &H1B
Sub Scanning()
Dim lngRow As Long, str As String
lngRow = Selection.row
str = Cells(lngRow, 1) & vbTab & Cells(lngRow, 2) & vbTab & _
Format(Cells(lngRow, 3), "dd-mmm-yyyy")
MsgBox "Click OK, then click the First Name box on the external application, then press F6 on the keyboard"
WaitAndSend str, VK_F6, VK_ESC
End Sub
Sub WaitAndSend(SendString As String, ExecuteKey As Long, CancelKey As Long)
Do
DoEvents
If GetAsyncKeyState(CancelKey) <> 0 Then Exit Do
If GetAsyncKeyState(ExecuteKey) <> 0 Then
SendKeys SendString, True
Exit Do
End If
Loop
End Sub
I had a worksheet table with blank rows separating the groups.
I needed to add another column - a formula - but wanted to retain the blank rows for formatting tidiness.
The table after adding the formula column:
Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.
Here's how I did it:
- Select column C.
- From the Edit menu, select Go To..., then click Special...
- Select Blanks, then click OK
Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.
Sub SelectionOffset()
Dim strInput As String, str As String, i As Long, bln As Boolean
Dim strRows As String, strCols As String
strInput = ""
Do
bln = False
strInput = InputBox("Selection offset by rows, cols" & vbNewLine & _
"eg. 12, 2", "Selection offset", strInput)
str = Replace(strInput, " ", "")
If str <> "" Then
i = InStr(str, ",")
If i = 0 Then strRows = str Else strRows = IIf(i = 1, "0", Left(str, i - 1))
If i = 0 Or i = Len(str) Then strCols = "0" Else strCols = Mid(str, i + 1)
If IsNumeric(strRows) And IsNumeric(strCols) Then
On Error Resume Next
Selection.Offset(strRows, strCols).Select
If Err.Number <> 0 Then
MsgBox "Invalid selection offset", vbExclamation, "Error"
bln = True
End If
On Error GoTo 0
Else
MsgBox "Selection offset is not numeric", vbExclamation, "Error"
bln = True
End If
End If
Loop While bln
End Sub
El Says:
"I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day."
This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals...
At each change in StartDate, Use function Sum, Add subtotal to Duration.
Here is one for the Code Library.
Somehow I end up misplacing this bit of code. So every time I need to do it, I end up re-writing it.
Perhaps I'll save someone the same frustration along the way.
This code snippet will loop through each file in your folder (and subfolders).
For each workbook opened, it will unprotect each worksheet using the supplied password.
Const cStartFolder = "D:\MySecretSpreadsheets" 'no slash at end
Const cFileFilter = "*.xls"
Const cPassword = "trustno1"
Sub UnprotectAllWorksheets()
Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet
ExtractFolder cStartFolder, arr()
On Error Resume Next
j = -1: j = UBound(arr)
On Error GoTo 0
For i = 0 To j
Set wkb = Workbooks.Open(arr(i), False)
For Each wks In wkb.Worksheets
wks.Unprotect cPassword
Next
wkb.Save
wkb.Close
Next
End Sub
Sub ExtractFolder(Folder As String, arr() As String)
Dim i As Long, objFS As Object, objFolder As Object, obj As Object
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(Folder)
For Each obj In objFolder.SubFolders
ExtractFolder obj.Path, arr()
Next
For Each obj In objFolder.Files
If obj.Name Like cFileFilter Then
On Error Resume Next
i = 0: i = UBound(arr) + 1
On Error GoTo 0
ReDim Preserve arr(i)
arr(i) = objFolder.Path & Application.PathSeparator & obj.Name
End If
Next
End Sub
From time to time, I am asked to audit a spreadsheet.
One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
- A1 has the formula =G1 * 0.105
- A1 has been formula auto-filled to A1:E5
- Then some time later, cell A3 has been changed to =G3 * 0.107
So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.
It's nice to be able to highlight these exceptions. Here are 2 ways.
1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that "something strange" is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?
2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:
Sub test()
Dim strFormula As String, rng As Range
strFormula = Selection(1).FormulaR1C1
For Each rng In Selection
If rng.FormulaR1C1 <> strFormula Then rng.Interior.ColorIndex = 6
Next
End Sub
To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).
I often use Excel's macro recorder to perform repetitive tasks.
It's usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish - I have to give each item a brief glance before the macro runs.
At the end of the macro, it's handy to position the selected cell as the start of the next item in the list.
I'll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w... over and over.
You can reassign your shortcut keys from the Macros window:
From Excel's menu: Tools, Macro, Macros... (or hit Alt+F8)
Highlight a macro, then click Options.
More often than not, the macro recorder will give me a good first draft but I'll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.
Could it be that Excel recognises the code comment?
So I deleted the comments to be sure. No, it wasn't the comment.
It turns out that the shortcut key is stored in the Code Module, but it's hidden from sight.
Export the Code Module (right-click the Module, click Export File) then open it in Notepad.
You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = "w\n14"
I had a strange one last week while assisting a user reported problem.
I've got a workbook and a VBA referenced XLA.
The workbook loaded fine, but produced errors when you tried to execute macros.
The debug point was on the "Left" function, which is a classic symptom of a broken reference.
Checking the VBA Project, I noticed that the referenced XLA was not loaded at all, even though it was referenced.
The XLA was in the right spot. Security and permissions were OK.
No matter how many times I closed and re-opened Excel, it still would not load that referenced XLA.
I even tried rebooting the computer.
It turns out that the user had suffered a "serious crash" just hours earlier. The next time he started Excel is came up with all sorts of recovery questions, which went something like 'this workbook suffered a serious crash, are you sure you want to open it?'.
Excel does this for all the suspect workbooks - even referenced XLAs - except that with referenced XLAs, that question is not presented to the user, it just assumes 'no, you dont want to open this corrupt workbook' (though it wasn't corrupt at all).
The fix:
I opened the XLA file by itself, answered the recovery question and all was good next time Excel ran.
That's not the only way to do it though.
It turns out that Excel maintains a list of Disabled Items that you can enable by going into Excel's menu:
Help > About Excel > Disabled Items...
Then highlight the items and click Enable.
A fairly strange place to manage disabled items. I guess since it's an Office-wide feature they might have struggled for UI consistency - who knows?
I spent some time trying to simulate a crash, but couldn't. It must have been pretty serious!
