Is it possible to call a UDF function by name in Excel?

Question

I teach my students to write UDF in Excel and offer them an Excel add-in with their functions in the module. They all have standard function names and input variables. I am trying to find a way to easily write some function calls that should return known solutions, then disable my add-in and activate each student's add-in to see if their code is working as it should.

However, when I turn off my add-in, the functions that I create change to directly reference my add-in file, so when I turn on the student add-in, the functions do not reference the student add-ons as I want them.

Does anyone know how I could do this without reinstalling the test equations for each student work?

Thank!

Answer 1

Instead of using your own add-in, can you add your functions to your personal.xlsb book and add each student to the test book in turn? Then you can refer to your functions as

=PERSONAL.XLSB!TESTFUNCTION()

and your student functions are

=TESTWORKBOOK.XLSX!TESTFUNCTION()

Cells that link to your personal book will always refer to your own formulas. For each student, simply copy and paste each module that they created into the test book, and Excel should pick them up.

Of course, you will need to copy and paste the student’s last work, so this will not work if you want to compare each student with everyone.