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

    Frage

  • 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.

    Dienstag, 13. März 2012 05:10

Antworten

  • 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


    Dienstag, 13. März 2012 10:32

Alle Antworten

  • 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

    Dienstag, 13. März 2012 06:51
    Beantworter
  • 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,





    Dienstag, 13. März 2012 07:49
  • 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


    Dienstag, 13. März 2012 10:32