none
Call DLL function from EXCEL VBA RRS feed

  • Question

  • I am trying to call function from dll which I have made in Visual Studio. I receive error message 

    "Run-time error '453':

    Cant find DLL entry point SGR in

    C:\Users\Aca\source\repos\PERMHP_1\PERMHP_1\bin\Debug\PERMHP_1.dll"

    I have made dll under visual basic project: Class Library (.NET Framework).

    I have added my dll to VBA project references

    Can anyone help how to call function?

    DLL CODE:

    Option Strict On
    Option Explicit On
    Public Class Residual_SGR
    
        Public Function SGR(VISW As Double, VISO As Double, FI As Double, PB As Double, T As Double, RSPB As Double, CW As Double, GAMAO As Double, PMIN As Double, SWEX As Double, EQSW As Double, AK As Double, PCM As Double) As Double
            Dim A, B, X, C As Double
    
            PB = PB * 14.22334
            T = T * 1.8 + 32.0
            RSPB = RSPB * 5.6145821
            GAMAO = 141.5 / GAMAO - 131.5
            PMIN = PMIN * 14.22334
            A = -758.0 + 0.86 * FI + 5.29 * CW - 0.0444 * CW ^ 2 - 77.2 * Math.Log(CW) - 0.0386 * RSPB
            B = A + 0.0000533 * RSPB ^ 2 + 4.06 * GAMAO - 0.057 * GAMAO ^ 2
            X = 0.000156 * (PB / PMIN) ^ 2
            C = B + X + 2.02 * Math.Log(PB / PMIN) + 0.0123 * PB - 0.00000369 * PB ^ 2
            SGR = C - 3.71 * T + 0.00643 * T ^ 2 + 253.0 * Math.Log(T)
            Return SGR
        End Function
    End Class

    VBA CODE:

    Option Explicit
    
    Private Declare PtrSafe Function SGR Lib "C:\Users\Aca\source\repos\PERMHP_1\PERMHP_1\bin\Debug\PERMHP_1.dll" _
    (ByRef VISW As Double, ByRef VISO As Double, ByRef FI As Double, ByRef PB As Double, ByRef T As Double, ByRef RSPB As Double, ByRef CW As Double, _
    ByRef GAMAO As Double, ByRef PMIN As Double, ByRef SWEX As Double, ByRef EQSW As Double, ByRef AK As Double, ByRef PCM As Double) As Double
    
    
    ' using the function from VBA
    Sub Button1_Click()
       MsgBox SGR(0.4406, 0.42512, 22.2, 141, 82.5, 95.8, 35.8, 0.82, 50, 2, 0.2, 0, 0)
    End Sub

    Thursday, June 7, 2018 7:53 PM

Answers

All replies

  • I have read that article and I have set all. I think so. I still do not know what to fix

    Thursday, June 7, 2018 10:16 PM
  • There are many articles about exposing .net class libraries to VBA through Com Callable Wrappers if you search the internet. Another discussion of the subject is at https://docs.microsoft.com/en-us/dotnet/framework/interop/com-callable-wrapper.

    There are also several articles on the subject at www.codeproject.com

    Finally, another option would be to create a Win32 DLL that exports your function in the conventional way by using C or C++.

    Thursday, June 7, 2018 10:44 PM
  • Hello Aleksandar_NS,

    This forum(Excel for Developers) is for development issue related to Excel Object Model and I think your issue is more related to VBA. So I will move the thread to  Visual Basic for Applications (VBA) forum.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 8, 2018 7:36 AM
  • Ok, thanks!
    Friday, June 8, 2018 8:02 AM
  • First I suggest you switch to C# for .NET COM.  When I had to do a .NET COM DLL I got the book 'COM and .NET Interoperability' by Andrew Trolelson.  Look at chapter 10/11.  It is from 2003 but .NET COM DLLs have not changed.  You can get a used copy on Amazon.  I got mine a year ago for ~$15.

    Friday, June 8, 2018 12:36 PM
  • From what I can determine it appears you are using a standard Windows calling convention. If you created a .NET DLL using the COM Class (which has the COM interfaces) then the functions would be exposed via COM. There is no need for the Declare statement and you would access the functions through a COM instance of your library instead. It would look something like the following (MyProject is used because I don't know your DLL project library name):

    Dim MyLibrary As New MyProject.Residual_SGR 'create instance of Class

    MsgBox MyLibrary.SGR(0.4406, 0.42512, 22.2, 141, 82.5, 95.8, 35.8, 0.82, 50, 2, 0.2, 0, 0)

    The below article is a bit old, but the concept is the same:

    How to call a Visual Basic .NET or Visual Basic 2005 assembly from Visual Basic 6.0


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Aleksandar_NS Monday, June 11, 2018 7:45 PM
    • Unmarked as answer by Aleksandar_NS Tuesday, June 12, 2018 12:18 PM
    Friday, June 8, 2018 12:48 PM
  • I'm not familiar with VB.NET.  It is easy to switch to C#.  I converted your DLL to C#.  Create Class Library using .Net Framework.

    using System;
    using System.Runtime.InteropServices;
    
    namespace MyMathServer
    {
        [ClassInterface(ClassInterfaceType.AutoDual)]
        [Guid("B94B31F7-19C4-4B9F-813B-543837E75BD5")]
        [ComVisible(true)]
        public class DotNetCalc
        {
            public DotNetCalc() { }
    
            public double SGR(double VISW, double VISO, double FI, double PB, double T, double RSPB, double CW, double GAMAO, double PMIN, double SWEX, double EQSW, double AK, double PCM)
            {
                double A;
                double B;
                double X;
                double C;
                double result;
    
                PB = PB * 14.22334;
                T = T * 1.8 + 32.0;
                RSPB = RSPB * 5.6145821;
                GAMAO = 141.5 / GAMAO - 131.5;
                PMIN = PMIN * 14.22334;
                A = -758.0 + 0.86 * FI + 5.29 * CW - 0.0444 * Math.Pow(CW, 2) - 77.2 * Math.Log(CW) - 0.0386 * RSPB;
                B = A + 0.0000533 * Math.Pow(RSPB, 2) + 4.06 * GAMAO - 0.057 * Math.Pow(GAMAO, 2);
                X = 0.000156 * Math.Pow((PB / PMIN), 2);
                C = B + X + 2.02 * Math.Log(PB / PMIN) + 0.0123 * PB - 0.00000369 * Math.Pow(PB, 2);
                result = C - 3.71 * T + 0.00643 * Math.Pow(T, 2) + 253.0 * Math.Log(T);
                return result;
            }
        }
    }

    Call it using this

    Sub Test()
      Dim SGR As New MyMathServer.DotNetCalc  ' Reference to MyMathServer
      Dim result As Double
      
      result = SGR.SGR(0.4406, 0.42512, 22.2, 141, 82.5, 95.8, 35.8, 0.82, 50, 2, 0.2, 0, 0)
      Debug.Print result  ' 48.8929390738156
      
    End Sub

    It should be the right result, if not check my conversion.

    Items to note:

    1. Run Visual Studio as Admin
    2. In Project Properties mark Assembly as COM and Register COM for Interop
    3. When it registers COM it litters the registry with over 17 entries.
    4. You need to quit Excel when you build project.  It won't update registry if Excel is running.
    5. To remove dll from registry use VS Command Prompt.  Run as admin.
           regasm /u /tlb "C:\Users\<user>\Documents\Visual Studio 2017\Projects\MyMathServer\MyMathServer\bin\Debug\MyMathServer.dll"


    • Edited by mogulman52 Saturday, June 9, 2018 12:58 PM
    • Marked as answer by Aleksandar_NS Monday, June 11, 2018 7:34 AM
    • Unmarked as answer by Aleksandar_NS Tuesday, June 12, 2018 12:18 PM
    Saturday, June 9, 2018 12:51 PM
  • The same result can easily be achieved in VB.Net if the OP used "Add Class" to add a COM class to the project and then defined the SGR function in that class.  The rest of the steps to set theproject to Register for COM Interop, build as Admin, etc. would be virtually identical.
    • Marked as answer by Aleksandar_NS Monday, June 11, 2018 7:34 AM
    • Unmarked as answer by Aleksandar_NS Tuesday, June 12, 2018 12:18 PM
    • Marked as answer by Aleksandar_NS Wednesday, June 13, 2018 6:01 PM
    Saturday, June 9, 2018 2:33 PM
  • Sample VB.Net project that creates a DLL with functions callable from Excel VBA can be downloaded from VBAFuncs.zip.  It was created using VS2015.  Tested with Excel 2013.

    After building the project --


    Add a reference (for object browser, etc.)

    What the object browser shows -

    Test macro to call DLL methods -

    Results of calling test macro -

    and

    • Marked as answer by Aleksandar_NS Monday, June 11, 2018 7:34 AM
    • Unmarked as answer by Aleksandar_NS Tuesday, June 12, 2018 12:18 PM
    • Marked as answer by Aleksandar_NS Wednesday, June 13, 2018 5:59 PM
    Saturday, June 9, 2018 3:58 PM
  • Thank you all for grate help!!!!
    Monday, June 11, 2018 7:36 AM
  • I'm not familiar with VB.NET.  It is easy to switch to C#.  I converted your DLL to C#.  Create Class Library using .Net Framework.



    Not sure why you are responding to my post.

    Seriously though, it's a VBA forum and you don't know VB.NET so you post a C# solution? :-p


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, June 11, 2018 1:28 PM
  • I have downloaded your example VBFuncs.zip and build it under Visual Studio 2017 witho no errors. I have succsefuly adedd reference in Excel 2016 VBA. I receive error in VBA code line

    Set res = New Residual_SGR

    I receive error message : Actvex component can't create object

    Can office cause this problem or any settings?

    Tuesday, June 12, 2018 12:24 PM
  • Thank you all for grate help!!!!

    So can you explain why you unmarked the answers?
    Tuesday, June 12, 2018 12:26 PM
  • If it is helpful I can upload my project from Visual Studio 2017?
    Tuesday, June 12, 2018 12:27 PM
  • I have tried to use dll under office 2016 and recive erorr as I have send i previous message.

    Tuesday, June 12, 2018 12:29 PM
  • I have tried to use dll under office 2016 and recive erorr as I have send i previous message.

    Well, you haven't posted any detail about your errors so I can only guess.

    There is probably some incompatibility between the way you built the project and the version of Office 2016 on your system.  For example, if your version of Office is 64 bit then you must make sure that you build and register a 64 bit version of the COM Dll.  A 32 bit version of Office requires a 32 bit version of the Dll and registration as a 32 bit COM server.

    Or perhaps you need to target a different version of the .Net Framework.

    I don't have Office 2016 so I cannot reproduce your issues, but there's nothing wrong with the code provided or the methodology.  You just need to find whatever incompatibility is causing your problems.


    • Edited by RLWA32 Tuesday, June 12, 2018 1:00 PM added registration comments
    Tuesday, June 12, 2018 12:36 PM
  • I just saw the post mentioning the ActiveX error about cannot create object.  My earlier suggestions about incompatibility seem relevant, especially making sure that the bitness and registration of the Dll match the bitness of the Office version being used.

    You say that you could add a reference to the COM DLL's type library in Excel 2016.  What does Excel's object browser show?  Post an image similar to the one that I posted earlier for the object browser.
    • Edited by RLWA32 Tuesday, June 12, 2018 2:30 PM added object browsers
    Tuesday, June 12, 2018 2:23 PM
  • Open up the Object Browser (F2) and look for the .NET library. Check for the Class name (Residual_SGR) to make sure it is the same. Sometimes the name is different when working through interop, but the name in the Object Browser needs to be the same in your VBA code.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 12, 2018 3:07 PM
  • I have Office 2016 64bit

    In Visual Studio compile configuration is set to Active(Debug) and platform to Active (Any CPU)

    I am not able to post image "Body text cannot contain images or links until we are able to verify your account."

    Why? What I must do?

    How to upload my Visual Studio project as ZIP file?

    Tuesday, June 12, 2018 6:11 PM
  • Run Visual Studio as Adminstrator and open the project.

    Go to the Project Properties.

    Select the Compile Options.

    Look for the "Target CPU" dropdown.  Change it from "Any CPU" to x64.

    Before -

    After -

    Rebuild the project.

    This should eliminate the runtime error - ActiveX component can't create object.

    Wednesday, June 13, 2018 1:55 AM
  • It works. Thank you!!!

    I have one more question.

    Have ave you generate next code numbers for GUID = 9178589a-7eb8-4bb8-ba63-b4b8ff76c9ca and next in vb code:

    Public Const ClassId As String = "21815572-07ac-48c7-bcba-d0de7db6eef6"
        Public Const InterfaceId As String = "80b70fc2-1036-47d7-af6a-e0c11dddc01c"
        Public Const EventsId As String = "c8d56ef0-0c5d-4ba4-bae2-3175b6956f5d"
    Is that standard numbers or I must generate them and how?

    Wednesday, June 13, 2018 4:39 PM
  • It works. Thank you!!!

    I have one more question.

    Have ave you generate next code numbers for GUID = 9178589a-7eb8-4bb8-ba63-b4b8ff76c9ca and next in vb code:

    Public Const ClassId As String = "21815572-07ac-48c7-bcba-d0de7db6eef6"
        Public Const InterfaceId As String = "80b70fc2-1036-47d7-af6a-e0c11dddc01c"
        Public Const EventsId As String = "c8d56ef0-0c5d-4ba4-bae2-3175b6956f5d"
    Is that standard numbers or I must generate them and how?

    First, kindly mark the appropriate responses as answers since your initial question has been fully resolved.

    When you have a different question (as shown above) you should ask a new question and create a new thread  instead of tacking a different subject onto the end of an existing question.  Otherwise, the new question, which might be relevant to other people, has absolutely no visibility.

    Having said the above, the GUID values were automatically generated by Visual Studio when it added the COM class to the project.  There is also an external tool, available in the "Tools" menu that can be used to generate GUIDs (also called UUIDs).

    Wednesday, June 13, 2018 5:35 PM
  • They actually removed the GUID tool from newer versions of Visual Studio, but you can add this functionality back in:

    https://marketplace.visualstudio.com/items?itemName=kylebahrke.GenerateGUIDforVisualStudio2015

    Or, you can use one of the many web pages:

    https://www.guidgen.com/

    https://www.guidgenerator.com/

    https://www.uuidgenerator.net/guid


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Aleksandar_NS Friday, June 15, 2018 2:04 PM
    Friday, June 15, 2018 1:30 PM
  • They actually removed the GUID tool from newer versions of Visual Studio, but you can add this functionality back in:

    https://marketplace.visualstudio.com/items?itemName=kylebahrke.GenerateGUIDforVisualStudio2015

    Or, you can use one of the many web pages:

    https://www.guidgen.com/

    https://www.guidgenerator.com/

    https://www.uuidgenerator.net/guid


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    In my installation of VS2017 the guid generation tool is present on the tools menu; the guidgen.exe tool was installed in VS2017's    ..\Common7\Tools folder.  Perhaps it depends on which workloads are installed?

    As shown on my system -

    Friday, June 15, 2018 2:03 PM
  • Bump.  For some reason the forums are not showing my earlier response about the existence of guidgen.exe
    Friday, June 15, 2018 2:45 PM