Heartbeat/watchdog service in MS Excel

We have already covered automating Excel tasks at work -- and indeed it can be a life saver (or rather, work-life balance saver) not to have to be present at work every single day at time XX:XX no matter what only to have to push a few buttons.

However, there is an inconvenience that no automation is 100% fool-proof, especially in a corporate environment. The automation computer may be restarted (by your colleagues or your IT department who decided to push some security updates). It may lose power (because the cleaning personnel got a bit too vigorous with their brooms). It may crash, or freeze, or have a hardware failure. It is particularly nasty is the automation computer is a shared terminal that only gets used once in a while, so a user log-off due to restart may not be immediately detected. And of course once the user is logged off, no code nothing written by that user can be invoked any more, and needless to say that admin access to all computers in the corporate is strictly verboten.

One way of getting around the inconvenience would be to implement a "heartbeat" or "watchdog" service that would periodically query the availability of your automation machine and "phone home" if it goes offline.

The general idea is as follows:

• The "server" is an Excel workbook that does nothing and resides in the user's private folder on the shared drive (most corporate environments have that), so that it's accessible from any computer in the office but only by the particular user. This worksheet contains no code, but is configured to open at startup on the automation machine.
• The "client" is another Excel workbook that runs on the user's regular desktop (which is assumed to be operational). 
• The client polls the server by trying to open the server workbook (assuming that if it's locked for editing, it's open on the automation machine so the latter is operational). 
• Should the server workbook become unlocked, this means that the automation machine has logged the user off and automation won't run. The client then informs the user by sending an email. 

To determine whether a file is open on the automation machine, I use the following macro (adapted from this idea):

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
    ' Check to see which error occurred. Open for 
    IsFileOpen = (errnum = 70)
End Function

The periodic polling code may look something like this:

Sub Heartbeat()
 Dim monitor As String
 Dim span, tick, fail, restart As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value: fail = .Range("numfail").Value: restart = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("INFO", "Heartbeat server detected")
  Else
    .Range("tick").Value = .Range("tick").Value + 1
    Call LogMessage("WARN", "Heartbeat server NOT detected")
  End If
  If .Range("tick").Value < fail Then
     Application.OnTime Now + span / 86400, "Heartbeat"
  Else
     Call LogMessage("FAIL", "Heartbeat server has failed, REPORTING FAILURE")
     Call SendEmail(.Range("ReportMail"))
     Application.OnTime Now + restart / 86400, "RestartHeartbeat"
  End If
 End With
End Sub

The idea is that the macro will restart itself using Application.OnTime if all goes well, until the server is not detected several times in a row (to guard against false positives due to intermittent network failures). In that case, a report is set via Outlook (adapted from here and possibly here) using this function:

Sub SendEmail(params As Range)
    Dim OutApp As Object, OutMail As Object, strbody As String, toaddr As String, ccaddr As String, subj As String
    strbody = "": toaddr = "": ccaddr = "": subj = "":
    Dim here As Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set here = params(1, 1)
    While Len(here.Value) > 0
       If UCase(Left(Trim(here.Value), 2)) = "TO" Then toaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "CC" Then ccaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "SU" Then subj = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "BO" Then strbody = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 1)) = ":" Then strbody = strbody & vbNewLine & here.Offset(0, 1).Value
    Set here = here.Offset(1, 0)
    Wend
    On Error Resume Next
    With OutMail
        .to = toaddr
        .CC = ccaddr
        .BCC = ""
        .Subject = subj
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing: Set OutApp = Nothing
End Sub

Note that the parameters for the email are conveniently stored on the spreadsheet for easy editing, so that only the range pointing to the parameter block needs to be passed. The email can then be easily defined using something like this:

After the failure report is sent, a slightly different macro is invoked:

Sub ReStartHeartbeat()
 Dim monitor As String, span As Integer, autoreset As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  autoreset = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("OK", "Heartbeat server restarted, monitoring resumed")
    Call SendEmail(.Range("ReSetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    .Range("retick").Value = .Range("retick").Value + 1
    Call LogMessage("FAIL", "Heartbeat server NOT restarted, keeping on trying")
    Call SendEmail(.Range("ReSetupErrorMail"))
    Application.OnTime Now + autoreset / 86400, "ReStartHeartbeat"
  End If
 End With
End Sub

Here the idea is to keep reminding the user that the server is still down, and getting  back on track once the user has successfully corrected the problem.

The entire process is set into motion by two macros:

Sub StartHeartbeat()
 Dim monitor As String, span As Integer
 Call LogMessage("INFO", "Service started")
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    Call LogMessage("OK", "Heartbeat server detected, monitoring started")
    Call SendEmail(.Range("SetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    Call LogMessage("WARN", "Service not started, heartbeat server not detected")
    Call SendEmail(.Range("SetupErrorMail"))
  End If
 End With
End Sub

Private Sub Workbook_Open() ' Put this in the Workbook code rather than Module1
 If ThisWorkbook.ReadOnly Then Exit Sub ' DO NOT execute if read only OR
 If Workbooks.Count > 1 Then
  Call LogMessage("WARN", "Started in non-clean session, entering setup mode. Service NOT starting. For production, run in a CLEAN session.")
  ThisWorkbook.Save
  Exit Sub ' ONLY execute in clean, dedicated session
 End If
 ThisWorkbook.Worksheets("Control").Calculate
 Application.OnTime Now + TimeValue("00:00:05"), "StartHeartbeat"
End Sub

The safeguards in place make sure that opening the client for debugging or viewing (or by accident) do not start spurious monitoring processes (.OnTime is nasty, once set it will persist untill that particular Excel session is ended, even after the workbook containing the .OnTime was closed). So the client only starts if the file is only opened in a clean, dedicated Excel session.

Finally, an auxiliary subroutine, purely aesthetic, is used to log the monitoring actions. Here goes:

Sub LogMessage(code As String, msg As String)
 Dim here As Range, c As Integer, level As Integer
 level = 255
 If UCase(Left(Trim(code), 3)) = "INF" Then level = 10
 If UCase(Left(Trim(code), 2)) = "OK" Then level = 5
 If UCase(Left(Trim(code), 3)) = "WAR" Then level = 2
 If UCase(Left(Trim(code), 3)) = "FAI" Then level = 1
 If UCase(Left(Trim(code), 3)) = "ERR" Then level = 0
 
 For c = 1 To 2
  Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range("A1")
  If c = 2 And level >= 2 Then Exit For
  While Len(here.Value) > 0
   Set here = here.Offset(1, 0)
  Wend
  With ThisWorkbook.Worksheets("Control")
   .Calculate
   here.Value = IIf(Len(code) > 0, code, "___")
   here.Offset(0, 1).Value = Now
   here.Offset(0, 2).Value = .Range("monitor").Value
   here.Offset(0, 3).Value = .Range("span").Value
   here.Offset(0, 4).Value = .Range("numfail").Value
   here.Offset(0, 5).Value = .Range("tick").Value
   here.Offset(0, 6).Value = .Range("autoreset").Value
   here.Offset(0, 7).Value = .Range("retick").Value
   here.Offset(0, 8).Value = msg
   Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range(here, here.Offset(0, 8))
   Select Case level
   Case 10
    here.Font.Color = RGB(200, 200, 200)
   Case 5
    here.Font.Color = RGB(0, 128, 0)
   Case 2
    here.Font.Color = RGB(255, 128, 0)
   Case 1
    here.Font.Color = RGB(128, 0, 0)
   Case 0
    here.Font.Color = RGB(255, 0, 0)
   Case Else
    here.Font.Color = RGB(255, 0, 255)
   End Select
  End With
  If c = 2 Then ThisWorkbook.Save ' only save on error
 Next c
End Sub

Excel VBA: Automatic macro execution for Windows Task Scheduler (and a few pranks to boot)

Despite all modern trends towards workflow automation, manual Excel-based workflows still abound in many areas of industry, notably finance. In our previous post we learned how to simplify and automate some of them, reducing manual copy-and-paste to a button push.

The next step of this would be further automation, so that the"button" gets pushed automatically, for example on a daily basis at a predetermined time.

One intuitive approach would be to invoke your code using an Auto Macro such as Workbook_Open, and then have Windows Task Scheduler open your workbook on schedule as required.

However, there is a catch here. Workbooks used for automated workflow are very often also used for ad-hoc manual tasks, or are simply opened to view some data, with no intention of performing any (re)calculations. Now your auto macro code would run every time the workbook is opened. If the automated workflow involves some data modification and/or email notification, you will have side effects very time you open your workbook just to see what is in there.

Worse still, if your workbook is on a shared drive, other used may stumble upon it by accident, triggering execution of the auto macros when they never expect it.

Of course, you could run two copies of your spreadsheet - one for automation and one for manual inquiries, naming the "automation" one something like "never_ever_open_this_manually.xlsm", but what happens in practice in such cases is that the two workbooks fall out of sync, and one of them is always out of date (or worse, they are both partially out of date).

A much more elegant solution would be for the auto macro to detect that the workbook was invoked by the Task Scheduler and not manually, and run the workflow only in this case.

After some probing and looking for inspiration I have come up with this solution:

Private Function IsScheduled() As Boolean
 ' Determine if the workbook was opened manually or as part of a Task Scheduler routine
 On Error GoTo Decided
 Dim Result As Boolean
 Result = True ' An invisible or programmatically started session is always assumed scheduled
  If Not Application.Visible Then GoTo Decided
  If Not Application.UserControl Then GoTo Decided
 Result = False ' A session with more that one workbooks is always assumed manual
  If Application.Workbooks.Count > 1 Then GoTo Decided
 ' otherwise assume scheduled task if the workbook name was supplied in the command line
 Dim wname As String, cmdline As String
 cmdline = UCase(Trim(GetCommandLine)): wname = UCase(Trim(ThisWorkbook.Name))
 Result = InStr(cmdline, wname) > 0
Decided: IsScheduled = Result
 On Error GoTo 0
End Function

Private Sub Workbook_Open()
 If Is Scheduled Then
  ' Run your automated workflow here
 End If
End Sub

It basically relies on the discovery that if a workbook is started by the Task Scheduler, its command line would be

C:\Program Files\OFFICE##\Excel.exe v:\path_to_workbook\workbook.xlsm

whereas for most other scenarios of workbook opening (directly from Excel or from Windows Explorer) the command line would look like

C:\Program Files\OFFICE##\Excel.exe

or

C:\Program Files\OFFICE##\Excel.exe /dde

i.e. would not contain the workbook name (instead, it passes the workbook via DDE)

To get to the command line, we need to implement the following API function (taken from here):

#If Win64 Then
    Private Declare PtrSafe Function GetCommandLineL Lib "kernel32" Alias "GetCommandLineA" () As LongPtr
    Private Declare PtrSafe Function lstrcpyL Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As LongPtr) As Long
    Private Declare PtrSafe Function lstrlenL Lib "kernel32" Alias "lstrlenA" (ByVal lpString As LongPtr) As Long
#Else
    Private Declare Function GetCommandLineL Lib "kernel32" Alias "GetCommandLineA" () As Long
    Private Declare Function lstrcpyL Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long
    Private Declare Function lstrlenL Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
#End If

Private Function GetCommandLine() As String
GetCommandLine = "FAILED TO RETRIEVE" ' fallback value is case of error
On Error GoTo Finally ' suppress errors
   Dim strReturn As String
   #If Win64 Then
    Dim lngPtr As LongPtr
   #Else
    Dim lngPtr As Long
   #End If
   Dim StringLength As Long
   'Get the pointer to the commandline string
   lngPtr = GetCommandLineL
   'get the length of the string (not including the terminating null character):
   StringLength = lstrlenL(lngPtr)
   'initialize our string so it has enough characters including the null character:
   strReturn = String$(StringLength + 1, 0)
   'copy the string we have a pointer to into our new string:
   lstrcpyL strReturn, lngPtr
   'now strip off the null character at the end:
   GetCommandLine = Left$(strReturn, StringLength)
Finally: On Error GoTo 0
End Function

Note the compiler directives to make the code 32/64-bit aware -- this is absolutely essential if the workbook resides on a shared drive in a network with mixed 32/64-bit Excel installation. Otherwise opening the workbook might crash the entire Excel session for random unsuspecting users.

BONUS: When debugging the automation routine I have stumbled upon some wonderful tools you can use to play a practical joke on your peers. Namely you can use Application.OnKey to override the function of a commonly used key (like, a letter or an arrow) to something totally bizarre, and wrap it into Application.OnTime to activate at some (possibly random) point in the future. Put it in your auto macro, like so:

Public num As Integer

Function sov(sekunder As Double) As Double
starting_time = Timer
Do
 DoEvents
Loop Until (Timer - starting_time) >= sekunder
End Function

Sub GetDizzy()
num = num + 1
On Error Resume Next
Select Case (num Mod 5)
Case 2 'move to opposite drection
 ActiveCell.Offset(0, 1).Select 
Case 3 'shake the workbook
 dx = Round(Rnd * 10) - 5: dy = Round(Rnd * 10) - 5
 For i = 1 To 3
  ActiveWindow.SmallScroll toright:=dx, down:=dy: sov (50 / 1000)
  ActiveWindow.SmallScroll toleft:=dx, up:=dy: sov (50 / 1000)
 Next i
Case 4 'nudge the workbook
 ActiveCell.ColumnWidth = ActiveCell.ColumnWidth + Round(Rnd * 15) - 7
Case Else 'normal operation
 ActiveCell.Offset(0, -1).Select 
End Select
If num > 200 Then MsgBox "You are tired and dizzy... Why don't you call it a day and go home?"
On Error GoTo 0
End Sub

Sub Payload()
 Application.OnKey "{LEFT}", "GetDizzy"
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("8:00:00"), "Payload"
End Sub

and watch your colleagues of choice get delighted at the emphatic computer offering them to go home at the end of the working day because they are tired and have worked long hours and it's late outside (I've borrowed the sleep function sov() from here). The code is very hard to detect because it executes totally silently and persists even after the workbook containing has long been closed; the empathy will last until Excel is closed or restarted -- but in most industry workplaces I have seen, this only happens when it crashes.

Your mileage may actually vary with the payload, but do remember not to use it for any real sabotage because this is a surefire way to get fired, if you excuse the tautology again.

Excel VBA: Automatic opening of external files and other neat tricks

Suppose you have a workflow in Excel that involves manual copy-and-paste of data from one spreadsheet (A) into another spreadsheet (B), and you want to automate it.

In some cases, you might make use of data sources and automatic linking between files, but there are many cases where this would be undesirable. Either the automatic update of links makes your process undesirably slow, or you need those intermediate values unchanged for reporting/auditing purposes, or the links update is just too buggy and unreliable (as are, alas, many automatic features in the MS Office suite), whatever the reason, sometimes you are better off with the "plain old-school" VBA macro approach, approximately,

1. Open the external spreadsheet A
2. Find the source data
3. Copy the data into your destination spreadsheet B

To do this gracefully, one must think through several scenarios though. What if the source spreadsheet is already open - should we re-open it (possibly discarding unsaved changes), or should we reuse the already opened sheet? What do we do after the data copying is done - should we force-close the source sheet or should we keep it? 

After giving these questions some thought, I have created a short VBA code snippet, and after having re-used it about a dozen times over the course of less than one month, I am putting it here for reference. Here goes:

Set targetWB = Application.ActiveWorkbook
TargetPath = targetWB.Path & "\"
SourcePath = "..\otherfolder" 'Relative to target workbook 
SourceName = "somename.xlsx" 'Can be any calculation to determine name

alreadyOpen = False
For Each thisWB In Workbooks
  If thisWB.Name = SourceName Then
   alreadyOpen = True: Set sourceWB = thisWB
  End If
Next thisWB
On Error GoTo ERRORHANDLER ' to gracefully handle "File not found" scenarios
 If Not alreadyOpen Then Set sourceWB = Workbooks.Open(Filename:=TargetPath & SourcePath & SourceName, ReadOnly:=True)
On Error GoTo 0

' *** Data copying code goes here *** 

If Not alreadyOpen Then sourceWB.Close SaveChanges:=False
Set sourceWB = Nothing: Set targetWB = Nothing ' garbage collection

Note a few touches here: 

• The code reuses the already opened spreadsheet if it is already open (pardon the tautology), and opens it otherwise. This makes the code both faster for debugging (no repeated open/close - the source workbook can be large!) and suitable for automated production workflows. The only downside here is that you need to separate the actual name of the spreadsheet, and a path to it; however if you only have the full path, you can easily work around it with a one-liner like so:

  SourceName = FullPathName: While InStr(SourceName,"\") > 0: SourceName = Right(SourceName,Len(SourceName)-InStr(SourceName,"\")): Wend

  using FullPathName instead of TargetPath & SourcePath & SourceName in Workbooks.Open(...).
• The code automatically closes the source spreadsheet only if it had to open it. This way, no spurious workbooks are left open in a production workflow.
• The files are opened read-only and closed without saving changes. This prevents unwanted queries that can otherwise pop up if the file happens to be opened by another user, or if the data copying process ends up messing up the source file (which, depending on how complicated the data processing routine is, is quite likely to happen). These queries are annoying whilst debugging and totally disruptive in a production workflow; last but not least we are preventing unwanted modification of the source file.

BONUS: Here is the routine I commonly use for the actual data copying. I find it preferable to using selection / clipboard / autofilter operations because it involves less GUI interaction and therefore is more robust (and, with Application.ScrenUpdating=True, can be faster!)

targetWS = "Analysis" : sourceWS = "Data" 'use any names 
targetStartAt = "A2": sourceStartAt = "A2" 
  'feel free to use named ranges here
  'or to have your code determine the locations based on search criteria
Set here = sourceWB.Worksheets(sourceWS).Range(sourceStartAt)
Set there = targetWB.Worksheets(targerWS).Range(targetStartAt)
While Len(here.value) > 0 
 If Not IsError(here.Value) Then
  If here.Value = "GOOD" Then ' put whatever condition to validate a source line to determine if it needs copying
   ' example of data copying, edit as your task requires
   there.Value = here.Value
   there.Offset(0,1).Value = Left(here.Offset(0,1).Value,8)
   For i = 4 to 12 
    there.Offset(0,i-2).Value = here.Offset(0,i).Value
   Next i
   Set there = there.Offset(1,0)
  End If
 End If
 Set here = here.Offset(1,0)
Wend

Memory leak tester in Excel

In many scenarios involving complicated Excel calculations, especially those relying on extensive VBA code and/or custom-made add-ins, there is a danger that memory leaks could be created by programming errors such as careless object operations.

Most often, such memory leaks are discovered post factum, when, left unchecked, they cause slowdowns or crashes when an Excel session runs out of memory. In such a case, correcting the error becomes a major pain. How to spot a memory leak offender in a calculation intensive spreadsheet containing thousands of custom functions? 

To this end, I have written a very simple program that basically evaluates a given formula multiple times and measures how the memory consumption increased. I have used this googled snippet to determine the memory consumption:

Declare Function GetCurrentProcessId Lib "kernel32" () As Long

Function GetMemUsage()
  ' Returns the current Excel.Application memory usage in KB
  Set objSWbemServices = GetObject("winmgmts:")
  GetMemUsage = objSWbemServices.Get( _
    "Win32_Process.Handle='" & _
    GetCurrentProcessId & "'").WorkingSetSize / 1024

  Set objSWbemServices = Nothing ' We don't want to cause memory leaks here :)
    ' We don't want to cause memory leaks here :)  
End Function

and wrote a very simple wrapper: 

Sub Measure_Leak()
 Set here = ActiveSheet.Range("A2")
 template = here.Value
 i1from = here.Offset(0, 1).Value: i1to = here.Offset(0, 2).Value
 i2from = here.Offset(0, 3).Value: i2to = here.Offset(0, 4).Value
 Set there = here.Offset(0, 5)
 
 there.Offset(0, 1).Value = GetMemUsage()
 
 For i1 = i1from To i1to
 For i2 = i2from To i2to
  working = "=" & template
  On Error Resume Next
   working = Replace(working, "$1", i1)
   working = Replace(working, "$2", i2)
  On Error GoTo 0
  there.Formula = working
 Next i2, i1
 
 there.Offset(0, 2).Value = GetMemUsage()
 Set here = Nothing
 Set there = Nothing
 ActiveSheet.Calculate
End Sub

Now I put this on an even simpler spreadsheet which looks like this:

Basically, pressing the Measure button evaluates the template expression substituting $1 and $2 with values spanning two ranges, a total of (i2t-i2f+1)*(i1t-i1f+1) times. An increased memory footprint at the end of the execution means there is a memory leak.

In the screenshot we see that a built-in Excel function does not cause any memory leaks (hurra!)

To test it, let us define a really leaky VBA function using this example:

' Put this into class module Class1
Option Explicit
Private A As New Class2
Private Str As String
Private Sub Class_Initialize()
   Set A.B = Me ' Fool garbage collector
   Str = Space(1024 * 10) ' Allocate lots of memory 
End Sub

'Put this into class module Class2
Option Explicit
Public B As Class1

with two functions that look similar but one is known to be leaky:

Function vbaNoLeak()
 Dim MyObject As Class2
 Set MyObject = New Class2
 Set MyObject = Nothing
End Function

Function vbaLeak()
 Dim MyObject As Class1
 Set MyObject = New Class1
 Set MyObject = Nothing
End Function

...and...

Note that using this method to troubleshoot parts of your VBA macro won't always work because many functions are prohibited inside VBA functions (they abort immediately, basically ensuring that VBA functions have no side effects). But it is very easy to modify the code above to be callable as a procedure from within a VBA macro.

The power of one-liners

In modern software engineering where most software is written by large groups of people most of whom seem to dislike reading each other's code. As a result, most organizations have some style guidelines for their code: proper indentation, naming conventions, commenting, you name it.

And most developers will have strong opinions about how code should (and even more so, shouldn't) look like. And they will defend (and, their position permitting, enforce) their opinions with near-religious fervor. Most often, they will yell at you for writing this:

for (i=0;i<n;i++) for (j=i;j<n;j++) if (a[i]>a[j]) {double t=a[i];a[i]=a[j];a[j]=t;} // bubble sort A

and instead insist on something like this:

// Bubble sort array A

for (counter_rows = 0; counter_rows < n; counter_rows++) 
  {
    for (counter_columns = counter_rows; counter_columns < n; counter_columns++) 
      {
        if (a[counter_rows] > a[counter_columns])
          { 
            double temp_variable t = a[counter_rows];
            a[counter_rows] = a[counter_columns];
            a[counter_columns] = temp_variable;
          }
      }
  }

Yes, it looks neat and tidy. But is it really all that readable?

No, not really.

It is very hard to honestly defend a standpoint that a piece of code is easier to read if have to scroll through three screens to read it, as opposed to fitting it on one screen. "But this way it is more organized", comes the objection. Very true, but how is it organized?

It is organized by instructions.

But what for? By instruction is how the compiler looks at the code, but the compiler does not give the slightest damn about your code style. Humans are much more interested in what the code does than in how the code does it (assuming that you, a fellow developer, already have some knowledge of the "how" once you know the "what").

From this standpoint it makes much more sense to organize the code by logically distinct blocks -- important steps of your algorithm that you would put on your flowchart or pseudocode (pseudocode, after all, was invented specifically for this purpose: convey the meaning of complicated code in a simpler, readable, understandable form).

And this means that simple, elegant one-liners -- once (and if) they are self-evident in what they do -- are much more preferable than expanding them on two screens. See for yourselves:

//simple operations, e.g. sumproduct or matrix multiplication
double S=0; for (int i=0;i<N;i++) S+=a[i]*b[i];

for (int i=0;i<N;i++) for (int j=0;j<N;j++) for (int k=0;k<N;k++) c[i][j]+=a[i][k]*b[k][j];

// one-liner error checking, prep or boilerplate
if (failed || !solution_good) return false;

double param; if (!genericParam.Has_Double) throw Error; param = genericParam.Get_Double();

customVector<double> vec(vec1); int vec_n=vec.Count(); double* vec_ptr=vec.Data(); 

//getter/setter methods
double someClass::getSomeProperty() {return someProperty;}

void someClass::setSomeProperty(double arg) {someProperty=arg;}

//etc...

UPDATE: Following some discussion, I feel I need to clear up a confusion here. Any code, prettified, is more readable than the same code, minified. The idea of one-liners isn't about improving the readability of the one-liner code itself! It is about the exact opposite: the one-liner code is assumed to be trivial and therefore not worth going into any great detail about, so the idea is to minify the one-liner code so that it does not get in the way of what's really important and interesting in your code. In other words, it is about improving the readability of the code surrounding your one-liners.

As for naming conventions, they definitely make sense for anything that would be (re)used in several places through the code. If something is set up in one place and is used elsewhere, by all means make the name of the variable (class, object, ...) speak for itself.

That said, still try to keep it short. Calculations in the code are formulas, and formulas read much easier with shorter variables than with long, verbose ones; that's why they introduced variables in textbook formulas in the first place, and they do write E = mgh instead of "Potential_energy = mass * specific_gravity * distance" anywhere beyond grade two at school.

For intermediate variables such as loop counters, simply don't bother. Mathematical names such as a, x, y, i, j, k will perfectly do and they will make your calculations so much easier.

There are exceptions -- sometimes, when naming is especially prone to confusion, do add some mnemonics, such as i_row and i_col rather than i and j, lest you mess up your array indices. But in most cases, formulas in the code need not look any more verbose than they do on your scrap paper.  Sometimes, it is even advisable to assign "long" mnemonic variables to short ones, do the math, and then assign the result back to the long variables.

So -- no, I'm not saying your code should look like Toledo Picochess (see below). But  do write in your IDE as you would write on the blackboard, and do write code as you would write pseudocode.

After all, making code readable is all about making it readable for a human.

P.S. Toledo Picochess looks like this: (now THAT's truly unreadable code!)

#define F (getchar()&15)
#define v main(0,0,0,0,
#define Z while(
#define P return y=~y,
#define _ ;if(
char*l="dbcefcbddabcddcba~WAB+  +BAW~              +-48HLSU?A6J57IKJT576,";B,y,
b,I[149];main(w,c,h,e,S,s){int t,o,L,E,d,O=*l,N=-1e9,p,*m=I,q,r,x=10 _*I){y=~y;
Z--O>20){o=I[p=O]_ q=o^y,q>0){q+=(q<2)*y,t=q["51#/+++"],E=q["95+3/33"];do{r=I[p
+=t[l]-64]_!w|p==w&&q>1|t+2<E|!r){d=abs(O-p)_!r&(q>1|d%x<1)|(r^y)<-1){_(r^y)<-6
)P 1e5-443*h;O[I]=0,p[I]=q<2&(89<p|30>p)?5^y:o;L=(q>1?6-q?l[p/x-1]-l[O/x-1]-q+2
:0:(p[I]-o?846:d/8))+l[r+15]*9-288+l[p%x]-h-l[O%x];L-=s>h||s==h&L>49&1<s?main(s
>h?0:p,L,h+1,e,N,s):0 _!(B-O|h|p-b|S|L<-1e4))return 0;O[I]=o,p[I]=r _ S|h&&(L>N
||!h&L==N&&1&rand())){N=L _!h&&s)B=O,b=p _ h&&c-L<S)P N;}}}t+=q<2&t+3>E&((y?O<
80:39<O)||r);}Z!r&q>2&q<6||(p=O,++t<E));}}P N+1e9?N:0;}Z I[B]=-(21>B|98<B|2>(B+
1)%x),++B<120);Z++m<9+I)30[m]=1,90[m]=~(20[m]=*l++&7),80[m]=-2;Z p=19){Z++p<O)
putchar(p%x-9?"KQRBNP .pnbrqk"[7+p[I]]:x)_ x-(B=F)){B+=O-F*x;b=F;b+=O-F*x;Z x-F
);}else v 1,3+w);v 0,1);}}

Parameter sweep in any Excel calculation

For the past few years I have dabbled a lot (and I mean a lot) in complex Excel-based quantitative finance. As a result, I would like to share a neat little trick that can be easily applied to any Excel spreadsheet.

Suppose you have a spreadsheet that does some sophisticated calculation for you (say, pricing an autocallable basket option using a Monte-Carlo simulation of a Black-Scholes model). And yes, it works; you change the input parameters and you observe the corresponding change in the output.

Now what you need is the parameter sweep analysis, i.e. you need to determine how your output changes as you vary one or more input parameters - for example, you may want to run a convergence test to see how your resulting price changes as you vary the number of Monte-Carlo simulation paths,

Of course you can do this manually, doing the simple routine "change input, run simulation, copy-paste results, repeat". But for anything more than 4-5 simulation runs his would be painfully slow and dangerously prone to error.

What's worse, if a single simulation run takes around several minutes, you'll be stuck between the need to just stare at your computer doing nothing else (and letting your productivity go to waste) or the temptation to attempt something different in the meantime (and inviting all sorts of multitasking-related errors like "aw, snap, looks like I've run this one twice, and heck, have I pasted this number to the right place?")

This is where a simple VBA macro comes to help. As simple as this:

Sub analysis1()

 Dim in1, out1, out2, out3, here As Object
 Set in1 = Worksheets("Calc").Range("Input1")
 Set out1 = Worksheets("Calc").Range("Output1")
 Set out2 = Worksheets("Calc").Range("Output2")
 Set out3 = Worksheets("Calc").Range("Output3")

 Set here = Worksheets("Report").Range("Out1D")

 While here <> Empty
  in1.Value = here.Value

  Worksheets("Calc").Calculate  ' (or do whatever you need to do to run simulation -- e.g. call some other macro)
  here.Offset(0, 1).Value = out1.Value
  here.Offset(0, 2).Value = out2.Value
  here.Offset(0, 3).Value = out3.Value
  Set here = here.Offset(1, 0)
 Wend
End Sub

Here we will need to define several named ranges: Input1 for the parameter that we will vary, Output1..3 for the calculation results we are interested in, and Out1D where the macro will look for values for input, and where, next to each input value, the result values will be output.

Why named ranges if we can specify cell addresses like.Range("F23") directly? Because it's much more robust coding style. If you decide to change the layout of your spreadsheet later, the named ranges are quite likely to point to correct locations, whereas your addresses will likely change (and your code may wreck some serious havoc on your spreadsheet). At worst (or if you decide to change parameters to vary/look at), all you will need is to re-point the named ranges to new locations rather than go through your code and edit addresses each time.

The added elegance of this example is that you don't need to specify how your sweeping parameter will vary anywhere in the code, nor do you need to count the number of runs. The program will do all of this for you, automatically running for all values you specify below the Out1D range until it hits an empty cell.
Another example will let you handle the dependence on two parameters:

Sub analysis2()

 Dim in1, in2, out1, here As Object
 Set in1 = Worksheets("Calc").Range("Input1")
 Set in2 = Worksheets("Calc").Range("Input2")
 Set out1 = Worksheets("Calc").Range("Output2D")
 Set here = Worksheets("Analysis").Range("Out2D")
 
 For i = 0 To 10
   here.Offset(-1, i + 1).Value = i * 0.01
 Next i

 While here <> Empty
  in1.Value = here.Value
  For i = 0 To 10
   in2.Value = i * 0.01
   Worksheets("Calc").Calculate '(or do whatever you need ...)
   here.Offset(0, i + 1).Value = out1.Value
  Next i
  Set here = here.Offset(1, 0)
 Wend
End Sub

As you can easily see, now we only have one output parameter (Output2D) and two input parameters (Input1, Input2). Similar to before, we look for values for parameter 1 and a place for output under the range Out2D. For the second parameter we have resorted to a simple for-loop (choosing 0, 0.1, 0.2 ... 1) but this is for simplicity and demonstration; arbitrary values for parameter 2 are possible with very little extension that I am leaving as an exercise.

A few words of warning:

• Macros are ignorant of your spreadsheet editions! If you hardcode an address, or otherwise rely on a specific layout of your workbook, you will need to re-exaamine your code every time you edit the layout. A partial workaround is using named ranges (at least you can freely move them around).
• Changes made by macros can't be undone! Before proceeding, make sure none of your important data gets overwritten, and do make a back-up copy before you test and debug your work. Luckily Excel will warn you that a file with macros cannot be saved as an .xlsx, prompting you to use .xlsm or .xlsb (or at least the old .xls) instead.
• Macros won't always run! Make sure you enable them in your Excel security settings, and make sure some pesky anti-virus program won't blindly remove them from your sheets (assuming you weren't actually up to writing malware).

Bonus: There is a very quick VBA way to save an accountant's butt.

Consider this delicate scenario. You are working on a complex spreadsheet prepared a while ago by someone else, and the spreadsheet just won't tie. Soon enough the reason is clear: the person who last worked on the sheet blindly pasted some numbers over formulas to to sweep some of their mistakes under the rug. So you are left with finding all these "cover-ups"... and needless to say, that person left the company on less-than-amicable terms, so there's no one to ask about the spreadsheet structure.

How to flag "all cells where values were pasted over formulas" in a huge spreadsheet?

Consider something like this:

' The conditions may vary. We are interested in finding "all cells that have a number and are not formulas";
' the two most obvious checks would be
' (i) Left(cell.Formula,1) = "=" (i.e. cell's formula begins with "=")
' (ii) cell.Formula <> cell.Value (i.e. its formula is different from its value)
' Neither is totally fool-proof but both work fine.

For Each cell in ActiveSheet.UsedRange
 'some more code may be needed to safely work around cells that contain errors.
 If IsNumeric(cell.Value) And Left(cell.Formula,1) = "=" Then

  ' if it is a formula, everything is OK, dim it
  cell.Font.Color = RGB(128,128,128) 
 Else
  ' if it is a number, make it stand out
  cell.Interior.Color = RGB(255,255,0) 
  cell.Font.Color = RGB(255,0,0) 
 End If
Next cell