Excel VBA to ExcelDNA?

[Robert Hudson]

Using VBA , I've written a moderately complex (~500 lines) Excel Add-In (.xlam) that computes the solution to chemical equilibrium problems and outputs a matrix. It works well, except for the fact that saved files that call the UDF end up with a path to the .xlam file that has to get reset every time I open it (and doesn't transport to other machines well).

In trying to track down a solution to this, I came across excel-DNA and so I'm wondering a) if ExcelDNA is a better way to create and distribute the Add-In and b) if it solves the problem I tried to describe above. I need it to run on MacOS as well.

Thanks in advance for any advice you can provide.

[Govert van Drimmelen]

Hi Robert,

 

Excel-DNA indeed lets you make add-ins that don’t have the problem with the paths. Internally Excel stores the function names from .xll add-ins without extra information, so the add-in and sheets using it are easy to distribute .

 

It should also not be too hard to port your VBA code to VB.NET, and I’m happy to help if you want to give it a try.

 

However, Excel-DNA does not support the Mac at all, and there is no workaround for this.

So you’ll have to decide how firm a constraint this is.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/8e2b48bc-237c-4e39-8706-d9a0a4326d5cn%40googlegroups.com.

[Patrick O'Beirne]

Have you investigated a way to not need the reference to the addin?

Is the UDF always needed to recalculate results the next time the workbook is opened? If so then necessarily the addin must be present always.

If the workbook does not need to be updated after saving then instead of a UDF, use a Ribbon button which calls a sub that populates cells with values only in the active workbook.

If that's awkward because the locations change, then write a Save macro to search for all cells containing the UDF and replace them with values before saving.

IF all the cells are in one contiguous area, such as a column, then in the UI, this is how a user might  do it without a macro:

   Use Ctrl+f to Find. Enter "=MYUDF(" into the Find What box.
   In the Look In box, select Formulas. Click Find All.

   Press Ctrl+a to select all the cells.
   Close the Find dialog.

   Finally press Ctrl+c to copy, Paste Special, Values, to convert to values.

VBA in Mac Excel is an awkward tool. Here's Ron de Bruin's dedicated website:

https://www.macexcel.com/

HTH

Patrick

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/044101d77240%241226e010%243674a030%24%40gmail.com.

[Robert Hudson]

Govert:

In the long run, I do need a solution that works on a Mac, but for now I'm happy to stick with Windows if it isn't too much work. I'd assumed an example exists out there that I could adapt. Perhaps you can just point me to that for starters.

I'm not ready to release all the code yet, but if you don't mind advising on how to port, the beginning and end of the UDF are below. There's a lot of extra variables defined that I've cut out here.

Option Explicit

    Dim cellText As Variant

    Dim i As Long, j As Long, k As Long, M As Long, N As Long, Ns As Long, Nc As Long

    Dim typeX As Long, mbType() As Long, Scomp() As Long

    Dim logK() As Double

    'Option Base 1

Public Function testeql(c As Range, Optional comp1 As Long = 0, Optional X1 As Double = -99.9, Optional comp2 As Long = 0, Optional X2 As Double = -99.9, Optional comp3 As Long = 0, Optional x3 As Double = -99.9)

    

    If IsArray(c) = False Then

         c = Range("Tableau")

    End If

    

'Test if Tableau is defined.

    Dim rBot As Long, cLeft As Long

    Dim iter As Long, itermax As Long, CallerCols As Long, CallerRows As Long, compType() As Long, jCompOld() As Long, jCompNew() As Long

    Dim nTOT As Long, nFIX As Long, nMAX As Long, jC As Long, nY As Long

    Dim Temp As Double, big As Double

    Dim err_y As Double, err_x As Double

    Dim nSpecies() As String

    Dim sp_con() As Double

    Dim Ncomp() As String

    Dim inArray() As Variant

    Dim A() As Double

    Dim X() As Double

    Dim logX() As Double

    Dim totX() As Double

    Dim Y(), Y_()       'Y and Y transpose

    Dim Z(), Z_1()      'Z and Z inversion

    Dim dX(), dX_()     'dX and dX transpose

    Dim t() As Double   't and t transpose

    Dim r() As Variant

    Dim ln10 As Double, xx As Double

    Dim outCol As Long, cLogK As Long, iCount As Long

    Dim Flag As String, compLabel As String

    Dim outComps As Long

    Dim dFlag As Boolean, xFlag As Boolean

    Const tol_totX As Double = 0.00000001

    Const tol_dX As Double = 0.000001

    Const VERY_TINY As Double = 1E-50

    Const rTop As Long = 1

    CallerCols = Application.Caller.Columns.Count

    CallerRows = Application.Caller.Rows.Count

     Ns = WorksheetFunction.Max(CallerCols,CallerRows)

     Nc = 0

    If CallerRows > CallerCols Then

        If CallerRows > Ns Then

            outComps = 1

         Else:

            outComps = 0

            Ns = CallerRows

            Nc = 0

        End If

        ReDim r(1 To Ns + Nc, 1 To CallerRows)

        

        outCol = Application.WorksheetFunction.Min(2, CallerCols)

        

        For i = 1 To Ns Step 1

           r(i, outCol) = 0

        Next i

        If outComps Then

            For i = 1 To Nc Step 1

                r(i + Ns, outCol) = 0

            Next i

        End If

        If CallerCols >= 2 Then

            For i = 1 To Ns Step 1

                r(i, 1) = i

            Next i

            If outComps Then

                For i = 1 To Nc Step 1

                    r(i + Ns, 1) = i

                Next i

           End If

        End If

    Else

         If CallerCols > Ns Then

            outComps = 1

         Else:

            outComps = 0

            Ns = CallerCols

            Nc = 0

        End If

        ReDim r(1 To CallerRows, 1 To Ns + Nc)

        outCol = Application.WorksheetFunction.Min(2, CallerRows)

        

        For i = 1 To Ns Step 1

           r(outCol, i) = i

        Next i

        If outComps Then

            For i = 1 To Nc Step 1

                r(outCol, i + Ns) = 0

            Next i

        End If

        If CallerRows >= 2 Then

            For i = 1 To Ns Step 1

                r(1, i) = i

            Next i

            If outComps Then

                For i = 1 To Nc Step 1

                    r(1, i + Ns) = i

                Next i

           End If

        End If

    End If

       

    testeql = r

End Function

As you can guess, the idea in using the function is to i) select a range of cells for output (usually n rows by 2 columns), ii) as arguments provide a range and optionally a couple of scalar values, and iii) type CNTRL+SHIFT+ENTER. In this case, the output is a bunch of integers.

[Robert Hudson]

How will the speed of numerical calculations be in Excel-DNA compared to VBA? I use this in conjunction with Solver, which can take several minutes to find an optimal set of parameters.

On Tuesday, July 6, 2021 at 3:22:40 AM UTC-5 Govert van Drimmelen wrote:

[Robert Hudson]

Thanks for the suggestions, Patrick. In general, it's most useful to keep the function calls "live". I assume Mac Users could just use an .xlsm rather than create an .xll using Excel-DNA for them.

[Govert van Drimmelen]

Hi Robert,

 

So so that I have some idea of where you’re starting – have you made a simple Excel-DNA add-in with a tiny function to test?

If not, I suggest you Install Visual Studio: Tutorials/InstallVisualStudio.md at master · Excel-DNA/Tutorials (github.com)

And then make your first VB.NET add-in: https://www.youtube.com/watch?v=UEIO2Q8HyD8&t=1678s

 

Once you’ve got that going, I see a few issues to discuss which appear in your specific function:

• Dealing with optional and default arguments
• Dealing with a Range parameter by taking an Object(,) array directly
• Reading data from the sheet (the Range(“Tableau”) line)
• Using worksheet functions like Min and Max – you might replace these with built-in functions instead
• Dealing with Option Base (you seem to have that commented out, so it might not be an issue)

 

My suggestion would be to first make a simple function that avoids all of these issues, then address them one-by-one as needed.

A function which avoids these issues should work the same in VBA and VB.NET. So that would be a nice start for you.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/9095d963-f036-45d9-a425-ade8a7e9cc33n%40googlegroups.com.

[Hudson, Robert J M]

Will give your suggestions a try.

I did install VSTO 2019, but used the wrong .NET version I think.

 

Looking forward to more feedback on the code.

I haven’t found a VBA “Max” function, which is why I’m using the worksheet function. I don’t make that much use of it, though I do use worksheet MMULT and MINVERSE a lot. I assumed Excel would be much more efficient than coding directly in VBA for that. Is that not the case?

[Kedar Kulkarni]

Just adding my experience with similar situation.

1. If we open a excel file with custom functions from xlam file, the custom functions always recalculate but with excel dna it does not (which is good as we avoid expensive calls to database and previous results show assuming the formulas dont depend on volatile precedents.). 

2. In our xlam file we override workbook open application level event and once opened we look at the links and any links to our xlam file, we change to this workbook (addin) path. 

This way we were able to always link our functions to correct addin. It works most of the times. 

[Robert Hudson]

I watched your video and made the demonstrated add-in using Visual Studio. So you're suggesting that I make a different function (simpler than eql presumably)?

[Robert Hudson]

I don't quite understand your idea #2? Is it performed using VBA code? Or manually?

[Kedar Kulkarni]

we do it in vba code of the addin (not manual) - try something like following in a class module of the addin. check https://docs.microsoft.com/en-us/office/troubleshoot/excel/create-application-level-event-handler 

Option Explicit

Public WithEvents app As Application  

Private Sub app_WorkbookOpen(ByVal wb As Workbook)

    Call relinkWorkbook(wb)

End Sub

Private Sub Class_Initialize()

    Set app = Application

End Sub

Private Sub relinkWorkbook(wb As Workbook)

    Dim links As Variant

    Dim i As Long

    Dim wbName As String

    Set links = wb.LinkSources(xlExcelLinks)

    wbName = ThisWorkbook.Name

    If IsEmpty(links) Then Exit Sub

    

    For i = UBound(links) To LBound(links) Step -1

        'replace link here to current workbook

        wb.ChangeLink "myaddin", ThisWorkbook.FullName ' This should change the link to current addin path.

    Next

End Sub

[MachThree]

If you install your VBA addin in the user's XLSTART directory (%appdata%\microsoft\excel\XLSTART) you will find that the links are portable across machines and different users (at least on WIndows). There is presumably a similar directory on Macs but I don't have ready access to one to verify this.

Placing the addin in this directory will also cause it to be loaded automatically whenever Excel starts, which may or may not be your intent. If you don't want to have the addin automatically load you can create a sub-directory of the XLSTART directory and load it using some sort of alternative means (e.g. as a registered addin). As long as the addin is in the same named sub-directory for all users this will also work.

If the addin isn't present in the user's XLSTART directory, within a workbook that uses your addin you will see a full path to the addin which will include the drive on which the user's XLSTART directory is stored, but internally within the workbook it is not storing the full pathname, but rather a relative path from XLSTART.

My recollection is that this does not work from the machine XLSTART directory e.g. "C:\Program Files\Microsoft Office\root\Office16\XLSTART" although I haven't tested this for some time. Often in locked down enterprise/educational environments installing addins in this directory is just too hard to arrange anyway.

[Robert Hudson]

Thanks very much. That seems to work!