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.

That add-in is for [tag:VBScript] not VBA. They are very different things. VBA can only be compiled and executed in it's own IDE which can only be used in Office. — SierraOscar, Oct 07 '15 at 11:33
Why do you think the VBA Editor is inefficient? It's based around the same principles of a proper IDE and allows [tag:IntelliSense] which is one of the biggest advantages of VBA. Sublime text and NotePad++ are just advanced text editors with syntax highlighting, not IDEs. — SierraOscar, Oct 07 '15 at 11:35
@MacroMan Ooo, should've caught that the plug-in is for vbscript. I guess I expected it to exist, and didn't check it thoroughly. You're positive VBA can only be compiled and executed in the native IDE? — Holene, Oct 07 '15 at 11:35
VBA is exclusive to MS Office, [Have a look at this question](http://stackoverflow.com/questions/4228061/recommended-ide-for-vba) - you can only execute VBA in the native IDE. Why don't you like the IDE though? — SierraOscar, Oct 07 '15 at 11:38
@MacroMan Well, you just pointed it out: Sublime Text and Notepad++ are (effective) advanced text editors. Sublime has stuff as Multiple Cursors and shortcuts for about anything, while my experience with the VBA IDE for now is basically "a button for everything" and limited shortcuts. And of course - *I* know Sublime, I'm not that interested in learning how to use the VBA IDE, just the language. I'm used to way more flexibility when writing. — Holene, Oct 07 '15 at 11:39
There are shortcuts for most things in the VBA IDE, just a case of learning them. I understand your point about what you're used to, but if you're serious about learning VBA then the IDE comes part and parcel. That being said, there's nothing to stop you _writing_ your code in N++ or sublime, then simply paste it into a module so that you can debug it. — SierraOscar, Oct 07 '15 at 11:42
"Most things" (i.e. https://msdn.microsoft.com/en-us/library/bb531266(v=vs.90).aspx) is a higly relative term. This is becoming a chat :p Thanks for your input. I guess my question is a massive duplicate (though it was difficult to find relevant answers with Google). Maybe VSTO is worth a try. — Holene, Oct 07 '15 at 11:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91596/discussion-between-macro-man-and-holene). — SierraOscar, Oct 07 '15 at 11:48
VBA is not exclusive to MS Office, you can find it e.g. in ArcGIS, AutoCAD, CATIA, Corel Draw, WordPerfect and others. — Daniel Dušek, Oct 07 '15 at 12:45
I am surprised at those asking what is wrong with VBA IDE!!! Just copy a VBA program to notepad with Language VBA or use VS to see the difference. The IDE was good in 1990. — NoChance, Nov 30 '16 at 10:56

score 4 · Answer 1 · edited Jun 16 '17 at 16:23

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.

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

1 Answers1