Excel crashes after calling a Pascal DLL

Question

I have wrote a DLL in Free Pascal and want to call it in Excel. I would be grateful if someone can help me out.

My Free Pascal DLL for testing is

procedure chess(var number : single);stdcall;
begin
    WriteLn(output,'Wir testen die Zahl: ', number);
end;

exports
       chess;
begin
end.

In Excel, I have the following VBA code

Private Declare PtrSafe Sub chess Lib "D:\FS\LTMProjekt\TestWB\CLModul_lib.dll" (ByVal Number As Single)

When I call the DLL in VBA with Call chess(TSim) Excel crashes and closes itself. I cannot find out why for the last couple of days and it gets realy frustrating now. Hopefully someone can help me out.

Thanks in advance.

Edit:

Pascal Code of the DLL:

library CLM_WEAdynModell;
{$mode objfpc}{$H+}

uses
    CLM_GlobalVariables, CLM_LoadCalc, CLM_Initialization;
   
type
    IOA = array[1..150] of single; 

procedure callTModel(var IOArray : IOA); stdcall; 
begin
     getInputValues(IOArray);
     defineModel;
     calcSecForces;
     writeOutputValues(IOArray);
     writeLogFile(IOArray);
end; 

exports
       callTModel;
begin
end.

The VBA Code:

Option Explicit 
Private Declare PtrSafe Sub callTModel Lib "D:\FS\LTMProjekt\TestWB\CLM_WEAdynModell.dll" (ByRef IOArray() As Single)

Private Sub CB_RUN_Click()

Dim ValID, TStep, INLimit, aIndex, bIndex, ActSensor, VisNmax, OUTLimit, i As Integer
Dim TSim, TMax As Single
Dim IOArray(149) As Single

With ActiveSheet

TStep = 0
ValID = 0
INLimit = 9   
VisNmax = 20  

ActSensor = .Range("NoSensor").Value + 1
OUTLimit = .Range("OUTLimit")
TSim = 0
TMax = .Range("TMax")

    ThisWorkbook.Worksheets("IstRes").Range("A4:IV8000").ClearContents
 
Application.ScreenUpdating = False

 Do While TSim < TMax
    TSim = .Range("TSim")
    
    aIndex = (4 + TStep) 'Zeilenindex für Source Array
    For i = 1 To INLimit
     bIndex = (1 + i)     ' Spaltenindex für Source Array
      
     If (i < 10) Then IOArray(24 + i) = ThisWorkbook.Worksheets("SourceData").Cells(aIndex, bIndex).Value
     bIndex = bIndex + INLimit
     If (i < 10) Then IOArray(39 + i) = ThisWorkbook.Worksheets("SourceData").Cells(aIndex, bIndex).Value
     bIndex = bIndex + INLimit
     If (i < 4) Then IOArray(7 + i) = ThisWorkbook.Worksheets("SourceData").Cells(aIndex, bIndex).Value
     bIndex = bIndex + 3
     If (i < 4) Then IOArray(19 + i) = ThisWorkbook.Worksheets("SourceData").Cells(aIndex, bIndex).Value
          
    Next i
    
    IOArray(101) = OUTLimit
    
    Call callTModel(IOArray)
    
    
    aIndex = (4 + TStep) 'Zeilenindex für Target Array
    ThisWorkbook.Worksheets("IstRes").Cells(aIndex, 1).Value = ThisWorkbook.Worksheets("SourceData").Cells(aIndex, 1).Value
    For i = 1 To OUTLimit
     bIndex = (1 + i)     ' Spaltenindex für Source Array
     ThisWorkbook.Worksheets("IstRes").Cells(aIndex, bIndex).Value = IOArray(101 + i)
    Next i
 
    Application.ScreenUpdating = True
     If TStep < VisNmax Then
      .Cells(9 + TStep, 3).Value = ThisWorkbook.Worksheets("SollRes").Cells(aIndex, ActSensor).Value
      .Cells(9 + TStep, 4).Value = ThisWorkbook.Worksheets("IstRes").Cells(aIndex, ActSensor).Value
     Else
      For i = 1 To (VisNmax - 1)
       .Cells(8 + i, 3).Value = .Cells(9 + i, 3).Value
       .Cells(8 + i, 4).Value = .Cells(9 + i, 4).Value
      Next i
      .Cells(8 + VisNmax, 3).Value = ThisWorkbook.Worksheets("SollRes").Cells(aIndex, ActSensor).Value
      .Cells(8 + VisNmax, 4).Value = ThisWorkbook.Worksheets("IstRes").Cells(aIndex, ActSensor).Value
     End If
    
    TStep = TStep + 1
    .Range("TStep") = TStep
   
    Call ScrUpdateEnableNoFlicker
    
    Application.ScreenUpdating = False
    
 Loop ' Ende DoWhile TSim <= TMax


End With ' ActiveSheet
'Application.ScreenUpdating = True


End Sub

var in Pascal is ByRef in VBA. So it dies on a bad pointer value. Since ByVal makes sense here, simply omit var. — Hans Passant, Oct 28 '20 at 12:53
Thank you. I omitted var in my Pascal Code but Excel still crashes. — Idas, Oct 28 '20 at 13:17
You need to add the actual code you use in Excel to call `Chess`. — MartynA, Oct 28 '20 at 14:38
How is `output` defined in your dll code: `WriteLn(output,'Wir testen die Zahl: ', number);`? If I replace that line with a simple `beep;` just to get some indication, it works ok. — Tom Brunberg, Oct 28 '20 at 22:36
It works with `beep;` for me as well. `output` is defined as `var output : TEXT;` Is it possible that the textdocument causes the problem? — Idas, Oct 29 '20 at 09:42
Ok, we are getting closer. How do you instantiate `output`? What other code do you have related to `output`? — Tom Brunberg, Oct 29 '20 at 19:27
output is the Pascal name for stdout. writeln(output,...) is the same as omitting output iow writeln(...) — Marco van de Voort, Oct 29 '20 at 21:19
Not a good idea to write on stdout in a DLL opened by Excel, which is not supposed to have a standard output: where do you expect it will be written?. I just tried with Delphi, and it doesn't crash, but I get a runtime error. If you really want to write something, write in a file. If it's just for debugging purposes, a msgbox might do as well. — , Oct 30 '20 at 08:35
Thank you all so far. `chess` is only for testing. The Procedure i really want to get working is the following ``` procedure callTModel(var IOArray : IOA);stdcall; begin getInputValues(IOArray); defineModel; calcSecForces; writeOutputValues(IOArray); writeLogFile(IOArray); end; ``` where `IOA`is defined as ``` type IOA = array[1..150] of single; ``` I figured out that a variable without the var (so it gets called by value) and called ByVal in Excel works. But when I try calling anything ByRef (like in the procedure callTModel) Excel crashes. — Idas, Oct 30 '20 at 10:20
Post the code then. Both the Pascal code, and the VBA, especially the declaration of the DLL function. Note that to pass an array, you should declare in VBA a ByRef scalar, and pass the first element of the array. You also have to take care of array bounds (note that the first element of the array A in VBA is either A(1) or A(0), depending on Option Base) — , Oct 30 '20 at 12:35
Thank you Jean-Claude. Could you explain to me how I pass the whole array by passing only one scalar (the first element of my array). — Idas, Oct 30 '20 at 13:16
When you pass the first element by reference, you are actually passing the **address** of the first element. It's enough to find the subsequent elements. And it's how you pass a VBA array to a DLL, unless you want to deal with VBA's internal data structures. — , Oct 30 '20 at 14:39

score 1 · Answer 1 · 2020-10-30T15:31:40.167

Here is an example of array argument passing between VBA and Delphi. The Delphi functions compute the sum of the array elements. In order to use arrays of arbitrary length, the argument is declared as a pointer (first function) with pointer math on, or an open array (second one).

The VBA call will have in both cases two arguments. SumOfArrayElements1 needs a pointer to the array first element, and the number of elements. The single open array argument of SumOfArrayElements2 needs two arguments as well in VBA: the same pointer to the first element, and the last index. Given that the first index is always 0 in Delphi for an open array, the last index is the array length minus one.

Delphi:

library calc;

{$POINTERMATH ON}

type preal = ^real;

function SumOfArrayElements1(a: preal; n: integer): real;
var i: integer;
begin
    Result := 0;
    for i := 0 to n - 1 do
        Result := Result + a[i]
end;

function SumOfArrayElements2(a: array of real): real;
var i: integer;
begin
    Result := 0;
    for i := Low(a) to High(a) do
        Result := Result + a[i]
end;

exports
    SumOfArrayElements1, SumOfArrayElements2;
end.

VBA

Option Explicit
Option Base 1
Declare PtrSafe Function SumOfArrayElements1 Lib "calc" (ByRef X As Double, ByVal N As Long) As Double
Declare PtrSafe Function SumOfArrayElements2 Lib "calc" (ByRef X As Double, ByVal N As Long) As Double
Sub Test()
    Dim A() As Double, I As Long, N As Long
    N = 100
    ReDim A(N)
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    For I = 1 To N
        A(I) = I
    Next I
    Debug.Print SumOfArrayElements1(A(1), N)
    Debug.Print SumOfArrayElements2(A(1), N - 1)
End Sub

Note: the ChDrive/ChDir in VBA is necessary for VBA to find the DLL, when it's stored in the same directory as the Excel file.

Excel crashes after calling a Pascal DLL

1 Answers1