Closed jbridge8133 closed 8 years ago
jbridge8133
commented
8 years ago I found that I could force in the new macro content using this method: Open the rebuilt xlsm file in VBA developer. Remove the current Module1, use import->Module and select the correct Module1.bas file from my hard drive. Then the new macro information is imported. This is sadly manual but is better than nothing.
NealHumphrey
commented
8 years ago @jbridge8133 I'm not completely clear on what problem you're facing. Can you be more specific about where you're editing things and what isn't working? For instance, when you say "I created two conflicting changes....".
here are some observiatoins that might help you The Save function copies all VBA in the .xlsm file into text files. Merging in Git forces you to choose between two .xlsm files - so if you've separately merged the text conflicts, the .xlsm file will not be correct. If you save the .xlsm file before you click rebuild, you'll overwrite your changes.
The situation you describe is the purpose this add-in was created so you shouldn't have problems...
jbridge8133
commented
8 years ago @NealHumphrey - Thank you so much for responding!
To clarify, ideally I do not want the xlsm (or xls) files in source code control at all. I just want the exported files in source code control (bas, frm, frx, xml, rels, bin).
I built the vbaDeveloper add-in just fine (thanks to the instructions).
Then I start with an xlsm from the developers. It contains many macros. So I use the vbaDeveloper Add-Ins to do two steps while I have the xlsm file open in Excel:
Both of these exports together in one dir allows me to discard my original xlsm file, because the VBADeveloper tool can create a new xlsm from the exported dir.
To create the new xlsm file, I open a new excel sheet and recreate the xlsm file using the directory of exported files and the Add-In tool:
Until when we start changing the Module.bas files (macros) in text editors separately from the VBA utility. Once the macro in the Module.bas file in the src directory is modified (using vim, Notepad, or merge comparison tool), when we Rebuild the xlsm file from source it does not pick up the new macro content. Opening the rebuilt xlsm file and editing the macro in VBA shows old Module.bas content, although the Module.bas file on disk shows the new content. I don't know where the macro in Excel/VBA is coming from - is it being saved somewhere? (vbaProject.bin from exported files perhaps?) Somehow the new Module*.bas content from the disk is not being "rebuilt"/imported. Is this an error in how "Rebuild a file" works? Maybe there's something I'm not understanding, or something in Excel that can't be worked around.
So far this vbaDeveloper tool is still helpful to us. We can do the process described above, and then perform the manual workaround inside the VBA editor where we remove the old Modules and do a File->Import of the new Module*.bas from disk, and then it shows up correctly inside the VBA editor and we save. The problem with this is that our xlsm files have many modules. So manual is kind of a pain. Worst case, perhaps we could create a tool to automate this removal and reimport of macros.
It seems to me that the Rebuild action from this tool is not correctly bringing in changed files on disk. Have I misunderstood how to use the tool?
Thanks for your help! I'm glad to know this is working for other people, as I don't have much experience in this area.
NealHumphrey
commented
8 years ago @jbridge8133 Ah, ok. Now your question makes more sense.
Currently, the rebuild function only deals with the VBA. It does not re-import the XML. See below for the expected workflow with the current tool.
Background I don't recommend rebuilding the .xlsm file from the exported XML data if you have merged changes to the XML via Git. In theory it should be possible - but it has not been thoroughly tested, and the XML is much more complicated since it is machine generated (i.e. by Excel) instead of written directly by a developer. Even a small change to a spreadsheet made directly in Excel can sometimes have big implications to what's stored in the XML.
Also, to understand how it works better - if you take an example.xlsx or example.xlsm file and rename it to example.zip, you can then unzip the folder and browse the XML structure.
Expected workflow When I use the XML export function, I commit the binary .xlsm file (I know, not good code practice - but developing software in Excel has never been good practice), and just use the XML export to visualize the difference between two files.
In this workflow, if you are merging changes to the spreadsheet themselves, you'll need to pick which version is the 'winner' in the merge, and then manually add the changes to the other branch. Overall this add-in allows parallel development of VBA, but I do not recommend parallel changes to the spreadsheet.
Rebuilding XML If you want to leave the binary .xlsm files out of your commits and instead rebuild them from XML, you'll need to add another subroutine to do this and then call it from the 'Rebuild' function. As noted in the 'Background' section (and as you'll see if you look at the code for exporting XML) this is pretty straightforward - just zip up the whole XML folder and rename the file to .xlsm.
If you decide to go this route, I'd suggest making your own branch, and then really make sure you test extensively. In theory you should be able to do this just fine - but if you have complex spreadsheets I'd be pretty worried about a mishandled merge conflict that accidentally corrupts the file and is really hard to undo. Worth keeping some copies of the binary .xlsm files from time to time as backup.
It's up to @hilkoc whether to bring your updates into the tool.
jbridge8133
commented
8 years ago Thank you so much for your help, @NealHumphrey!
I did not realize the tool wasn't really intended to Rebuild the xlsm file.
So with the tool as it is currently written, I can use it to export and expose diffs in the macros for each developer change. Git will alert us to conflicts. (We will keep those in git, and the xls/xlsm as well.) The devs can then merge any conflicts they see between bas macro files, but do that merge within the VBA code environment. Save and export, commit to git. Does that sound right?
Or perhaps enhance the tool.
I think how it works now may be enough of a start for us to make some source control and build pipeline progress!
Thank you
On Mar 7, 2016, at 5:42 PM, Neal Humphrey notifications@github.com wrote:
@jbridge8133 Ah, ok. Now your question makes more sense.
Currently, the rebuild function only deals with the VBA. It does not re-import the XML. See below for the expected workflow with the current tool.
Background I don't recommend rebuilding the .xlsm file from the exported XML data if you have merged changes to the XML via Git. In theory it should be possible - but it has not been thoroughly tested, and the XML is much more complicated since it is machine generated (i.e. by Excel) instead of written directly by a developer. Even a small change to a spreadsheet made directly in Excel can sometimes have big implications to what's stored in the XML.
Also, to understand how it works better - if you take an example.xlsx or example.xlsm file and rename it to example.zip, you can then unzip the folder and browse the XML structure.
Expected workflow When I use the XML export function, I commit the binary .xlsm file (I know, not good code practice - but developing software in Excel has never been good practice), and just use the XML export to visualize the difference between two files.
In this workflow, if you are merging changes to the spreadsheet themselves, you'll need to pick which version is the 'winner' in the merge, and then manually add the changes to the other branch. Overall this add-in allows parallel development of VBA, but I do not recommend parallel changes to the spreadsheet.
Rebuilding XML If you want to leave the binary .xlsm files out of your commits and instead rebuild them from XML, you'll need to add another subroutine to do this and then call it from the 'Rebuild' function. As noted in the 'Background' section (and as you'll see if you look at the code for exporting XML) this is pretty straightforward - just zip up the whole XML folder and rename the file to .xlsm.
If you decide to go this route, I'd suggest making your own branch, and then really make sure you test extensively. In theory you should be able to do this just fine - but if you have complex spreadsheets I'd be pretty worried about a mishandled merge conflict that accidentally corrupts the file and is really hard to undo. Worth keeping some copies of the binary .xlsm files from time to time as backup.
It's up to @hilkoc whether to bring your updates into the tool.
— Reply to this email directly or view it on GitHub.
NealHumphrey
commented
8 years ago Not 100% right. You can use git to merge changes in the VBA code - since it's human-generated code, resolving merge conflicts should be straight forward. Use the rebuild function to pull the code from your .bas files. If you merge branches, resolve .bas conflicts in Git, and then run the rebuild function you should be good to go. The Rebuild function deletes all macros and replaces them with the content of the /src file.
What you can't do is delete the original spreadsheet. the XML content is what is contained in the spreadsheets itself. The rebuild function never touches the spreadsheet.
The code isn't too hard to follow - might be worth just stepping through the export and rebuild functions to see how they work.
Neal
PetLahev
commented
8 years ago @jbridge8133 with respect to this tool, you may also want to check some other possibilities. Check features in Rubber duck - http://rubberduckvba.com/Features - Git is scheduled for version 2.0 but looks impresive
jbridge8133
commented
8 years ago @NealHumphrey Miscommunication... Thank you for continuing to write me.
This part you said above: Use the rebuild function to pull the code from your .bas files. If you merge branches, resolve .bas conflicts in Git, and then run the rebuild function you should be good to go. The Rebuild function deletes all macros and replaces them with the content of the /src file.
This is exactly how I was trying to use the tool. The problem I have is that the Rebuild function does NOT delete all macros and replace them with the content of the Module1.bas file. The old macro persists and the new content from Module1.bas is not brought in until I explicity Import the File from within VBA.
I would be very happy to get the tool to work as you described, so hopefully I am just missing something, perhaps in the two add-ins I am running too many Export, and later possibly both Rebuild and Import is messing me up. I can try this different ways later today, but if you have more specifics on which Add-In menu item to use at each step that may solve my problem?
Thank you
jbridge8133
commented
8 years ago @PetLahev Thank you for pointing out another option - I will check it out!
jbridge8133
commented
8 years ago @NealHumphrey What I realized is that if I use "Rebuild" then I can not import the macros using "VbaDeveloper -> Import code for..." after it. That is why the macro changes weren't being brought in. If I give up on the Rebuild step and only do the Add-In VbaDeveloper -> Import code for... it works just fine. Then the xlsm contains the new macro information in Module1.bas even when modified outside of the VBA environment.
This is great for our project and I will try this, keeping both the xlsm and the Exported code in git, and having devs follow this workflow (skipping git branching steps):
I think another thread here mentioned how to make the import and export automatic. That would be a nice feature to minimize manual steps (and potential errors)!
And it might be nice if I removed the Excel Add-In "XML Import-Export" since I will only be having the developers use the "VbaDeveloper" Add-In.
Thanks again, Jenny
NealHumphrey
commented
8 years ago @jbridge8133 thanks for the update. The updates you mention should be relatively easy to make. I believe that there is a toggle in the code (true/false) for the auto-export function - again, sorry it's been a while since I've dug into the source for this.
jbridge8133
commented
8 years ago I'm new to GitHub standard practices. How do I integrate this into my team's source code control? Should I fork this code into my personal account and then clone it to a local network Stash repo location? Or I could leave a GitHub URL in this team's network Stash repo location in case they ever want to change the code further? Thanks for sharing your experience.
NealHumphrey
commented
8 years ago Yes, fork a copy and clone from there. If you have a stable, reusalbe way to implement any upgrades @hilkoc might take a pull request to bring those back in - but not sure if he/she is actively developing this project these days.
Neal
Neal Humphrey Founder, Flashband | www.flashband.org | 919-449-6879 Are you on the Flashband mailing list http://eepurl.com/mslV5 yet?
https://flashband.org/events https://flashband.org/events https://flashband.org/events
On Mon, Mar 14, 2016 at 4:47 PM, jbridge8133 notifications@github.com wrote:
I'm new to GitHub standard practices. How do I integrate this into my team's source code control. Should I fork this code into my personal account and then clone it to a local network Stash repo location? Or I could leave a URL in this team's network Stash repo location in case they ever want to change the code further? Thanks for sharing your experience.
— Reply to this email directly or view it on GitHub https://github.com/hilkoc/vbaDeveloper/issues/18#issuecomment-196515067.
I am using vbaDeveloper to enable source code control of binary xlsm files. We could like to be able to see diffs of dev changes to the current version, to enable merging. So not only would I like as much of the project exported into text source code as possible, but I'd like to be able to use git to resolve merge conflicts in these text files. (I understand we'll still have to resolve merge conflicts in binaries such as .bin or .frx by choosing one or the other which is not ideal, but acceptable to us as we mostly make macro changes.)
Specifically I created two conflicting changes to Module1.bas macro, and for the second commit I merged the contents using an editor outside VBA. However I do not find that the "Rebuild a file" add-in menu item actually pulls the new Module1.bas file from the disk into the created xlsm file. Somehow in the rebuilt spreadsheet (and Module1.bas as seen in VBA) the values from the previous macro persist, although I can look in Module1.bas on my disk (cat or Notepad) and the contents there are correct.
I would love some help on this problem as currently I have a team of developers not using any source code control at all.
I may be causing this problem myself somehow as I am not an Excel/VBA developer. (My area is build and release pipeline automation.) I am resolving the macro conflict using editors outside of VBA, and suspect this to be the cause of the problem. Perhaps after resolving the conflict externally, I need to cut-and-paste it into the Module1.bas file using the VBA editor, and save from there? I don't understand how the old macro contents are persisting - where is it being kept?
Any advice would be greatly appreciated!