Velixo NX - Excel Mac - force Velixo function to calculate within Macro

Posted 25 days ago by Ben Perry

B
Ben Perry
Answered

0 Votes

Post a topic

I can't work out if i'm missing something, but i need to get a Velixo function to calculate within a macro.


Essentially I'm inserting an SI.QUERY into a cell, then I want to copy the range and paste as values so I can then insert subtotals.

SI.QUERY doesn't execute until after my macro has ended despite trying application.calculate.


Any thoughts?

0 Votes

Gabriel Michaud

Gabriel Michaud posted 14 days ago Best Answer

Hi Ben,

The main problem is that Office add-ins function run asynchronously; with modern add-ins there is no way to force these functions to run synchronously (that is, to cause the execution of your VBA code to pause until it has recalculated). I found a solution which might be helpful to you, please take a look at this snippet as an example:

Public Sub ChangeValueAndWaitForRefreshViaOnTimeCallback()
    'Change a value, which will cause Excel to recalculate
    'NOTE: I am using named ranges for clarity.
    Sheet1.Range("Location").Value = "100"
    
    Application.OnTime Now + TimeValue("00:00:02"), "ContinueAfterRefresh"
End Sub

Private Sub ContinueAfterRefresh()
    ' This code will execute after the scheduled delay,
    ' allowing time for asynchronous functions to refresh.
    If IsError(Sheet1.Range("Total").Value) Then
        If Sheet1.Range("Total").Value = CVErr(2051) Then
            Debug.Print "Still busy calculating, wait a bit longer..."
            Application.OnTime Now + TimeValue("00:00:02"), "ContinueAfterRefresh"
        Else
            MsgBox "Unkown error"
        End If
        
    Else
        'TODO: Run your code here:
        MsgBox "Calculation finished: " & FormatCurrency(Sheet1.Range("Total").Value)
    End If
End Sub


The solution is to use Application.OnTime to launch a timer, which runs every second, and checks if the cell is still showing #BUSY, #CALC or any other value indicating it is still calculating. If so, it waits another second. Let me know if that makes sense if this meets your requirement.

We are exploring other ways to integrate VBA with Velixo NX and make it more seamless, but in the meantime I hope this will be helpful!

0 Votes

6 Comments

Sorted by
Gabriel Michaud

Gabriel Michaud posted about 3 hours ago

(If that's the case, I may have other ideas that don't require using macros)

0 Votes

Gabriel Michaud

Gabriel Michaud posted about 3 hours ago

Hi Ben,

Thanks for providing an update. I am glad to hear the proposed solution worked!

Out of curiosity, is your goal to group by the returned data by account and add subtotals?

Gabriel

0 Votes

B

Ben Perry posted 12 days ago

Just reporting back to say it works as expected. I tweaked your code a bit to avoid nesting within the else statement.

My code below (stripped back to make it more legible):

Sub InsertFormula()
    ActiveSheet.Range("A1").Formula2 = "=SI.QUERY(Connection,""GLENTRY"")"
    
    ' Trigger AddSubtotals function to run after a delay
    Application.OnTime Now + TimeValue("00:00:03"), "AddSubtotals"
End Sub

Sub AddSubtotals()
    ' Check if Velixo function is still processing
    If IsError(ActiveSheet.Range("A1").Value) Then
        Application.OnTime Now + TimeValue("00:00:02"), "AddSubtotals"
        Exit Sub
    End If

    ' Copy and Paste as Values
    ActiveSheet.Range("A1").SpillingToRange.Copy
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

    ' Insert Subtotals
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub


0 Votes

B

Ben Perry posted 13 days ago

Hi Gents,

Sorry for the delayed reply. Appreciate your responses.
@Gabriel - that looks like it'll do the trick!

I'll give it a go and report back.

Thanks :)

0 Votes

Gabriel Michaud

Gabriel Michaud posted 14 days ago Answer

Hi Ben,

The main problem is that Office add-ins function run asynchronously; with modern add-ins there is no way to force these functions to run synchronously (that is, to cause the execution of your VBA code to pause until it has recalculated). I found a solution which might be helpful to you, please take a look at this snippet as an example:

Public Sub ChangeValueAndWaitForRefreshViaOnTimeCallback()
    'Change a value, which will cause Excel to recalculate
    'NOTE: I am using named ranges for clarity.
    Sheet1.Range("Location").Value = "100"
    
    Application.OnTime Now + TimeValue("00:00:02"), "ContinueAfterRefresh"
End Sub

Private Sub ContinueAfterRefresh()
    ' This code will execute after the scheduled delay,
    ' allowing time for asynchronous functions to refresh.
    If IsError(Sheet1.Range("Total").Value) Then
        If Sheet1.Range("Total").Value = CVErr(2051) Then
            Debug.Print "Still busy calculating, wait a bit longer..."
            Application.OnTime Now + TimeValue("00:00:02"), "ContinueAfterRefresh"
        Else
            MsgBox "Unkown error"
        End If
        
    Else
        'TODO: Run your code here:
        MsgBox "Calculation finished: " & FormatCurrency(Sheet1.Range("Total").Value)
    End If
End Sub


The solution is to use Application.OnTime to launch a timer, which runs every second, and checks if the cell is still showing #BUSY, #CALC or any other value indicating it is still calculating. If so, it waits another second. Let me know if that makes sense if this meets your requirement.

We are exploring other ways to integrate VBA with Velixo NX and make it more seamless, but in the meantime I hope this will be helpful!

0 Votes

H

Harry Lewis posted 22 days ago

Ben -

Currently Velixo NX is not able to integrate with VBA.  We will discuss options with our Development team and get back to you.  That process might be easier if you create a support ticket by contacting [email protected].

0 Votes

Login or Sign up to post a comment

Preview
0 of 0