Creating and using an Excel VBA framework

[fredlo2010]

Hello,

I have been coding excel for a while and I have a utilities file workbook with a big group of functions and procedures I use often in my projects. Right now every time I need to use of my tool sets I would import it to my project and I would run it normally from there using "Call or Application.run"

But I am trying to create a way where I would have all my utilities always updated and also have the flexibility of having them exposed in the code. It would be a little awkward to have a construction like bellow in my code:

Sub Sample()

    If Application.Run("Utilities.xlsm!U_W_IsWorkbookOpen", "Myworkbok.xlsx") Then
        Debug.Print "The workbook is open"
    Else
        Debug.Print "The workbook is not open"
    End If


End Sub

and a better more readable construction would be:

Sub Better()

    If U_W_IsWorkbookOpen("Myworkbok.xlsx") Then
    
        Debug.Print "The workbook is open"
    Else
        Debug.Print "The workbook is not open"
    End If


End Sub

In order to make the latest to work I would have to add the reference to the workbook that would be using the framework procedure. Which of course will be accompanied with the headaches of adding a reference to all your Workbooks.

Have you done anything like this before? Any ideas?

Thanks

[Tommy]

First thing that pops into my pointed little head is an Add-In. Have you looked into that?

[fredlo2010]

Hi Tommy thanks for the quick reply.

The addin functions has to be referenced in full "Utilities.xlsa!U_W_IsWorkbookOpen" for it to work. Also I will not have intelisense to work with. The autocompletion part is not that bad because I can add it for development and then remove it when deploying.

Thanks

[Tommy]

Try it this way and it works like you want. http://www.fontstuff.com/vba/vbatut08.htm

[SamT]

Hey, Fred, Long time no see. How's work? Doing good?

Does the newer Excels even have a Personal.xlsm? In XP, I created mine by recording a macro. I now have dozens of Modules holding often used subs. Using Option Private Module in some keeps those contents from being used anywhere else. Those I mostly paste into other projects.

If you have an XLStart Folder in the ProgramFiles\Microsoft Office folder, whatever workbook you store there will always ope when you start Excel.

[fredlo2010]

Sam!

Been good; just a lot of work and school; but I can't complain (well I can complain I am taking java this semester lol) How about you ?


I have a personal workbook. But my list of code is growing so much and I constantly reuse them. I do not want to change something in 13 macros when I modify a report. So a combination of "centralized addin + reference" can be a good combination.

Of course I would have to test this a lot to make sure there are no problems. I am sure that there are a few macros that I would not be able to convert over but it would be just a few.

The problem with the XLStart is that not everyone might have it available in their computer plus I will have to handle deployment and update to our servers which is out of the my reach( I would have to talk to IT and see how to do this) but sometimes I want the changes to be applied right away and there is always a propagation delay (in the servers)

So here I am again. Trying to do risky stuff

[SamT]

I have a personal workbook.

From: http://answers.microsoft.com/en-us/o...6-3b05a13f3b3c

In Excel 2007 and 2010, its name is Personal.xlsb,

In WIndows 7, it is stored in C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART where <username> is your Windows login name. The AppData folder is hidden, so you must show hidden files and folders in Windows Explorer in order to see it.

From Ron DeBruin:

To create a Personal.xlsb in the correct folder, create a dummy macro and store it in "Personal Macro Workbook"

I think that Personal.xlsb has special attributes that other books don't

Consider that there are two kinds of standard Modules, the ones with the subs and functions that you use while working on a project and the kind that hold subs and functions that you often want to include in a project. If you use these conventions on the second kind of module, they can have procedure names that are the same as those in other modules.

1. Always put "Option Private Module" at the top
2. Always declare all subs and functions , Constants and Variables Private.

Personal.xlsb will always open when Excel opens, but the Workbook is hidden. If you have Worksheets that you often include in a project, you can store them in it, too. You have to copy the Cells and paste them into a sheet in the Project book, but ... OR, copy the sheets into "New Book" and use New Book as the Base of the Project.

If two workbooks are open at the same time, in the VBA Project Explorer, you can drag modules and Forms from one into the other. Drag a common use Module into a Project Workbook and edit the Private declarations so suit.

You can have several versions of the same module by following the conventions above.

Have you considered using an Enterprise wide standard of Data Field names? Check out this thread for one example of how that can help. Especially the workbook I attached for the OP and the code I asked snb to check for me.