Excel Macro Issue(Calling a DLL's function in Excel Macro)

Question

thanks for your feed back.I read the Link "http://support.microsoft.com/kb/828550" and apply the Changes as per suggested in the Link.then when i Executed the Code when the function of DLL calls it give error 429 "Active X component did not Create Object"...

here i depicted the VBA code which i use. ---------------------------------------------------------------------

Sub CallMult3()

On Error GoTo Err

Dim Count As Integer Count = Cells(35, 16)

Dim abc As New SampleTEST.Cls_SampleTest

'Dim abc As SampleTest.Cls_SampleTest

'Set abc = CreateObject("SampleTest.Cls_SampleTest") '//* if this line is not commented then it give Error 429

FlagForAuto = 1

CounterForInitialEntry3Call = 0 Range("X16:AA35").Select

Selection.ClearContents

Cells(1,1) = abc.Main(ThisWorkbook.FullName, Cells(440, 5), Cells(12, 11)) '//*when this line Executed it gives error 429

FlagForAuto = 0

Exit Sub

Err:

MsgBox Err

Cells(10, 1) = 4

Cells(11, 15) = 99

End Sub -------------------------------------------------------------------

I also include the libraries as reference..

1. Sample.tlb

2. Microsoft Active X 6.0 Object Library

3. 'Microsoft Office 14.0 Access Database Engine Object'

I run this code in MS Office 2010 64 bit and 64 bit Operating System.

So, please kindly help us address this issue. Awaiting help eagerly.

Thank you,

regards,

Vaibhav.

Answer 1

Look on the comments

Sub CallMult3()
On Error GoTo Err
Dim Count As Integer: Count = Cells(35, 16)
Dim abc As New SampleTest.Cls_SampleTest
Dim abc As SampleTest.Cls_SampleTest                'Are you have class SampleTest ? and Sub Class_  named .Cls_SampleTest
Set abc = CreateObject("SampleTest.Cls_SampleTest") 'If do not that why you have error

FlagForAuto = 1
CounterForInitialEntry3Call = 0
Range("X16:AA35").Select: Selection.ClearContents
Cells(1, 1) = abc.Main(ThisWorkbook.FullName, Cells(440, 5), Cells(12, 11)) 'no Class no abc value
FlagForAuto = 0
Exit Sub

Err:
MsgBox Err
Cells(10, 1) = 4
Cells(11, 15) = 99
End Sub

---

Oskar Shon, Office System MVP

Press if Helpful; Answer when a problem solved

Answer 2

Look on the comments

Sub CallMult3()
On Error GoTo Err
Dim Count As Integer: Count = Cells(35, 16)
Dim abc As New SampleTest.Cls_SampleTest
Dim abc As SampleTest.Cls_SampleTest                'Are you have class SampleTest ? and Sub Class_  named .Cls_SampleTest
Set abc = CreateObject("SampleTest.Cls_SampleTest") 'If do not that why you have error

FlagForAuto = 1
CounterForInitialEntry3Call = 0
Range("X16:AA35").Select: Selection.ClearContents
Cells(1, 1) = abc.Main(ThisWorkbook.FullName, Cells(440, 5), Cells(12, 11)) 'no Class no abc value
FlagForAuto = 0
Exit Sub

Err:
MsgBox Err
Cells(10, 1) = 4
Cells(11, 15) = 99
End Sub

---

Oskar Shon, Office System MVP

Press if Helpful; Answer when a problem solved

Thanks for your Feed Back.

In

Dim abc as New SampleTest.Cls_SampleTest

'SampleTest' is my .tlb file name which reference i included in Excel Macro and 'cls_sampleTest' is the Class file which created in the DLL.

Let me summerize you..

I create a class file 'cls_sampleTest' in class Library on visual Studio 2008.then build that solution which gives me SampleTest.dll and SampleTest.tlb.I reference that SampleTest.tlb in Excel macro.after that i create object 'abc' as New  tlbFileName.ClassName.

Now why i did not value.please help me its very urgent...

Thank you,

Answer 3

Here are some comments about you code.

1) A dll stops executing when the function call is complete and execution is returned to your VBA module.  Any memory that is used inside the dll will be destroyed when the dll completes execution.

There is one exception to the statement.  If you use windows operating system to allocate memory.  Thn you have to remember to deallocate this memory when you are finished using the dll, otherwise, you will have a memory leak.

2)  You need to delcare any variables or structures (memory that is kept after the dll is executed) in the VBA code.  Then pass the parameters to the DLL when you callthe dll.

3) You can't use a new statement or a createobject with a dll.  What you can do is declare the variable/structure in the VBA code and then call the dll to initialize the variable/structure by passing the variable as a pointer (ByRef).

---

jdweng