A little bit of Excel Automation

Posted: 2020-10-05

Excel applications are the good, the bad and the ugly of trading or logistics process. Spreadsheets are great as a simple database with couple of formulas to act as a business logic, however what happens when a spreadsheet becomes integral part of the business process?

There is a reason for this and this reason is either to support a new business initiative or to implement workaround due to an existing system deficiency. The situation becomes very ugly very quickly. The new process means copying and pasting data into Excel and then copying and pasting into Outlook to communicate it to another group. Some of the copy and paste errors we have seen in our career were counted into hundreds of thousands of dollars.

There are couple of tricks that can help to fortify Excel worksheets:

  • Lock the cells, such as only few input cells are editable
  • No overriding of formulas. For example, two trades are represented as two separate records rather than a single record with additional formula for combined quantity
  • Control sums with conditional formatting if values are outside of tolerance. For example, a scheduling spreadsheet can show supply and market for trades and storage and conditional formatting can flash warning in case of imbalance
  • Our favorite: Adding a bit of VBA to send an email via Outlook just by pressing a button

The last point is also a great time saver. Simply pressing a button opens up a new Outlook email with pre-populated content. This reduces number of copy-paste errors. Here how it works in practice:

Data input

Data Input

Pre-populated email

Data Input

Here is VBA for those who are interested in

Private Sub btnSendEmail_Click()
On Error GoTo ErrorHandler
    Dim EmailTo As String
EmailTo <span style="color:#666">=</span> <span style="color:#008000;font-weight:bold">Me</span>.Parent.Names(<span style="color:#ba2121">&#34;EmailTo&#34;</span>).RefersToRange.Value
<span style="color:#008000;font-weight:bold">Dim</span> EmailSubject <span style="color:#a2f;font-weight:bold">As</span> <span style="color:#b00040">String</span>

EmailSubject <span style="color:#666">=</span> <span style="color:#008000;font-weight:bold">Me</span>.Parent.Names(<span style="color:#ba2121">&#34;EmailSubject&#34;</span>).RefersToRange.Value
<span style="color:#008000;font-weight:bold">Dim</span> EmailBody <span style="color:#a2f;font-weight:bold">As</span> Range

<span style="color:#008000;font-weight:bold">Set</span> EmailBody <span style="color:#666">=</span> <span style="color:#008000;font-weight:bold">Me</span>.Parent.Names(<span style="color:#ba2121">&#34;EmailBody&#34;</span>).RefersToRange
SendEmail EmailBody, EmailTo, EmailSubject
<span style="color:#008000;font-weight:bold">Exit</span> <span style="color:#008000;font-weight:bold">Sub</span>

ErrorHandler: MsgBox "Got Error: " & Err.Description, vbExclamation End Sub

Private Function SendEmail(rng As Range, eTo As String, eSubject As String) Dim htmlContent As String, i As Long, j As Long htmlContent = "<table>"

<span style="color:#008000;font-weight:bold">For</span> i <span style="color:#666">=</span> 1 <span style="color:#008000;font-weight:bold">To</span> rng.Rows.Count
    htmlContent <span style="color:#666">=</span> htmlContent <span style="color:#666">&amp;</span> <span style="color:#ba2121">&#34;&lt;tr&gt;&#34;</span>
    <span style="color:#008000;font-weight:bold">For</span> j <span style="color:#666">=</span> 1 <span style="color:#008000;font-weight:bold">To</span> rng.Columns.Count
        htmlContent <span style="color:#666">=</span> htmlContent <span style="color:#666">&amp;</span> <span style="color:#ba2121">&#34;&lt;td&gt;&#34;</span> <span style="color:#666">&amp;</span> rng.Cells(i, j).Value <span style="color:#666">&amp;</span> <span style="color:#ba2121">&#34;&lt;/td&gt;&#34;</span>
    <span style="color:#008000;font-weight:bold">Next</span>
    htmlContent <span style="color:#666">=</span> htmlContent <span style="color:#666">&amp;</span> <span style="color:#ba2121">&#34;&lt;/tr&gt;&#34;</span>
<span style="color:#008000;font-weight:bold">Next</span>
htmlContent <span style="color:#666">=</span> htmlContent <span style="color:#666">&amp;</span> <span style="color:#ba2121">&#34;&lt;/table&gt;&#34;</span>

<span style="color:#008000;font-weight:bold">Dim</span> OutApp <span style="color:#a2f;font-weight:bold">As</span> <span style="color:#b00040">Object</span>, OutMail <span style="color:#a2f;font-weight:bold">As</span> <span style="color:#b00040">Object</span>
<span style="color:#008000;font-weight:bold">Set</span> OutApp <span style="color:#666">=</span> CreateObject(<span style="color:#ba2121">&#34;Outlook.Application&#34;</span>)
<span style="color:#008000;font-weight:bold">Set</span> OutMail <span style="color:#666">=</span> OutApp.CreateItem(0)

<span style="color:#008000;font-weight:bold">With</span> OutMail
    .To <span style="color:#666">=</span> eTo
    .Subject <span style="color:#666">=</span> eSubject
    .HTMLBody <span style="color:#666">=</span> htmlContent
    .Display
<span style="color:#008000;font-weight:bold">End</span> <span style="color:#008000;font-weight:bold">With</span>

End Function