Creating Custom Excel GP Functions Using VBA

We do a lot of monthly account reconciliations at work in Excel. This is a very repetitive process every month so any bit of effort I can spend now to reduce time spent in the future is a win in my book. To this end, I created a couple functions accessible through Excel’s function bar which pull information such as account balances for a period, account descriptions and multi-currency balances. I saved all of the VBA code driving these functions in a .xlam and “installed” this Add-in on all users’ computers in a uniform directory (C:\GPFunctions). Alternatively you can just save it in your Personal workbook that launches on Startup. The prerequisite for this to work is that all users’ Windows login must be enabled to have read access to the GP SQL Server (Trusted Connection).

So what? Well this effectively allows us to pull live account balances into a single cell in any Excel worksheet without being logged into GP. I would not recommend doing this for more than 100 accounts per worksheet as it effectively performs one SQL query per function (100 queries) and be slow to refresh. But for multi-company dashboards (such as inter-company balance checks) or simple balance sheets, pulling account balances is quick and painless. While this may not apply to all companies, we are license starved at my company so everyone has to fight to log into GP. This effectively bypasses the need to log into GP to pull a simple balance as this queries the SQL Server directly.

For now I simply pasted the VBA code for GPAccBal below. I plan to release my full .xlam, but there are other features in the .xlam I wish to write about before releasing it so sit tight.



GPAccBal Example

GPAccBal Sample

GPAccBal Result

GPAccBal VBA Code:

Public Const gpServer As String = “” ‘Put your SQL Server name here

Function GPAccBal(company, account, period, fiscal_year, Optional server)
‘Do not forget to import Active X library

‘References     : Microsoft ActiveX Data Objects 6.1 Library (or newest version)
‘Written By     : James Lyn
‘Date Written   : December 2014
‘Purpose        : To return period balance for one account from the GP database

‘   company     : designates the database to query against
‘   account     : designates the account number to query
‘   period      : 1-12                  – designates the period for which to return the balance as per the fiscal calander
‘   year        : i.e. 2014             – deisgnates the fiscal year to query
‘   server      : optional, default gpServer. If the server name changes, input changed name into parameters

‘Output         : Period balance for a single account for the selected company and period in the functioning currency

‘Store current application settings
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents

‘Disable unnecessary functionality
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim query As String
Dim serverVar As String

If IsMissing(server) Then
serverVar = gpServer
serverVar = server
End If

query = “select  sum(PERDBLNC) ” & _
” from   (select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10110″ & _
” Union all” & _
” select PERDBLNC, YEAR1, PERIODID, ACTINDX from GL10111 ) GL” & _
” where” & _
” actindx in (select distinct actindx from GL00105 where actnumst = ‘” & account & “‘)” & _
” and year1 = ” & fiscal_year & _
” and periodid <= ” & period & _
” group by actindx”

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

On Error GoTo ConnectionError:

‘Open trusted connection with server
cn.Open “DRIVER={SQL Server Native Client 10.0};SERVER=” & serverVar & “;trusted_connection=yes;Database=” & company

‘Query server
rs.Open query, cn

‘Copy query results
If (rs.EOF = False) Or (rs.BOF = False) Then
GPAccBal = rs(0)
GPAccBal = “”
End If

‘Close connections

‘Restore application settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
Exit Function

‘MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical
‘MsgBox “Please check your parameters.”
If rs.State = 1 Then
Set rs = Nothing
End If

Set cn = Nothing

‘Restore application settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
Exit Function

End Function



Disclaimer: I am very much an amateur programmer. I have never taken an official Microsoft course. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set. Users accept all the risk of using anything I provide and are encouraged to thoroughly test in a dedicated test environment before moving to production. Files I provide are free for use and are not to be resold. By using my files you agree to not hold me liable for any damages caused by said files.