phantom Excel process with remote VB6 dll

[lalbatros]

Hello,

I remoted most of my number crunching stuff to a dll.
It work fine except for a few strange things that I observed like from time to time a strange message "Error in loading DLL" even though everything works fine. This message appears only when entering the main formula in the formula, and is no problem at all in usual work.

When I investigated, I noticed in the "windows task manager" that one instance of excel remains open even after I have closed excel. This phantom excel process is maybe related to the other problem I explained above.

How does my application work? Very easy!
Instead of calling a VBA function, it creates a VB6 object from this dll and uses the methods of this object. In this way I have remoted the number crunching functionality.
The main parameter passe in this process is a reference to the original workbook. I need it because the number crunching functions need to read data from the workbook.

I made it sure to terminate the life of the object properly on both sides by ad hoc obj=Nothing statements.
Still I think that the "garbage collection" or the "terminating" is the cause of the problem.

Any idea?
I would appreciate your suggestions quite a lot since I want to make everything clean and safe for my end users.

thanks

 

[Norie]

Does ActiveWorkbook exist as an object in VB?

As far as I know it doesn't, it's an object connected to the Excel application.

Now I don't know if that's your actual problem but that's hard to tell with out seeing some more code.

You've posted some I realise but there's only 1 line of code that appears to be connected with Excel.

Perhaps you should have a look at Tushar Meta's site for the topic http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit

 

[lalbatros]

I found this web page is probably related to my problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;319832

but I didn't come with a solution.
Of course I am careful about not making a lot of changes without getting a solution.

Do you understand clearly what I should do?

 

[Legacy 98055]

You are using late binding. Does that support page apply to you?

Well... Since you can't provide the code, all I can suggest is for you to step through your dll line by line to determine the offending code. You do realize that you can debug your dll within visual studio in real time with Excel as the active client? Correct?

If you have many lines of code, add in this line of code before you return your functions assignments...

If ExcelTimesTwo Then MsgBox "Found multiple instances..."

This will at least narrow it down to the function that is causing you grief.

Place this is a module:

Option Explicit
 
Private Declare Function Process32First Lib "kernel32" (ByVal hSnapshot As Long, lppe As PROCESSENTRY32) As Long
Private Declare Function Process32Next Lib "kernel32" (ByVal hSnapshot As Long, lppe As PROCESSENTRY32) As Long
Private Declare Function CloseHandle Lib "Kernel32.dll" (ByVal Handle As Long) As Long
Private Declare Function OpenProcess Lib "Kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long
Private Declare Function EnumProcesses Lib "psapi.dll" (ByRef lpidProcess As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long
Private Declare Function GetModuleFileNameExA Lib "psapi.dll" (ByVal hProcess As Long, ByVal hModule As Long, ByVal ModuleName As String, ByVal nSize As Long) As Long
Private Declare Function EnumProcessModules Lib "psapi.dll" (ByVal hProcess As Long, ByRef lphModule As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long
Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal dwFlags As Long, ByVal th32ProcessID As Long) As Long
Private Declare Function GetVersionExA Lib "kernel32" (lpVersionInformation As OSVERSIONINFO) As Integer
 
Private Type PROCESSENTRY32
   dwSize As Long
   cntUsage As Long
   th32ProcessID As Long           ' This process
   th32DefaultHeapID As Long
   th32ModuleID As Long            ' Associated exe
   cntThreads As Long
   th32ParentProcessID As Long     ' This process's parent process
   pcPriClassBase As Long          ' Base priority of process threads
   dwFlags As Long
   szExeFile As String * 260       ' MAX_PATH
End Type
 
Private Type OSVERSIONINFO
   dwOSVersionInfoSize As Long
   dwMajorVersion As Long
   dwMinorVersion As Long
   dwBuildNumber As Long
   dwPlatformId As Long           '1 = Windows 95.
                                  '2 = Windows NT
 
   szCSDVersion As String * 128
End Type
 
Private Const PROCESS_QUERY_INFORMATION = 1024
Private Const PROCESS_VM_READ = 16
Private Const MAX_PATH = 260
Private Const STANDARD_RIGHTS_REQUIRED = &HF0000
Private Const SYNCHRONIZE = &H100000
'STANDARD_RIGHTS_REQUIRED Or SYNCHRONIZE Or &HFFF
Private Const PROCESS_ALL_ACCESS = &H1F0FFF
Private Const TH32CS_SNAPPROCESS = &H2&
Private Const hNull = 0
 
Private Function StrZToStr(s As String) As String
   StrZToStr = Left$(s, Len(s) - 1)
End Function
 
Private Function getVersion() As Long
   Dim osinfo As OSVERSIONINFO
   Dim retvalue As Integer
   osinfo.dwOSVersionInfoSize = 148
   osinfo.szCSDVersion = Space$(128)
   retvalue = GetVersionExA(osinfo)
   getVersion = osinfo.dwPlatformId
End Function
 
Public Function ExcelTimesTwo() As Boolean
    Dim r As Long, HoldPrcs() As String
    Dim FoundExcel As Boolean
 
    ReDim HoldPrcs(0) As String
 
    Select Case getVersion()
 
        Case 1 'Windows 95/98
 
            Dim f As Long, sname As String
            Dim hSnap As Long, proc As PROCESSENTRY32
            hSnap = CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0)
            If hSnap = hNull Then Exit Function
            proc.dwSize = Len(proc)
            ' Iterate through the processes
            f = Process32First(hSnap, proc)
            Do While f
                sname = StrZToStr(proc.szExeFile)
                r = r + 1
                ReDim Preserve HoldPrcs(r) As String
                HoldPrcs(r) = sname
                f = Process32Next(hSnap, proc)
            Loop
 
        Case 2 'Windows NT
 
            Dim cb As Long
            Dim cbNeeded As Long
            Dim NumElements As Long
            Dim ProcessIDs() As Long
            Dim cbNeeded2 As Long
            Dim NumElements2 As Long
            Dim Modules(1 To 200) As Long
            Dim lRet As Long
            Dim ModuleName As String
            Dim nSize As Long
            Dim hProcess As Long
            Dim i As Long
            'Get the array containing the process id's for each process object
            cb = 8
            cbNeeded = 96
            Do While cb <= cbNeeded
                cb = cb * 2
                ReDim ProcessIDs(cb / 4) As Long
                lRet = EnumProcesses(ProcessIDs(1), cb, cbNeeded)
            Loop
            NumElements = cbNeeded / 4
 
            For i = 1 To NumElements
                'Get a handle to the Process
                hProcess = OpenProcess(PROCESS_QUERY_INFORMATION _
                Or PROCESS_VM_READ, 0, ProcessIDs(i))
                'Got a Process handle
                If hProcess <> 0 Then
                    'Get an array of the module handles for the specified
                    'process
                    lRet = EnumProcessModules(hProcess, Modules(1), 200, _
                                        cbNeeded2)
                    'If the Module Array is retrieved, Get the ModuleFileName
                    If lRet <> 0 Then
                        ModuleName = Space(MAX_PATH)
                        nSize = 500
                        lRet = GetModuleFileNameExA(hProcess, Modules(1), _
                            ModuleName, nSize)
                        r = r + 1
                        ReDim Preserve HoldPrcs(r) As String
                        HoldPrcs(r) = Left(ModuleName, lRet)
                        If InStr(HoldPrcs(r), "EXCEL.EXE") Then
                            If FoundExcel Then
                                ExcelTimesTwo = True
                                Exit Function
                            End If
                            FoundExcel = True
                        End If
                    End If
                End If
                'Close the handle to the process
                lRet = CloseHandle(hProcess)
            Next
 
    End Select
 
End Function

 

[lalbatros]

Norie,

ActiveWorkbook is reffered to only in the VBA interface.
The dll receives a reference to it in an Object variable.

Within the dll, there is only one line of code connecting to excel:

thisNameVal = ThisRecipeWorkbook.Names(nm).RefersToRange.Value

This line supplies the dll with anything that it needs.
The values calculated by the dll are returned in the most classical way without looking back in the excel file.

From my last reading, it seems that really an excel instance is created even if I didn't ask for it explicitely.
If I had asked for it explicitely, things would have been simpler since I would have an hand on it and I could "Quit" this instance.
However, I am not able to ask for this excel instance explicitely, or at least I don't see how.
After all, I need the workbook to be passed to my dll, and working with this object creates the instance spontaneously, as far as I understood it.

 

[lalbatros]

Thanks a lot all of you.
You helped me understand faster what happens.

I have passed "activeWorkbook.Application" to the dll and used it explicitely, and I switched to late binding.
This solves the problem, and -strange enough- it also improved the speed.

I also realized that I could go even further with what I learned.
For example I will try the "withevents" to see if I could remote even more code.
Maybe ultimately I would only need to initialize the remote object and remove all macros from the workbook.
However, I think there will always remain a need to interface the remoted UDF.
Or would you know a way to remote a UDF without creating interface code?

 

[Legacy 98055]

Or would you know a way to remote a UDF without creating interface code?

<A HREF="http://support.microsoft.com/kb/285337" TARGET="_blank">How To Create a Visual Basic Automation Add-in for Excel Worksheet Functions</A>
From link:
"Integration of COM Add-ins (called Automation Add-ins) in Excel 2002 and later has been enhanced such that COM Add-in functions may now be called directly from a worksheet formula without the need for a VBA wrapper..."