Velixo NX and VBA interoperability

Created by Velixo Team, Modified on Fri, 16 May at 3:55 AM by Velixo Team

Applies to:

Velixo NX
Every ERP

TABLE OF CONTENTS

Overview
Running VBA code after Velixo NX calculations have finished

Overview

In modern Office add-ins like Velixo NX, Microsoft does not support exposing a VBA library like Velixo Classic does.

Therefore, as of today, migrating to Velixo NX requires adjusting or removing your VBA code.

There are two primary use cases we’ve seen with VBA:

Executing a VBA macro when Velixo has finished calculations
Imperatively invoking a Velixo command like Refresh, Writeback, or Drilldown, then continuing the macro after the command has finished

Currently, it is possible to achieve the first goal by adapting your VBA code written for Velixo Classic, as shown below.

The second goal is not yet solvable, and our R&D team intends to address this gap in 2025.

Running VBA code after Velixo NX calculations have finished

Insight

Whenever a custom function (e.g., GI() or ACCOUNTTURNOVER()) from a modern JS add-in is recalculating, Excel returns a special value for its cell, called #BUSY. Visually, it shows as a green refresh indicator inside the cell. Internally, it is a type of cell error with error code 2051.

Solution idea

Identify a set of cells that you’d like to track for recalculation, and repeatedly check whether any of them are #BUSY, using VBA timers. Once all cells are not #BUSY, execute your post-calculation VBA code.

Limitations

It may be very expensive to check every cell on every worksheet for #BUSY, especially on a large workbook. The snippet and the workbook provided below require the VBA programmer to give a specific list of Velixo formula-containing ranges, which they would like to “watch” for recalculation.

VBA snippets

Attention: The first snippet below will not work properly if scoped to ThisWorkbook or one of the sheets. You need to create a new VBA module for it:

Option Explicit

Const OneSecond As Double = 1 / 86400
Dim NextCheck As Date

' IMPORTANT: Change this to the actual range that you need to watch for calculation finish in your workbook

Public Function GetTargetRange() As Range
    With ThisWorkbook.Sheets("Sheet1")
        Set GetTargetRange = Application.Union( _
            .Range("A1"), _
            .Range("A2"), _
            .Range("A3") _
        )
    End With
End Function

Sub CodeToRunUponCompletion()
    ' Adapt this function to run your custom VBA logic.

    MsgBox "Calculation has finished! Run your code here"
End Sub

Function IsCellBusy(ByVal cell As Range) As Boolean
    Dim cellValue As Variant
    cellValue = cell.Value

    IsCellBusy = False

    If IsError(cellValue) Then
        ' 2051 is the internal CVErr code for #BUSY
        If cellValue = CVErr(2051) Then
            IsCellBusy = True
            Exit Function
        End If
    End If
End Function

Sub WaitForRelevantCellsToCalculate()
    Dim targetRange As Range, cell As Range
    Set targetRange = GetTargetRange()

    For Each cell In targetRange
        If IsCellBusy(cell) Then
            NextCheck = Now + OneSecond
            Application.OnTime NextCheck, "WaitForRelevantCellsToCalculate"
            Exit Sub
        End If
    Next cell

    CodeToRunUponCompletion
End Sub

Sub StopChecking()
    On Error Resume Next
    Application.OnTime EarliestTime:=NextCheck, _
                       Procedure:="WaitForRelevantCellsToCalculate", _
                       Schedule:=False
End Sub

Warning! Conversely, the code below should be in ThisWorkbook, not a module.

In ThisWorkbook, add the following code that would start our calculation watcher and timer whenever a worksheet calculates or changes in the workbook.

If you know in advance that all your cells of interest (GetTargetRange()) reside on the same worksheet, you may use Worksheet_Calculate and Worksheet_Change for better performance. In this case, you need to place the below code in the worksheet’s own VBA module (e.g., Sheet1).

Private Sub StartWatcherIfNeeded()
    Dim targetRange As Range
    Set targetRange = GetTargetRange()

    Dim cell As Range

    ' If any of our target cells are #BUSY, start the calculation finished listener.

    For Each cell In targetRange
        If IsCellBusy(cell) Then
            StopChecking
            WaitForRelevantCellsToCalculate
            Exit For
        End If
    Next cell
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    StartWatcherIfNeeded
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
    Dim monitoredRange As Range
    Set monitoredRange = GetTargetRange()

    If Not Intersect(monitoredRange, target) Is Nothing Then
        StartWatcherIfNeeded
    End If
End Sub

Sample workbook

The sample workbook below has a single GI() formula. It tracks the A1:A3 range for recalculation and shows a VBA message box upon calculation finish.