Timing Problem - DLL with EXCEL?

cseeton · ‎12-30-2008

I had figured out the static versus dll libraries to get my dll to work on my computer and my laptop with Exceland just ran into another problem.

I have a piece of code that takes a small bit of time to iterate on (couple of seconds). I have generated the dll and it ran fine with Excel 2007 and it seemed to work well, but now the excel VBA just skips over calls that take any time to execute. If I go into debug mode for the VBA and pause after the call the code completes and the results are passed into Excel. Is there any way to force VBA to wait for the results or is there a complier option that I needto consider.

Many thanks.

Chris

------

Sega continues...

I saved the file on the laptop as a Macro Enable Workbook and the dll stops working, saving back (on the laptop) as a 97-2003 causes everything to wait as it should and the code completes and returns. Resaving as a MacroEnabled 2007, but not closing Excel, everything works. Closing Excel and reopening the 2007 file, the dll does not work. Is there something specific the way dlls load and are excuted in Excel 2007 MacroEnabled format that anyone knows about (saving as a default excel 2007 format strips the VBA project)???

----

Still having trouble here is some quick code I put toghether...

[cpp]      subroutine TESTXLdll (x,num,FT,ierr,herr)
      implicit double precision (a-h,o-z)
      implicit integer (i-k,m,n)
      character herr*255
      
!DEC$ ATTRIBUTES DLLEXPORT, Alias: "_TESTXLdll"::TESTXLdll
!DEC$ ATTRIBUTES STDCALL, REFERENCE::TESTXLdll

      !add loops to add time to calculations
      FT=0.0
      ierr=0
      
      if (x.gt.0.d0) then
        do i=1,num
            do j=1,num
                FT = FT + Dlog(x)/DLog(10.d0)
            enddo
        enddo
      else
        ierr = 1
      endif
      if (ierr.eq.0) then
        herr = "ok"
      else
        herr = "error, x<0"
      endif
      end[/cpp]

theExcel part:

Option Explicit

Private Declare Sub TESTXLdll Lib "XLTest.dll" (x As Double, num As Long, FT As Double, ierr As Long, ByVal herr As String, ln As Long)

Private herr As String * 255
Private ierr As Long
Private num As Long, x As Double

Function Test(x, num)
Dim FT As Double
If x < 0 Or num < 0 Then Test = "Input(s) is LT zero": Exit Function
Call TESTXLdll(x, num, FT, ierr, herr, 255&)
Test = FT
If ierr <> 0 Then Test = "Error, Error, Error"
End Function

gib · ‎01-01-2009

Quoting - cseeton

I had figured out the static versus dll libraries to get my dll to work on my computer and my laptop with Exceland just ran into another problem.

I have a piece of code that takes a small bit of time to iterate on (couple of seconds). I have generated the dll and it ran fine with Excel 2007 and it seemed to work well, but now the excel VBA just skips over calls that take any time to execute. If I go into debug mode for the VBA and pause after the call the code completes and the results are passed into Excel. Is there any way to force VBA to wait for the results or is there a complier option that I needto consider.

Many thanks.

Chris

I think this is a VBA problem, nothing to do with Fortran (it will happen with any DLL). After a quick search I concluded that the solution is not obvious. I suggest that you ask the question on an Excel/VBA forum, e.g. microsoft.public.excel.programming

anthonyrichards · ‎01-02-2009

What do you mean by "skips over calls that take any time to execute". Do you mean that when you change an argument that the contents of a cell which calls Test in a formula fails to update? Why not add MsgBox "Test entered" and MsgBox "Return from DLL routine" statements to see what exactly is happening? I do not know what macro enabled Excel is, but why not add the code you posted to a module attached to the Excel workbook. That should work every time, so long as the DLL is in the workbook's folder or in a folder on the default search path ( c:windowssystem32 is usually a good place to put the DLL).

cseeton · ‎01-02-2009

Quoting - anthonyrichards

What do you mean by "skips over calls that take any time to execute". Do you mean that when you change an argument that the contents of a cell which calls Test in a formula fails to update? Why not add MsgBox "Test entered" and MsgBox "Return from DLL routine" statements to see what exactly is happening? I do not know what macro enabled Excel is, but why not add the code you posted to a module attached to the Excel workbook. That should work every time, so long as the DLL is in the workbook's folder or in a folder on the default search path ( c:windowssystem32 is usually a good place to put the DLL).

The Call TestXLdll is in a VBA module and the dll issaved in the same folder as the excel workbook.

The problem is that with Excel 2007 (I didn't check with 2003, but I don't remember this problem in the past), I have subroutines that make a quick, simple calculation and return values back to the Excel module and everything works as it should; however with the bigger (longer) calculations the VBA code reads the dll call and doesn't seem to wait for a responce before proceeding to the next line. If I am stepping through the code manually or put in a break, I can pause after a long call to wait for the dll to return values (but I have many calls in my module and can't afford to do it manually each time).

With the lack of responces, it seems like I am doing something wrong, but I can't figure out if there isa complieroption (I am using the defaults, plus the STDCALL,REFERENCE Attributes) thatis returning focus to the VBA and then following with values when the iterations are complete, or why is VBA not pausing to recieve the values after calling the dll?

Excel 2007 nowallows a user to save the file in three types: MacroEnabled, default (macros stripped, probablyforce the use of addins and call through those?) and binary (smaller file sizes).

Thanks.

Chris

gib · ‎01-02-2009

Quoting - cseeton

The Call TestXLdll is in a VBA module and the dll issaved in the same folder as the excel workbook.

The problem is that with Excel 2007 (I didn't check with 2003, but I don't remember this problem in the past), I have subroutines that make a quick, simple calculation and return values back to the Excel module and everything works as it should; however with the bigger (longer) calculations the VBA code reads the dll call and doesn't seem to wait for a responce before proceeding to the next line. If I am stepping through the code manually or put in a break, I can pause after a long call to wait for the dll to return values (but I have many calls in my module and can't afford to do it manually each time).

With the lack of responces, it seems like I am doing something wrong, but I can't figure out if there isa complieroption (I am using the defaults, plus the STDCALL,REFERENCE Attributes) thatis returning focus to the VBA and then following with values when the iterations are complete, or why is VBA not pausing to recieve the values after calling the dll?

Excel 2007 nowallows a user to save the file in three types: MacroEnabled, default (macros stripped, probablyforce the use of addins and call through those?) and binary (smaller file sizes).

Thanks.

Chris

Have you confirmed that if you force this DLL routine to return immediately, with some artificial results, it does work correctly? Apparently the normal VBA behaviour is to wait for an external function to return before proceeding.

gib · ‎01-02-2009

According to a newsgroup post, this situation should be handled by DoEvents:

y = myfunction(x)

DoEvents

Range("A1").Value = y

Try that.