Using Sublime Text or Notepad++ as Editor and Builder for VBA

Question

OK, so I have to learn VBA in my new line of work as we use Excel to more or less anything. The built-in VBA editor in Excel drives me absolutely bonkers - it feels like it's not made for writing code in an efficient way.

I have been using Sublime Text for all coding purposes recent years. There's a plug-in for writing VBA vbScript in Sublime, but I don't understand how to link my Sublime written code to my Excel documents. Say I try to run the following file

Sub test()
  Debug.Print "Hello, World"
End Sub

from Sublime Text, I get the following out from the console [Finished in 0.1s], but no printout.

My question is this: does anyone have a solution for how to run VBA in Excel from Sublime Text or Notepad++? I'm still a massive noob in VBA, therefore it would be nice to be able to actually see the effects on the Excel Workbook next to testing the code.

Answer 1

The problem with VBA code and writing it in Sublime is that VBA is embedded into Excel, so you cannot just build the macros you write in Sublime

---

• One solution to this is writing your VBA code and copying-pasting into VBA Editor (mentioned above). This is not manageable when you start working with multiple modules, classes, etc.

---

• Second approach is to use Import/Export functionality of VBA. You can export your modules and classes into *.bas and *.cls files and edit them directly in Sublime Text. For that you can use macros from Ron De Bruin website. Just copy the code into seperate module and it will export all code you have in the currect project into seperate files, which you can edit in Sublime Text. After you make changes in Sublime just import all files back again into VBA editor with the macro. You might want to change the VBA code slightly as well by commenting out

 

If wkbSource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
        "not possible to export the code"
    Exit Sub
End If

... otherwise it won't import back to the same spreadsheet.