Easy way to make a "library" of functions/decl available to vba? 1

[electricpete]

This is sort of a followup to thread707-1604231

There is a group of interrelated functions in 101 bas files that I'd like to be able to access from vba periodically.

I am willing to insert all 101 files into modules in a spreadsheet project... once. But I don't want to have to do that every time I use them. And also I don't particularly like having 101 modules clutting up my project modules collection.

Can anyone advise how to proceed in order to make these functions/declarations readily available in any particular spreadsheet without a lot of setup (without inserting 101 modules)?

I thought an excel add-in would be ideal. So I inserted a group of those modules into a spreadsheet. Then I saved it as xla (add-in). Then I closed excel, opened a brand new spreadsheet, added my add-in from spreadsheet tools/add-ins menu. Then went to vba window, inserted a new module and created a test subroutine to try to access some of those functions and declarations. They are not recognized, even though they are declared as public in the add-in project. Apparently public declaration does not extend beyond project?

By the way, I have checked the FAQ's. I will be glad to post my add-in or provide further discussion if it's not clear what I have already tried to do.

 

[electricpete]

Hmmm. Now I'm not even sure add-in is what I want, because I'd like the resulting files to be self-contained and portable excel files.

 

[SkipVought]

ep,

Why do you have 100+ modules?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

Hi Skip.

It's a reasonable question.

This is a library developed by someone else that I want to use.

The modules are interdependent, but there is no roadmap of what modules are required to support what functions. Even if you find the module that has the function you want and load it, it calls to other functions buried in other modules. The only easy way to avoid "not found" errors is to load them all.

 

[Gavona]

How about: All modules in a single workbook. The worksheet in that workbook to be VeryHidden. The modules all to have Option Private Module so any Sub's / Functions don't appear on play / user defined function lists.
Now the workbook that contains the code that references this code either:
Opens your code workbook as an on open event or has a reference to the code workbook (in VBE editor: Tools, references .....). As far as i can see these two approaches are equivalent.

That said I bet there is a utility out there that would tell you about what subs/functions call other subs/functions so you could just copy the relevant modules.

Gavin

 

[Gavona]

In case it helps this code will list all the subs/functions and which modules they are in.

Option Private Module

Option Explicit
 
Sub ListOfMacros()
    On Error Resume Next '< error = reference already set
     'set reference to Microsoft Visual Basic for Applications
     'Extensibility 5.3
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{0002E157-0000-0000-C000-000000000046}", 5, 3
     'now get the list of macros
    Call GetTheList
End Sub
 
Private Sub GetTheList()
    Dim N&, Count&, MyList(200), List$, myRange As Range
    Dim Component As VBComponent
    For Each Component In ActiveWorkbook. _
        VBProject.VBComponents
        With Component.CodeModule
        Set myRange = Range("A1")
        myRange.Offset(N, 0).Value = Component.CodeModule.Name
        
            Count = .CountOfDeclarationLines + 1
            Do Until Count >= .CountOfLines
                MyList(N) = .ProcOfLine(Count, _
                vbext_pk_Proc)
                Count = Count + .ProcCountLines _
                (.ProcOfLine(Count, vbext_pk_Proc), _
                vbext_pk_Proc)
                Debug.Print MyList(N)
                List = List & vbCr & MyList(N)
                myRange.Offset(N, 1) = MyList(N)
                If Count < .CountOfLines Then N = N + 1
            Loop
        End With
        N = N + 1
    Next
    MsgBox List, , "List of Macros"
End Sub

Gavin

 

[electricpete]

Thanks Gavin, that's very helpful as a start and I will certainly use that routine.

In an ideal fantasy world, it'd be nice to have a listing of the interdependencies as well (what are all the modules required to support sub A). But I won't hold my breath on that... don't see how to automatically generate that list.

I have come to understand that the 101 modules are grouped into packages where each package has no more than 20 or so modules. So I think I can tolerate loading 20 modules at a time to support the functions I want in a given spreadsheet... it is a satisfactory workaround for me now that it is not so difficult to load them (just separate the files into directories corresponding to packages, then run my import routine from the other thread).

 

[Gavona]

It should be possible but I don't have the expertise. The code I posted gives a list of modules/functions/subs. Manually you could use Edit,Find within the VBE to locate calls from other modules. Not sure how to automate but I mam looking forward to seeing a solution!
Good luck!

Gavin