DevCon 8 – MS Excel Beyond Formulas and Macros

Posted on

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,

Enabling Developer Tab

  1. Click the File tab.
  2. Click Options.
  3. Click Customize Ribbon.
  4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Writing the First Function

  1. Navigate to Developer tab and click on Visual Basic logo
  2. Right click on Microsoft Excel Objects folder in the left tree and navigate to Insert -> Module
  3. This is the playground, let’s write some hello word code. Copy past the following code to your module.
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
  1. Now Press the run button or press F5 to execute the code

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.

Macro is your best friend

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).

Excel Objects

Basically, there are two types of objects in Excel

VBA Objects

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 

COM objects

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
 

Connecting to an Access database:

 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

Global Object and Events

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:

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook_events.aspx

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.

TFS and Excel – The programmer heaven

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:

https://www.visualstudio.com/en-us/docs/work/office/bulk-add-modify-work-items-excel

Materials

You may download the Slide from here: DevCon8 – MS Excel

Financial Management Excel Sheet can be downloaded from here: ExcelFinancialManagement_v1


Leave a Reply

Your email address will not be published. Required fields are marked *