spences10 / vba-ide-code-export Goto Github PK

It may be useful to store Excel workbooks in their unpacked form which is a directory of XML files. This has been done before in VBADeveloper and OoXmlUnpack. From observation of those projects, it seems that this may lead to more efficient storage in VCS and might help with diffing different versions of workbooks.

OoXmlUnpack has achieved this very well. It not only decompresses the workbook zip file, but it also "pretty prints" the XML for better VCS support and it deletes the CalcChain.xml file which isn't necessary and only makes diffs ugly. It may be best to use OoXmlUnpack in the background to achieve this.

VBADeveloper is a good example of how the UX of this Pack / Unpack feature can be so painful that the benefit is not worth it. The UX should be very easy. Perhaps the command line interface proposed in issue #56 can help with this.

Finally, I think that some users may not want to use this feature so it should be able to be disabled.

I am finding that a new line is added after the Attribute header each time I do a Import then Export cycle. I view the .frm file in a text editor and watch the file grow as I import then export over and over. Does anyone else suffer from this?

If you specify a path in the configuration file such as:

"MyModule": "src\\Awesome\\MyModule.bas"

Then the directory src and src\Awesome must exist otherwise an unhandled error occurs.

I believe a problem exists with the VBAProject name which is similar to the problem with references mentioned in issue #7.

I propose that there be a property in the configuration which specifies the VBAProject name and then on import, that name will be set. I don't think it is necessary to unset the VBAProject name on export.

A possible name for this configuration property: "VBAProject Name".

If the majority of the code for this was in a src folder then the README would get more visibility.

@mattpalermo have you thought any more about how to build the add-in?

A ribbon UI which can be used to invoke the actions of CodeExport (import, export, etc) would be very helpful to the UX. Opening the VBE to get at the CodeExport menu is sometimes just too much work. Plus the Ribbon UI is quite stable, pretty, easy to find and easy to use.

To implement this, we could just use one of the tools already available to inject CustomUI.xml into an Excel workbook. Then we would have to save the workbook and commit it to the repo every time the CustomUI.xml was changed. Additionally, the CustomUI.xml couldn't be treated as a text file since it is locked inside the Excel workbook. This may be perfectly OK in the short term, but in the long term it may be ideal to implement a CustomUI.xml injection feature into CodeExport and use it to handle its own CustomUI.xml injection.

Using an error handler such as the one used in the Import method is great for user experience. However it can be very painful for development since you cannot jump to the point in execution where the error was raised with the debugger. There is an elegant way to provide this convenience to the developer while still having a nice friendly error message to the user.

A solution is to put a small bit of logic in the error handler which goes a little something like this:

Sub DoSomething()
    On Error Goto ErrHandler

    ' Actual business logic happens here

ExitProc:
    ' Any clean up code
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case SomeErrorICanHandle
            ' Handle this error in the best way
        Case Else
            ' For all unhandled errors, crash gracefully.
            UserDecision = ShowCrashPrompt("some message")
            If UserDecision = decision.debug then
                Stop ' This starts the debugger
                Resume ' This causes execution to go back to the line at which the error was raised
            Else
                ' Crash gracefully
            End If
    End Select
End Sub

The ShowCrashPrompt doesn't have to be anything fancy. Might be able to get away with a built in dialog type. Just as long as we can get that option of debugging.

If you want the application to have a beautiful dialog then you can create a custom userform. I have done this for a proprietary project before and it turned out quite beautiful. In that example I even provide a diagnostic report that can be sent to a maintainer :)

Make Excel out of XML

Considerations:

• What type of Excel is is being made into

A prebuilt add-in or xlsm would really help for those too lazy to build the add-in 😊 .
I think it would be most appropriate to use the Releases feature of github to distribute this.

Project files may modify the user interface ribbon by having a CustomUI.xml inside the project file (e.g. in the Excel add-in xlam). This CustomUI.xml cannot be edited inside Excel (as far as i know). Instead an xml file is usually injected into the Excel file. The CustomUI.xml can be somewhat generated in Excel by modifying the ribbon manually and then exporting the changes but it is not uncommon to create or modify it using a text editor. Therefore it would be useful to make importing the CustomUI.xml as easy as possible for the same reasons that the VBA source code needs to be easy.

From this article, It appears it is possible to inject this CustomUI.xml into the Excel file using VBA. I propose that the add-in should be able to import a CustomUI.xml when the Import button is pressed. I also propose that the CustomUI be unset when the Export button is pressed to prevent the Excel file from becoming out of sync with the CustomUI.xml file.

I have recently been using this guide lately to keep a clear record of changes: http://keepachangelog.com/en/0.3.0/

It seems to be fairly easy to do. What do you think about it @spences10? Would make for a nice professional touch.

The only non-trivial thing about it would be the release date. I suppose we can just agree to use AEST... I joke, UTC time.

Hi all,

I'm a new user of this add-in but I keep getting this error for both import and export:

"No" will close the windows and "Yes" will make it show up again!

The fact is that both functionalities do work, and since I'm new to the tool, I can't know which part is broken/missing due to this error.

Please advise :-)

Edit:
One detail to add is that the installation info is not accurate, there's no exe in the realease page and thus I just placed the xlam in the MS addin directory manually.

Edit.2:
Adding the following references seem to have solved the issue (even though 1 at least is missing from my excel 2016):

• Microsoft Scripting Runtime
• Microsoft Visual Basic for Applications Extensibility 5.3
• Windows Script Host Object Model
• Microsoft Shell Controls And Automation

There are a few different "whitespace errors" that are introduced to the VBA when the code is imported (and exported?). Issues #22 and #45 are two of these whitespace errors.

I have also recently experienced a whitespace error where a trailing space will be added to line 2 of a form module. This is the line that looks like this:

Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} MyFormName

To solve these problems, the code should be run through a "formatter" on import (and on export?). It is important that this formatter is non-destructive. I think it would be best to only do enough manipulation to fix the whitespace errors and not get carried away with an other formatting.

Nice work on the README @spences10. It is easy to read and the instructions are very specific. Loving the table of contents as well! I have a few thoughts about further improvements that could be made:

• Move the Usage section above the Build section. Hopefully most users won't need to build the add-in, but will instead just install and go!

• GIFs for the Usage section? I'm not sure if it would be possible to explain it all using a short, audioless gif. Perhaps a YouTube video might be the next step up.

• Update Usage section to mention the new Ribbon UI

• The manual instructions in the "Add the code" section doesn't instruct the builder to set the VBAProject name. In the CodeExport.config.json file, the VBAProject name is declared as "VBAProject Name": "CodeExport".

• Add Instructions for how to use VBA-IDE-Code-Export to build itself. The instructions in the "Add the code" section describes how to add the code manually. The explanation makes it very clear how to achieve this, but why build it manually when we have created a tool to automatically build things like this? What about, in addition to the manual instructions, also describing the method where you can just use VBA-IDE-Code-Export to build itself automatically?

If an unhandled error occurs half way through exporting, then the user can be left in a situation that could result in data loss if they're not careful. The problem is that only some of the modules have been exported to the file system and have been deleted from the VBAProject. This leaves the user in a situation where they do not have a complete copy of their work in a single location. The export action will not work with only some of the modules in the VBAProject, it will raise an error. And even worse, if they use the import action, they could potentially overwrite work done to the modules which didn't get exported. Currently the best way to recover from this is to manually either export the remaining modules or import the missing modules.

Clearly it would be ideal if no unhandled errors occurs, however that is not possible to attain since some things are out of our control. An extreme example would be an immediate program interruption (think power outage).

So instead, I propose that the export action be done in two steps. The first writes the files to the file system, and when that is successful, then delete the modules from the VBAProject.

An alternative (or compliment) to this would be to allow the export action to be successful with only some of the modules present. Perhaps a warning can be shown in this situation.

So, we can use a wiki for when there's a bit more documentation than what should go in the README?

What do you think @mattpalermo

https://github.com/spences10/VBA-IDE-Code-Export/wiki

An unhandled error occurs in Export() if the properties "References" or "Module Paths" is not set in the Export() sub. I believe the correct way to deal with this is just like in the Import() sub.

It would be nice to make up a few "example/test" projects (they can be stored in a subdirectory of this repo). Each mini project can have a description instructing how CodeExport is used in that situation. This would serve two purposes:

1. It will serve as an example of the features of CodeExport and how to use it.
2. It will serve as documented test cases. At the moment when I want to test a build, the only projects I can test it on is CodeExport itself and a single closed source project I use it on. CodeExport in particular doesn't use all the features of itself so it is not a comprehensive test.

Some example/test projects may be constructed to show things that shouldn't work with CodeExport. A simple, clear warning in the test description will hopefully be enough to make sure that readers don't use those cases as examples of how to use CodeExport in their own project.

@mattpalermo I'm not sure if this is taken care of anywhere already but

Here's my experience, have the source code somewhere on your machine, go to build the add-in, so for me I know how to build the Excel .xlsm from the source then add the modules all groovy, then I compile...

I know that there are some refs need adding and I could just go through and hit compile and add in the references on what error messages I get back

Or I have to go back to the documentation and find the list

What I'm thinking is that the list is added to the first module the user will see,

So maybe add in some comments:

Add in the following references:

1. Microsoft Scripting Runtime
2. Microsoft Visual Basic for Applications Extensibility 5.3
3. Windows Script Host Object Model
4. Microsoft Shell Controls And Automation

Now that I've just added them to the config .cls I may just leave them in there

Let me know what you think

I am trying to follow the instructions from the readme, but fail at step 1;

1. [Download](https://github.com/spences10/VBA-IDE-Code-Export/releases) the
   add-in installer (e.g. `CodeExport_setup_1.2.3.exe`)

There is no *.exe by any name in the release area or the archives therein.

The ImportFrom and ExportTo configuration uses absolute file paths. This makes the configuration not portable. The problem is that if I have a project directory with an Excel file and VBA source files which are imported to and exported from the Excel file then I cannot move the project directory without breaking the import/export configuration. I would like to keep the configuration with the project directory because this is a part of the source code - it configures the mapping between VBA files and modules in the Excel file.

When someone clones the project directory, I would like them to be able to import all the files specified in the configuration provided.

An error occurs if I have a userform and I run import twice. Error number 60061.

The CustomUI.xml is another component of the workbook that is very well suited to being stored as a text file instead of being bundled into the workbook binary. To achieve this I can think of two different solutions:

1. Use a tool like EffOff in the build process to inject the CustomUI.xml every time the workbook is built.
2. Unpack the workbook into a decompressed stack of XML files. The CustomUI.xml will be in there somewhere. Then as part of the build process pack the workbook, and then when it comes to committing changes, unpack the workbook.

This feature can be used to implement issue #54.

Discovered this trying to use the Excel to XML button on the on an old Excel 2003 workbook.

There's no check on the file type/extension to determine if the add-in can turn the workbook from the BLOB to the zip file.

and that error is not handled. Instead the crash prompt is invoked. The user should be prompted that there was a syntax error and they need to resolve this by editing the configuration file.

These files can be found in the CodeExport project directory. The .gitattributes file is important for fixing bugs which arise in Excel due to using Unix style line terminations. The other files help in other less serious ways.

The Exit Sub statement has been used outside of the "Finally" block (In this case it is labelled "exitSub") in at least one place in the code. The Exit Sub statement should be replaced by Goto exitSub. The code should be searched through for all uses of Exit and fix this.

The problem with using Exit Sub or Exit Function instead of Goto exitSub is that if in the future someone puts more logic into the exitSub block, they will expect that this will always be called when the procedure exits normally. At the moment there is no logic, so there is currently no functional difference.

The current method for building the files list is Configure Export then Make File List. The configure export asks the user questions about how the file list should be created, and then stores it in state which is used in any following calls to the Make File List action, no matter which project the File List is for. If the user calls the Make File List action without calling the Configure Export action first, it will use the configuration from the last time the Add-in was used.

To illustrate this problem, if you checkout commit 521e6e9 (the latest commit on master), and then use the Add-in straight away, the ImportFrom and ExportTo fields are populated with the values "C:\Users\spenc\gitrepos\MHC Insight". I am assuming this a directory in @spences10's computer.

Here is some ideas for how to solve the problem:

Have the user prompted for configuration when Make File List is called. Then no state has to be stored in the Excel file at all. It will also make the user's life easier.

For the configuration option currently labelled "List components in 'CondeExportFileList.conf file", the Add-In still has to figure out where the configuration was stored, in a module or in a file. To solve this, there is a few reasonable options I can think of:

• Just search for the module 'modFileList', if that's not found then search for the file 'CodeExportFileList.conf'. (Or the other way around).
• Have the Add-In only use one or the other.

Here is an extreme idea: Don't have any configuration.

I don't see a use for the ImportFrom and ExportTo configuration values. Typically, development of a bunch of interdependent files (like VBA files + Excel file) happens in a project folder where the relative location of these files in the project directory stay the same. However you cannot assume anything about the absolute path of the project folder and therefore relative file paths are most suitable. If we take the KISS approach, we can assume that all the VBA files are in the same directory as the Excel file and then we just need a list of files. See issue #4.

As for the configuration of whether or not to use CodeExportFileList.conf. Always use the config file. A good default is better than configuration. The CodeExportFileList.conf has the advantage over the module of being plain text. If I browse a project directory I can quickly inspect CodeExportFileList.conf with any tool I want (e.g. on github).

When Make Config File is run, the VBAProject Name property of the config file should be set to the current project name in the workbook.

An automated installer for the add-in would really help those who are too lazy to install their add-in 😊 .
Issue #27 may be a good first step before achieving this.

From my experience with setting up an installer for EffOff it can be a bit of a headache. The Inno installer or the WiX installer may provide good solutions for this. The NSIS installer scripts seem very verbose, but it seems to be popular on alternativeTo.net.

Or is there a better way to distribute excel add-ins?

The new empty line is added to the end of the module regardless of if there is already an empty line at the end of the file.

This seems similar to issue #22 but should be a lot simpler to fix.

PS script does not get used in the latest set if enhancements as the .xlam blob is now saved to github

Need to document the exact steps for a release so that releases are repeatable.

For the last release I did the following

• Build the Add-In workbook and Add-In
• Created a release through the Releases interface of GitHub. The version number follows semantic versioning in the format "v1.2.3" and the release title follows the format "version 1.2.3".
• Uploaded the Add-In workbook and Add-In as downloads for the release.

There isn't any version numbers in the source code to change... I don't think.

When I generate a configuration I tend to delete the entries for the sheets which don't have any code in them. I think this is a good practice as it makes it easier to find code when it is exported since you don't have to go through lots of empty files. At the moment I am dealing with a project with a lot of sheets with no code in them.

So I have a feature request:

When the configuration is generated, can the empty sheets not be put into the configuration file? Would this be disruptive to anyone using this?

A command line interface for CodeExport could be useful to build and open a workbook straight from the command line with a few keystrokes. For example, in your working directory, you may be able to type a command:

xlbuild MyWorkbook.xlsm

which would open the workbook and run the CodeExport import command. This would allows for a much faster workflow, especially if there is actions that need to be taken before opening and after closing MyWorkbook.xlsm. This will be crucial for implementing issue #55 and the OoXmlUnpack functionality since these functions will (may) operate on the MyWorkbook.xlsm before and after Excel has opened it.

I am not sure that the alert that appears after the import and export is successful is a good user experience. It is a very annoying experience to have to move your mouse point half way across the screen every time to dismiss the dialog. Although, I understand that confirmation feedback is a good thing for user experience.

Any thoughts?

Need to add instructions to the Installation part of the README to enable VBA project model trust as described in this comment in the source code:

'// Also check the 'Trust access to the VBA project model check box', located...
'// Trust Centre, Trust Centre Settings, Macro Settings, Trust access to the VBA project model

Originally, the configuration contained two settings named "ImportFrom" and "ExportTo". These were removed when I fixed the problem of the configuration being not portable as described in #4. The objective of these settings was to allow importing and exporting from a directory other than the same directory as the Excel file. However the paths were given as absolute paths and this caused problems for many use cases such as storing the configuration file in VCS. As of #10 individual file paths may be specified either relative to the Excel file directory or via an absolute path. This, in theory, achieves the same thing as the "ImportFrom" and "ExportTo" settings intended to. However if all the files are stored in a common directory, it would be nice not to have to re-write the path to that directory for every single file path.

I was thinking that a setting such as "BasePath" may be useful in those situations where "BasePath" may be either relative or absolute.

To implement this, a good place to start may be at the EvaluatePath function in modImportExport.bas.

I have found that library references don't persist when all the VBA code is removed. This could be annoying. Perhaps this tool can solve this problem by having a list of library references in the config file and then automatically inserting them when the code is imported. I would like to know what everyone thinks about this.

Great work ok the shields @spences10. They look good, but it seems there is a couple of problems to be fixed.

The numbers shown are not the correct numbers. Maybe there is permissions that need to be given shields.io for this to work? Also the 'releases' shield link should point to https://github.com/spences10/VBA-IDE-Code-Export/releases instead of https://github.com/spences10/VBA-IDE-Code-Export

The current repo description is: "Export VBA code from the VBA IDE using the VBComponent class".

I think this should be updated to better summarise the utility of the tool. I think the mention of the VBComponent class is not of immediate interest to a user glancing at the repo trying to determine what the utility can do for them.

Perhaps something like: "Export & Import VBA code. Use Git (or any VCS) for VBA programming."
I'm not 100% sure.

Hi @spences10 ,

I'm trying to experiment with code export functionality and I'm facing some strange behavior. I've created a test macro with simple user form. I've generated a config file and exported the code. As it seems the code is intact, however when I check my Excel file afterwards, all macro components (i.e. form, module) were deleted. Is that intended behavior or I missed something?