Search code examples
excelvbaexcel-addins

VBA importing udf module from Add In to Workbook


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.


Solution

  • 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