Datenaktualisierung mit Excel Makro

unknown

Hallo zusammen,

in Infor 11 konnte ein Refresh (F9) der Daten mit dem Excel VBA Befehl Application.CalculateFullRebuild
durchgeführt werden.
In Infor 12 geht das nicht mehr, gibt es hierfür eine Alternative?

Code Beispiel:

 

Sub refresh_All()

Dim i As Integer

Dim anzahl As Integer

anzahl = Worksheets("Blattschalter").Cells(8, 12).Value

For i = 1 To anzahl

    Start = Timer()

    Application.CalculateFullRebuild   

   

    Worksheets("Blattschalter").Cells(16, 12).Value = i

    proRunde = Timer() - Start

    Worksheets("Blattschalter").Cells(19, 12).Value = (anzahl - i) * proRunde

    Worksheets("Blattschalter").Cells(22, 12).Value = proRunde   

   

Next i

MsgBox "Fertig"

End Sub

Danke und Viele Grüße

Bernd Krichbaum

Vit Holy

Hello Bernd,

the 'Application.CalculateFullRebuild' should be used but while building VBA you need to be careful how it is used together with Custom Functions (javascript based alternative to User Defined Functions). Custom functions are async out of process functions, i.e. when you call VBA function CalculateFullRebuild the custom functions calculation is initialized however the code execution continues as if it was finished (which is not true). 

Basically, you need to rework the flow of the subroutine to "emulate" synchronous code execution with OnTime function or with proper event handling (i.e. split it into multiple methods reacting on various events like Application.AfterCalculate, etc.)

In general VBA compatibility with Javascript based add-ins is not ideal, unfortunately. See e.g. github.com/.../907

Kind regards,

V.