Interface Check (VBA) ·

Abstract

When two systems exchange data via an interface, the data transmission should be checked:

Ensure that the data is transferred in the right form
Log how long the transmission takes

The following template of a subroutine is checking this on the calling / receiving side. For a database call the column header are checked against given values. This protects against unforeseen column changes of the database output. In addition to this the runtime of the database call is logged. The runtime accuracy is just in seconds but now you can easily detect runtime differences over time.

This template is meant to serve as basic start for an interface check via VBA.

Appendix – Call_SQLQuery Code

This template subroutine requires (calls) the logging modules modLog and clsLog, and the module (external link!) LibFileTools.

Please read my Disclaimer.

Option Explicit

Public q                                As clsQueries

Sub Call_SQLQuery(sDatabase As String, sSQL As String, _
  ws As Worksheet, sDestination As String, vSQLHeader As Variant, _
  Optional var1 As String, Optional val1 As Variant, _
  Optional var2 As String, Optional val2 As Variant, _
  Optional var3 As String, Optional val3 As Variant, _
  Optional var4 As String, Optional val4 As Variant, _
  Optional var5 As String, Optional val5 As Variant, _
  Optional var6 As String, Optional val6 As Variant, _
  Optional var7 As String, Optional val7 As Variant, _
  Optional var8 As String, Optional val8 As Variant, _
  Optional var9 As String, Optional val9 As Variant, _
  Optional var10 As String, Optional val10 As Variant, _
  Optional bNoHeaders As Boolean = True)
 
'Source (EN): https://www.sulprobil.de/interface_check_en/
'Source (DE): https://www.berndplumhoff.de/schnittstellen_prüfung_de/
'(C) (P) by Bernd Plumhoff 05-Sep-2016 PB V0.1
 
Dim dtStamp                           As Date
Dim i                                 As Long
Dim lHeaderColStart                   As Long
Dim lHeaderRowStart                   As Long
Dim sPath                             As String
Dim v                                 As Variant
Dim Logger                            As clsLog
 
Set Logger = New clsLog
Logger.Name = "Call_clsQueries"
Logger.LogLevel = g_log_params.log_level
sPath = GetLocalPath(ThisWorkbook.path)
If sPath = "" Then sPath = ThisWorkbook.path
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
Application.StatusBar = "Running " & sSQL & ", storing data in sheet " & _
  ws.Name & ", cell " & sDestination
lHeaderRowStart = ws.Range(sDestination).Row
lHeaderColStart = ws.Range(sDestination).Column
If UBound(vSQLHeader) = 0 Then
  ws.Range(ws.Cells(lHeaderRowStart, lHeaderColStart), _
    ws.Cells(1048576, lHeaderColStart)).ClearContents
Else
  ws.Range(ws.Cells(lHeaderRowStart, lHeaderColStart), _
    ws.Cells(1048576, lHeaderColStart + UBound(vSQLHeader) _
    - LBound(vSQLHeader) - 1)).ClearContents
End If
dtStamp = Now
Set q = New SQLQuery
Call q.QueryExport(ws.Range(sDestination), _
  sDatabase & ";" & sPath & "SQL\" & sSQL, _
  var1, val1, var2, val2, var3, val3, var4, val4, var5, val5, _
  var6, val6, var7, val7, var8, val8, var9, val9, var10, val10, _
  NoHeaders:=bNoHeaders, CloseRst:=True)
If ws.Range(sDestination) = "" Then
  Logger.fatal sSQL & " failed"
  Exit Sub
Else
  dtStamp = Now - dtStamp
  Logger.info sSQL & " ran " & Format(dtStamp, "n:ss") & " [m:ss]"
End If
If Not bNoHeaders Then
	i = 1
	For Each v In vSQLHeader
		If ws.Cells(lHeaderRowStart, lHeaderColStart + i - 1) <> v Then
		Logger.fatal "Sheet " & ws.Name & ": '" & v & "' expected in column " & _
			i & " of row " & lHeaderRowStart & ". Abort"
		'Now get main dialogue window into foreground again
		With ThisWorkbook.Windows(1)
			.Activate
			If .WindowState = xlMinimized Then .WindowState = xlNormal ' or xlMaximized
		End With
		AppActivate Application.Caption
		Call MsgBox("Sheet " & ws.Name & ": '" & v & "' expected in column " & i _
			& " of row " & lHeaderRowStart & ". Abort", vbOKOnly, "Error")
		End
		End If
		i = i + 1
	Next v
End If
ws.Columns.EntireColumn.AutoFit
 
End Sub

You can call the subroutine with:

Call Call_SQLQuery("MyDatabase", "Example.sql", _
  Worksheets(2), "A1", _
	Array("Column Header 1", "Column Header 2", "Column Header 3", ""), _
  "Parameter_1", "String value for parameter 1", _
  "Parameter_2", dDoubleVariable_for_parameter_2)

This call expects a database output consisting of three columns. The subroutine deletes (clears) the correct count of column data starting from the upper left output corner ws.Range(sDestination) down to the bottom of the worksheet. The end of the column headers it checks with the empty string “”.

Note: A comprehensive documentation of my Excel implementations can be found in Excel VBA A Collection.