Microsoft excel is a powerful tool used by billions of people around the word, programmer as well. Power users are able to perform arithmetic operations using macros and formulas. However, excel has a hidden gem that opens a new paradise for programmers. The ability to modify excel sheets using Visual Basic for Application (VBA) directly from Excel interface. In this post, I am going to share my experience using this tool with you all,
Public Sub HelloWorld() Columns("E:E").ColumnWidth = 65.29 Rows("8:8").RowHeight = 210 Range("E8").Select With Selection .FormulaR1C1 = "Hello World" .HorizontalAlignment = xlGeneral .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .VerticalAlignment = xlCenter .Font.Size = 70 End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone ActiveWindow.DisplayGridlines = False Range("A1").Select End Sub
The beauty of VBA is that it does not require any explanation. It’s pretty self-explanatory and if you have programming background, you should be able to read and understand it. By coming this far, you already have what you need to start programming your excel sheet. Below is some of the techniques that I learned during my time working with excel that you might find it useful as well.
When you record a Macro, Basically Excel will follow your every move and logs it as a VBA script. This is very useful when you are looking for a code to perform a certain action. For example, you want to create a pivot table and assign it a data source. Googling it will not help you much since there are very limited resources out there. So all you have to do is to perform the operation using Microsoft Excel UI while recording a Macro. After stopping the Macro, you can find the related code in the Excel Code editor (new module is automatically created for a new Macro).
Basically, there are two types of objects in Excel
VBA objects are built in the application and can be created using Dim and/or Set keyword. These objects consists of Event, Method, and property. To find out the full list of available objects press F2 in Visual Basic Editor. Here is a sample code and some of the useful objects that you can use.
Dim wk As W<code>rkbook Set wk = Workbooks.Open "C:\Docs\Accounts.xlsx" wk.SaveAs "C:\Docs\Accounts_Archived.xlsx" Dim collFruit As New Collection ' Add item to the collection collFruit.Add "Apple" Dim a As Worksheet Set a = ActiveWorkbook.ActiveSheet a.Cells(2, 2) = "Some text"
Note: You can use the Set keyword solely (without using dim to define the object). Despite the technical differences between two method, the biggest drawback of using Set alone is that you won’t be getting any intellisense since object type is unknown to the code editor.
You also can subscribe to an Object event by writing a procedures like the one below:
Private Sub Worksheet_Activate() MsgBox "Sheet1 has been activated." End Sub
These object are not Excel native and derived from other applications. These include the Dictionary, Database objects, Outlook VBA objects, Word VBA objects and so on.
Set dict_Month = CreateObject("scripting.dictionary") If Not dict_Month.exists(.Cells(i, 1).Value) Then dict_Month.Add .Cells(i, 1).Value, CreateObject("scripting.dictionary") End If
Public Function RunQuery(ByVal strDBPath As String, _ ByVal strQueryName As String, _ Optional ByVal execOption As Integer = 4, _ Optional ByRef rowAffected As Long = 0, _ Optional ByVal showError As Boolean = True _ ) As Object Dim objCmd As Object Dim objRec As Object Dim varArgs() As Variant Set objCmd = CreateObject("ADODB.Command") With objCmd On Error Resume Next .ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strDBPath & ";" & _ "Jet OLEDB:Engine Type=5;" & _ "Jet OLEDB:Database Password=""" & DBPassword & """;" & _ "Persist Security Info=False;" .CommandText = strQueryName 'If UBound(parArgs) = -1 Then Set objRec = .Execute(RecordsAffected:=rowAffected, Options:=execOption) If Err.Number <> 0 And showError Then 'Display an error message to the user. MsgBox "Ooops. There was an error while generating report! Please contact administrator if the problem persists", vbCritical, "Generation Error" Sheets("start").Range("A30").Value = Err.Description Sheets("start").Range("A31").Value = strQueryName Err.Clear End If End With Set RunQuery = objRec Set objRec = Nothing End Function
You have the option of having workbook-wide variable or set some variable values at workbook start up. All you need is a procedure with the event name in the ThisWorkbook object. For example to perform some warm up activity when the sheet opens:
Private Sub Workbook_Activate() Worksheets(1).Cells(1, 1).Value = "I am here:)" End Sub
Full list of events:
It is also possible to have Workbook wide variable using Global Key word
Global Const aSheetName = "Detail Report"
However, I would recommend having a hidden sheet where you set your constant values. This is useful to save memory and also have the value saved for next usage.
Another hidden treasure of Excel is its ability to connect to TFS. You are able to bulk insert your TFS items into the Server or download everything from server and use Excel tools to perform some Analysis. The full tutorial on this can be found from the link below:
You may download the Slide from here: DevCon8 – MS Excel
Financial Management Excel Sheet can be downloaded from here: ExcelFinancialManagement_v1