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 posted
16 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!
I'm extracting transactions between a date range and that have PROJECTID = X.
Then I want to group by both ITEMID and EMPLOYEEID dimensions and subtotal the AMOUNT.
Unfortunately, Excel's subtotal function doesn't allow grouping by multiple columns so I add a column to insert & fill-down a formula that concatenates the ITEMID and EMPLOYEEID into a single value that I can use to group by.
If Velixo had a CONCAT aggregate function that might be useful.
Curious to hear what you're thinking.
If you want to see the full macro I can probably share it.
Cheers Ben
0 Votes
Gabriel Michaudposted
2 days ago
(If that's the case, I may have other ideas that don't require using macros)
0 Votes
Gabriel Michaudposted
2 days 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 Perryposted
14 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 Perryposted
15 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 Michaudposted
16 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 Lewisposted
24 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
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 posted 16 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 SubThe 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
8 Comments
Gabriel Michaud posted 1 day ago
Ben,
Check out this video: https://www.loom.com/edit/febdfcac00744656a7acc3ea44dfb3de
The file I used is attached to my message as well.
Attachments (1)
SI-GL-RT12 -....xlsx
167 KB
0 Votes
Ben Perry posted 2 days ago
Hi Gabriel,
Yes - almost.
I'm extracting transactions between a date range and that have PROJECTID = X.
Then I want to group by both ITEMID and EMPLOYEEID dimensions and subtotal the AMOUNT.
Unfortunately, Excel's subtotal function doesn't allow grouping by multiple columns so I add a column to insert & fill-down a formula that concatenates the ITEMID and EMPLOYEEID into a single value that I can use to group by.
If Velixo had a CONCAT aggregate function that might be useful.
Curious to hear what you're thinking.
If you want to see the full macro I can probably share it.
Cheers
Ben
0 Votes
Gabriel Michaud posted 2 days ago
(If that's the case, I may have other ideas that don't require using macros)
0 Votes
Gabriel Michaud posted 2 days 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
Ben Perry posted 14 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 Sub0 Votes
Ben Perry posted 15 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 posted 16 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 SubThe 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
Harry Lewis posted 24 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