How to make a . dll in VBA?

Question

It has some modules of VBA which are used constantly for connection on the company server, has password and needs to be more secure. One alternative I’ve come to see is to use .dll (has one that we use).

How do I make a module that looks something like this

sub conn_server()

    'Meu código que conecta com string de conexão no servidor, com usuário e senha

end sub

You’d have a way of making it one .dll? Or you can’t do the VBA become a .dll?

Answer 1

It is not possible to create DLL’s with VBA, this is only possible with the basic visual (vb6) what I do not recommend to date. So your only solution to isolate your code is through a. NET project.

For that you need a few steps to work everything right. First of all you will be creating an Assembly. NET that is not compatible with VBA COM technology. You will have to make the same compatible.

Step 1: Create an interface

Important: Be noted as Comvisible and with the Guid unique.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace VBA_Sample
{
    [ComVisible(true)]
    [Guid("CB9CDA01-4581-4B9F-B77E-D7F5ACC7F4DD")]
    public interface IMostraVba
    {
        void metodo_teste();

    }
}

Step 2: Create a concrete class where your implementation will be:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace VBA_Sample
{
    [ComVisible(true)]
    [Guid("384A53F1-FEAC-4CFA-ABB8-D100A2E75198")]
    [ClassInterface(ClassInterfaceType.None)]
    public class MostraValor: IMostraVba
    {

        #region IMostraVba Members

            void IMostraVba.metodo_teste()
            {

            }

        #endregion

    }
}

Important: Be noted as Comvisible and with the Guid unique.

Step 3: In the project properties you have to mark as exposed for COM: