VBA importing udf module from Add In to Workbook

Question

So, i have an .xlam AddIn with several UDFs in it. As a workaround to a well known absolute path problem I am importing my UDF to a current Workbook so the UDFs could be called from the Workbook, not from the AddIn using the following code:

Sub CopyOneModule()
Dim FName As String
On Error GoTo errhandler
With ThisWorkbook
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("HMFunctions").Export FName
End With
ActiveWorkbook.VBProject.VBComponents.Import FName
MsgBox ("Functions successfully imported")
errhandler:
If Err.Number <> 0 Then
        Select Case Err.Number
        Case Is = 0:
        Case Is = 1004:
            MsgBox "Please allow access to Object Model and try again.", vbCritical, "No Access granted"
        End Select
End If

It seems to be working fine. So, my (probably dumb) question is: Is there a way to make workbook with imported UDFs "unsee" the same module stored in the AddIn? It's needed to avoid the following situation which may be very confusing:

Thank you in advance.

Could you not just unload or disable the add-in? Otherwise could you do a text replace in the txt file before importing? — snoopen, Feb 27 '16 at 12:46
For example the add-in could have the udf's marked Private which you could remove in the txt file — snoopen, Feb 27 '16 at 12:49
Thank you so much! I did simple replace in my dummy text file and now everything works just the way i want it to. — Salabon11, Feb 27 '16 at 18:43

score 0 · Accepted Answer · edited May 23 '17 at 12:30

Removing the Private labels from temporary text file, as suggested by snoopen worked like a charm. Question closed. Here is the final code I am using for import:

    Sub CopyOneModule()
Dim FName As String
Dim FileContent As String
Dim TextFile As Integer
Dim ws As Workbook

Set ws = ActiveWorkbook

On Error GoTo errhandler
With ThisWorkbook
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("HMFunctions").Export FName
End With

TextFile = FreeFile
Open FName For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
Close TextFile
FileContent = Replace(FileContent, "Private", "")
TextFile = FreeFile
Open FName For Output As TextFile
Print #TextFile, FileContent
Close TextFile

ws.VBProject.VBComponents.Import FName
MsgBox ("Functions successfully imported")
errhandler:
If Err.Number <> 0 Then
        Select Case Err.Number
        Case Is = 0:
        Case Is = 1004:
            MsgBox "Please allow access to Object Model and try again.", vbCritical, "No Access granted"
        End Select
    End If

End Sub

VBA importing udf module from Add In to Workbook

1 Answers1