The REGISTER macro function and the Declare Visual Basic statement are not equivalent. Using the REGISTER function registers the DLL function only for the macro sheet or the worksheet. You cannot directly call the function from a Visual Basic module without using the Declare statement, even if you have already used the REGISTER function and called the same DLL function from a macro sheet. You can, however, call the function from a Visual Basic module by using the ExecuteExcel4Macro method to call the registered DLL function just as you would call any other Microsoft Excel macro function.
Public functions are those declared without either the Public or Private keywords (or explicitly declared with the Public keyword). If you declare the DLL function as Private, you can call the function only from the Visual Basic module where it is declared.
In addition, the REGISTER and UNREGISTER functions allow you to dynamically load and unload DLL functions. This is not possible in Visual Basic. Once a DLL function is loaded by a Visual Basic module (the first time a declared function in the DLL runs), the DLL remains loaded until you close the workbook that contains the Visual Basic module.
You can use the dynamic loading behavior of the REGISTER and UNREGISTER functions by creating a stub macro on a macro sheet. The stub macro should accept appropriate arguments for the DLL function, register the DLL function, call it, unregister it, and return the return value from the DLL function. You can then use the ExecuteExcel4Macro function to call the stub macro from Visual Basic. The stub macro in turn calls the DLL and returns to Visual Basic. A simple example of this is shown in the CallEvaluateExample in Sample\Example\EXAMPLE.XLS.