hilkoc / vbadeveloper Goto Github PK
View Code? Open in Web Editor NEWTools for development and version control of vba code
License: MIT License
Tools for development and version control of vba code
License: MIT License
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!
First of all, thanks for this great add-in!
I installed the xlam in excel add-in directory and started using the automatic import/export feature;
It works well but every time I open a file it asks to import the src, even if the file is an xlsx that doesn't contain any macro. On save/close, it exports the NamedRanges.csv file.
It would be great to add a check for xlsm and xlam (and xls) extensions.
It also keep asking to import vbaDeveloper.xlam src files, but there's #41 for this already.
It would be good to import/export connection files as well.
I have added vbaDeveloper as an "add-in" in Excel. It does not import module in /src/vbaDeveloper.xlam automatically when opened or export when I click "save". I need to click "Export code for ...." manually at the ribbon button.
Am I missing some steps in config?
The purpose of the project is to help get plain text source code files into source control. Shouldn't there be plain text files in this repo? =;)-
I'm not one who typically renames the project as I'm fine just using the default name. However, I found that when I tried to use this the addin kept exporting my other addin(s). The problem is that the addins I use (including some MS ones), as well as my projects all have the same name (VBAProject).
To fix this, I will simply change my project names but if I get ambitious I may alter the code and suggest a pull request. I think this could easily be fixed by utilizing the project ID rather than the name. The ID is unique and from the ID you can get the project name as well as the workbook name etc.
There's no license specified in this project anywhere. Can I take the code under the MIT license? It'd be useful if you added a LICENSE file and/or license headers to the individual source files. Thanks!
Hi there,
Thanks for making this add-in!
In our project, we have named ranges based on formulae which contain commas. When the import procedure tries to parse NamedRanges.csv
, it gets in a pickle because of our extra commas.
I fixed this by writing and reading the NamedRanges.csv
file using semicolon as a separator instead. I can submit a pull request for this if you think it's a good step!
Thanks!
Ste
Please adjust this line in Build.bas
' 5. If using a non-English version of Excel, rename your current workbook into ThisWorkbook (in VB Editor, press F4,
' then under the local name for Microsoft Excel Objects, select the workbook. Set the property '(Name)' to ThisWorkbook)
to
' 5. If using a non-English version of Excel, rename your current workbook into ThisWorkbook (in VB Editor, **press Ctrl+R**,
' then **in the Project Explorer** under the local name for Microsoft Excel Objects, select the workbook. Set the property '(Name)' to ThisWorkbook)
Hello and thanks for creating this.
I'm having trouble figuring out how to set this up to automatically loop through our hundreds of .xlsm files and export the code from each. I know how to open files automatically using the file system object technique, but I'm hitting a wall trying to integrate that method with the vbaDeveloper code. I want to adapt the program to (one-by-one) 1) open every xlsm file in a folder 2) automatically export all of the objects and modules from each file 3) close the file without saving and 4) open the next file until all files in the folder have been processed.
Also, many of these xlsm files have Workbook Open events that I don't want to execute during the process of capturing the object and module code. I found some guidance online on how to set this up, but if you have a simple solution that is known to work with vbaDeveloper, this would be very helpful.
Thanks!
After exporting and importing named ranges they are kind of broken.
For example, the output of "?ThisWorkbook.Names("MyName").RefersTo" should be something like "='Table1'!$A$1", but somehow it tends to be "='Table1'!R1C1", which should be the output of .RefersToR1C1 .
What was originally exported does still have the right "$A$1$ format.
I'm confused on these two steps
' 8. Close excel. Open excel with a new workbook, then open the just saved vbaDeveloper.xlam
' 9. Let vbaDeveloper import its own code: Put the cursor in the function 'testImport' and press F5
I have vbaDeveloper.xlam
saved in my local cloned repository. When I double click it on my file system, nothing happens. What exactly am I supposed to open? What does it mean by "let vbaDeveloper import its own code"? What is vbaDeveloper? Thank you.
Hi
I've set installed everything correctly or it seems, as stated in the instructions contained in the build.bas
file.
Yet when start the step 10, the vbaDeveloper is expecting to import its own code. But nothing is being imported in an automatical sense...
I have to manually import the remaining .bas
files.
Is it what is expected for me to do?
I'm new to the VBS environment and any help would be great.
Thanks
Hi, thank you for this addin.
The project is not exported when saved.
While I don't particularly care whether my code is taken and transformed beyond recognition, it would have been nice to include a little comment somewhere, acknowledging the original work this code is clearly based on, which was published on Code Review Stack Exchange 2014-09-28 under a CC-BY-SA license (i.e. attribution required) and can be found here.
Cheers!
This looks like a great tool but I am unable to get it to work. I followed the instructions exactly, including setting the correct reference to "Microsoft VBA for Applications Extensibility 5.3" and the Microsoft Scripting DLL. I get an error message "Compile error: User-defined type not defined" and then the code stops and the debugger points to the first line in the first routine:
Public componentsToImport As Dictionary 'Key = componentName, Value = ComponentFilePath"
When I checked to see if the reference to "Microsoft VBA for Applications Extensibility 5.3" is set properly, the first time it was not. This extensibility DLL was not selected after reopening the Excel file after the build. So I selected it a second time, closed the workbook, and reopened the vbaDeveloper.xlam file by double-clicking on it. Then I verified whether the reference to "Microsoft VBA for Applications Extensibility 5.3" was set now and it is checked now.
After this, when I put the cursor into the testImport() procedure and hit the F5 button, I get a dialog box that allows me to select "importComponents" or "importCode" or "exportCode". When I select ImportComponents, I get the same error message as above... "Compile error: User-defined type not defined".
When I do "File.SaveAs..." on the workbook "Book1.xls" after reference has been set and the Build.bas code has been imported, it opens a dialog box pointing to the following directory
C:\Users\admin\AppData\Roaming\Microsoft\AddIns
At this point we are told to navigate to the * src/ directory and replace the filename "Book1.xls with the name "vbaDeveloper.xlam" and change the file type to "Excel Add-in (*.xlam)", and then save it.
When I do that Windows Explorer opens the pre-existing directory named ".\src\vbaDeveloper.xlam" (It opens that directory because that one is named exactly the same as the file to be saved in the .\src directory. In my case, there is an existing file "vbaDeveloper.xlam" from the previous attempt at doing the installation, so I simply replace it. I assume that this is what's supposed to happen.
I am relatively new to building other people's projects in VBA, so I don't know what is wrong. I suspect it could be an issue with the path to unzip the source code kit, because the Public variable "componentsToImport" isn't set correctly. However, I am guessing here based on the later code that references this variable. The instructions do not specify the preferred location to unzip the source code kit. Does this matter?
Is the source kit supposed to be in the root directory of the C drive or something? I have it in a Dropbox folder.
I assume we also need to finally enable the vbaDeveloper.xlam as an Excel Addin? There are no instructions to do that, but this step is obviously needed. When I do that, I don't get any menus in the Ribbon. I see a mention of menus in the code, but none appear in the Addins Ribbon.
Could someone please include some test criteria to let people know when the installation is successful?
I need help to install and use this. I am weary of copying VBA code from old workbooks, saving and versioning workbook filenames, etc. I can use the Personal.xlsb workbook approach, but real version control from within VBE would be much better. Specifically version control to create code libraries and check-in and check-out modules and functions for use in the current workbook, or at least load them as a group into new workbooks. A few more instructions how to use this tool with GitHub or SVN would be much appreciated.
On step 7 in Build.Bas it sais
' 7. In VB Editor, menu File-->Save Book1; Save as vbaDeveloper.xlam in the same directory as 'src'
But in that directory there is a FOLDER named exactly "vbaDeveloper.xlam". Trying to press the Save button will just open that folder, not save as a file with the name "vbaDeveloper.xlam".
Soo... I should delete or rename that directory first? Or just save somewhere else and then copy paste the file to the src directory after?
Hi,
I am storing Excel's with VBAs in a GH repo. Currently, whenever a change is made I use this to export the updates to the source, commit the change and push. I'd like to set this up to happen automatically in a GitHub Workflow so that each time a commit is made on a PR the workflow exports the source changes and makes a new commit to update the src
folder. I don't think that this is possible with this package, but I'm just wondering if it would even be theoretically possible to export the VBA source like this package does without having access to Excel?
Thanks!
I have been able to go trough all the steps of the installation process but it took me a few minutes to understand where I need to put my file and why the code was not running when I executed "Sub TestImport()". I have been able to figure out a few things because I have some good knowledge in VBA but for someone that is a beginner it can be painful and I am sure that a few people got discouraged. My suggestion is to create a file that will automate the installation process on Windows platform. This must be an external file because it is the only way to control others files.
I very like the idea behind this repository and I think it can make a significant impact to increase the VBA community in GitHub. @hilkoc let me know if you like the idea. I can take care if this issue and create the file for the installation.
vbaDeveloper is very useful when I'm doing development, but at some point I want to deliver the product (workbook) to a non-developer recipient without the additional baggage of the vbaDeveloper plug-in. Surely I'm not the first to want this? But in the VBE it's not possible to delete the vbaDeveloper project ("Remove" option is grayed out), nor is it possible to remove it with a VBA program or command (VBProjects collection does not have a Remove/Delete/Clear method).
Anyone that can help with this? Have you successfully removed vbaDeveloper from a workbook?
Hello @hilkoc
The format
function is not able to process the code below:
Sub SplitNotes( _
ByVal NotesStr As String, _
ByVal IDStr As String, _
ByRef Notes() As String, _
ByRef TaskLocal As TaskType)
Dim tmpArray() As Variant
Dim tmpID() As Variant
Dim tmpNote() As String
Dim a As Integer
Dim b As Integer
Dim arrayMax As Long
Dim IDMax As Long
Dim counter As Long
Dim tblNotes As ListObject
Dim clNotes As ListColumns
Dim rowNote As ListRow
Dim found As Boolean
Set tblNotes = Sheets("InternalConfig").ListObjects("TblNotes")
Set clNotes = tblNotes.ListColumns
tmpArray = SplitBase1(NotesStr, "]")
tmpID = SplitBase1(IDStr, Chr(10))
If IsArrayAllocated(tmpArray) Then
arrayMax = UBound(tmpArray)
If tmpArray(arrayMax) = "" Then arrayMax = arrayMax - 1
Else
arrayMax = 0
End If
If IsArrayAllocated(tmpID) Then
IDMax = UBound(tmpID)
Else
IDMax = 0
End If
If IDMax < arrayMax Then
ReDim Preserve tmpID(1 To arrayMax)
tmpID(arrayMax) = ""
End If
ReDim Notes(1 To 3, 1 To (arrayMax + tblNotes.ListRows.Count))
'add note to TODOist and/or properly format
For a = 1 To arrayMax
tmpNote = FormatNote(tmpArray(a), tmpID(a), TaskLocal)
Notes(1, a) = tmpNote(1) 'id
Notes(2, a) = tmpNote(2) 'date
Notes(3, a) = tmpNote(3) 'note
Next
'include notes in local table not yet in the array
counter = arrayMax
For Each rowNote In tblNotes.ListRows
'check if note belongs to task
If rowNote.Range(clNotes.Item("item_id").Index) = TaskLocal.ID Then
found = False
'check initial range if note was not already included
a = 1
Do While a <= arrayMax
If rowNote.Range(clNotes.Item("id").Index) = Notes(1, a) Then
found = True
'check if note was deleted in TODOist
If rowNote.Range(clNotes.Item("is_deleted").Index) = "1" Or _
rowNote.Range(clNotes.Item("is_archived").Index) = "1" Then
'delete
If a < arrayMax Then 'not in the end of the array
For b = a + 1 To arrayMax
Notes(1, b - 1) = Notes(1, b)
Notes(2, b - 1) = Notes(2, b)
Notes(3, b - 1) = Notes(3, b)
Next
End If
arrayMax = arrayMax - 1
counter = counter - 1
End If
Exit Do
End If
a = a + 1
Loop
'if not found and not deleted
If Not found And _
rowNote.Range(clNotes.Item("is_deleted").Index) = "0" And _
rowNote.Range(clNotes.Item("is_archived").Index) = "0" Then
'add
counter = counter + 1
Notes(1, counter) = rowNote.Range(clNotes.Item("id").Index)
Notes(2, counter) = Format(DateConv(rowNote.Range(clNotes.Item("posted").Index)), "yyyy-mm-dd")
Notes(3, counter) = rowNote.Range(clNotes.Item("content").Index)
End If
End If
Next
If counter > 0 Then
If counter <> UBound(Notes, 2) Then ReDim Preserve Notes(1 To 3, 1 To counter)
Else
ReDim Notes(0, 0)
End If
End Sub
From what I see, it's trying to align the "Exit Do" at the same indent as the "Do While", making the "Loop" to have the indent reduced. It is throwing the error below:
Error while formatting Module1
9 Subscript out of range
on line 105: End Sub
indentLevel: -1 , levelChange: -1
Best regards,
Mauricio
In the export process, Forms are exported as binary files (.frx). Since vbaDeveloper re-exports and overwrites and because of something in the file creation process(maybe a date stamp or some similar tag??), when putting the exported VBA form into version control it appears to have changed in every commit, regardless of whether the user actually made changes. This can lead to confusion in version control.
I see 3 choices:
Ignore, and adapt my Git workflow (i.e. just don't commit the unchanged files). This is slightly more manual, and also a slight training burden for my not-very-git-savy teammates.
Add a function to the vbaDeveloper to prompt whether to export forms and any other offending file types. Don't prompt if no offending file types would be exported.
Hunt down what the reason for the differing binary files and see if it is possible to exclude that component from the export.
Anyone else have opinions, or have a need for option 2 or 3 if I were to develop it? Anyone (particularly the project owner) opposed to option 2?
Is there a way to import a file that is located in other path which is not the "src" folder when using testImport()? It would be very useful to edit VBA files remotely
Hello,
I'm not sure, if I installed the tool wrong (or use it wrong), but whenever I open a workbook where I added vbaDeveloper (In VB Editor -> Tools -> References -> Browse and select vbaDevelo
it asks to "Import the code for vbaDeveloper.xlam now?". Why is that? Shouldn't it only ask to import my module?
The problem of Excel wrongfully importing classes as modules was investigated by Tim Hall and the origin was identified as being related to UNIX-style line endings (LF) instead of Windows-style line endings (CR/LF).
VBA-tools/VBA-Web#24
git config has to be configured appropriately so that pulls do generate correct local line endings before importing in Excel.
We have a shared email, we are under using follow up flag function to sort email,how can I use a macro to automatic mark email on follow up flag column with some logic。
code like
Do Until (rs.State = adStateOpen): Set rs = rs.NextRecordset: Loop
If (rs.Fields.count < 1 Or rs.Fields(0).Name <> "VERSION") Then
Err.Raise Number:=vbObjectError + 500, Description:="Cannot read server side API version number"
End If
is formatted to
Do Until (rs.State = adStateOpen): Set rs = rs.NextRecordset: Loop
If (rs.Fields.count < 1 Or rs.Fields(0).Name <> "VERSION") Then
Err.Raise Number:=vbObjectError + 500, Description:="Cannot read server side API version number"
End If
As a solution I propose the patch #44. It introduces a constant SIMPLIFIED_FORMAT, initialized with False by default. If it is set to True, the code formatting will be reduced just to trimming trailing spaces of each code line.
This tool sounds very promising, but its installation has been a nightmare so far. I am using Excel 2013 and if I try to save the file with the name vbaDeveloper.xlam leaving the Sav as type as it is, I get the "To continue saving as macro-free workbook, click Yes", If I click Yes then the file can not be opened. If I click No then I am stuck. What is the solution?
Do
findIncidenciaParteRg.EntireRow.Delete
Set findIncidenciaParteRg = wsIncidenciasParte.Range(RgIncidenciasParteAllIdparte).find(wsPartes.Cells(currRow, ColPartesIdparte), LookIn:=xlValues, LookAt:=xlWhole)
Loop While Not findIncidenciaParteRg Is Nothing
produces
Do
findIncidenciaParteRg.EntireRow.Delete
Set findIncidenciaParteRg = wsIncidenciasParte.Range(RgIncidenciasParteAllIdparte).find(wsPartes.Cells(currRow, ColPartesIdparte), LookIn:=xlValues, LookAt:=xlWhole)
Loop While Not findIncidenciaParteRg Is Nothing
so the end Loop
is in wrong identation.
Also
query = "SELECT p.*, i.idincidencia, i.valor, m.descripcion, m.tipovalor FROM partes p" & _
" LEFT JOIN incidencias i ON i.idparte = p.idparte" & _
" LEFT JOIN masterincidencias m ON m.idincidencia = i.idincidencia"
produces
query = "SELECT p.*, i.idincidencia, i.valor, m.descripcion, m.tipovalor FROM partes p" & _
" LEFT JOIN incidencias i ON i.idparte = p.idparte" & _
" LEFT JOIN masterincidencias m ON m.idincidencia = i.idincidencia"
I'm having trouble getting the menus to load. I've confirmed that the procedure is running, but the menus are not showing up in the Ribbon.
I've looked all over in the Ribbon customization settings and can't find them there to turn on.
I looked at the Application.CommandBars(1).Controls and I can see VbaDeveloper as one of the controls, but they are not visible.
Any help is appreciated.
I have tried to follow the installation instructions (twice), but when I come to step 9 (running testImport), the routine eventually crashes with error message "Compile error: Expected: end of statement", pointing to the first line of "Custom Actions".
It looks like Excel is trying to interpret CustomActions as a VBA module and therefore does not understand the first line, "VERSION 1.0 CLASS"..
I am having some troubles with special characters (Unicode).
When I make the 'ImportCodeFor' call in in the add-in menu a .bas file with this line:
strPattern = "Dr. ès lettres"
becomes
strPattern =" Dr. ès lettres"
The general windows settings for 'Language for non-Unicode programs' is set to German, and should allow for such characters. A copy paste via notepad++ into the code window also properly copies to character. I suppose somewhere in the import process the encoding of code content might need to be specified? So far I had no luck figuring out where myself.
When trying to export the code this error pops up, saying that the method "export" of object "_VBComponent" failed.
Is this a problem of environment, app, excel,my application,...?
I am running Excel 16 with Windows 7 Enterprise
Hello,
Nice work with the tool, it's really helping me.
I was wondering how (if) you deal with having more than one Excel binary (.xlsm) linked to the same src files? I'll explain my case.
I have a couple of files that will be using the same libraries. I think of put them on the same root folder, and when I export/import the code, they would both point to the same subdirectory (that would be ./src/
instead of ./src/filename.xlsm/
)
Thanks,
Mauricio
I tried building the addin, but when I get to step 7 ("In VB Editor, menu File-->Save Book1; Save as vbaDeveloper.xlam in the same directory as 'src'"), Excel insists on saving the .xlam file to its own AddIns directory (C:\Users\($username)\AppData\Roaming\Microsoft\AddIns
), even though I select the parent directory of "src" as the save location.
If I then try to run testImport()
for the installed addin, I get the error:
Folder does not exist: C:\Users\($username)\AppData\Roaming\Microsoft\AddIns\src\vbaDeveloper.xlam\
No import directory for project vbaDeveloper, skipping
Has anybody else run into this problem? Excel 2010, Windows 7.
Formatter module contains public sub format()
.
Excel has a built-in function called Format
.
if I add a reference to your project (so I can call exportVbProject
) I my calls to the latter are mistaken for calls to the former which causes errors.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.