Why I use Word as a text editor for my Excel VBA code
In the last six months I have learned to code in Excel VBA because I had a number of tasks that I needed to automate. The reason for this is because the work that I had been given to do was very manually time consuming and I never would have been able to complete it in the allotted time given if I did not use Excel VBA to automate the process.
One of the files written in Excel VBA only needs to be saved with the name of the month each month, so there is no need to store the code somewhere else: the code stays in the new file and is resaved with the file every time it is saved in the current month name.
Get Crystal X’s stories in your inbox
Join Medium for free to get updates from this writer.
The other three Excel VBA files are not resaved every month. I receive a file from a third party every month and the Excel VBA files must be saved into the new Excel files that I receive every month. I had tried copying and pasting the VBA files from the previous month’s file into the current month’s file, but that did not work out very efficiently, so I needed to find another way to save the code and resave it into each month’s Excel spreadsheet that I receive from the third party.
As it happens, I had to modify the code of the VBA files that I had written in order to comply with a request made of me. Because there was so much to modify, I saved the VBA code into a Word document and began to modify that code.When I had completed the modification, I saved the code written in Word to Excel VBA. I was very pleased that the program worked with only slight modifications, so I resaved the modified code back into Word.
I had two remaining Excel VBA files to modify, but I was able to complete that task expeditiously because I had intentionally formatted the data in all three of the worksheet pages the same, so I only needed to find and replace key names in each VBA file to specifically relate to the file in question.
I have saved all three Excel VBA programs in separate Word files, and each month I intend to copy and paste the code into the new spreadsheet that I am sent from the third party.
