CODEWITHSUNDEEP
vbavisual-studioexcel
Dblock247
Dblock247

Reputation: 6725

Is it possible to write Excel VBA Code in Visual Studio

Is there a way to write VBA Code in Visual Studio. If not is there any other alternatives?

Upvotes: 81

Views: 154510

Answers (6)

GollyJer
GollyJer

Reputation: 26662

The best you can do is bend the office Visual Basic Editor (VBE) tool to your liking. If you stay in it's native environment you get the full power of error detection, Intellisense, live code running, etc.

My tips...

  1. In the VBE go to Tools > Options > Editor tab.
    Turn off 'Auto Syntax Check'. You still get code highlighted errors but no annoying popups.

  2. Go to the Editor Format tab and change the Font to Consolas (Western), Size 11.

  3. For code indenting install the awesome, free, Code Manager. It adds some sick keyboard shortcuts.
    enter image description here

  4. Make the Edit toolbar easily accessible for code commenting/uncommenting. enter image description here

  5. Use Rubberduck to add unit testing, source control, code inspections and refactoring functionality.

Rubberduck Menu

With those simple changes you end up with a half way decent, useful, and keyboard friendly environment to write your visually appealing code. :-D

enter image description here

Upvotes: 80

Szymon Pecuch
Szymon Pecuch

Reputation: 91

You can try the xlWings package for python and use it with VS Code https://youtu.be/xoO-Fx0fTpM

xlWings is a python module that allows to manipulate excel files from python and call python from excel. If you have it installed (see instructions here: https://docs.xlwings.org/en/latest/installation.html) it provides a basic import/export interface that works pretty well with any external editor.

After you installed the xlWings addon for Excel (no Administrator rights required), you can then call xlwings vba edit in the shell. This will export all scripts from your excel file into text files, watch for changes and import them back into excel. With vsc autosave this results in minimal delays - it's possible to have the developer tools in excel open at the same time and the updated content appears there with a delay of a second or so.

This currently hits my personal sweet spot between complexity of setup and ease of use, though I wish that it would be somewhat configurable (e.g. to specify the directory the script files get exported to).

Upvotes: 4

Gabriel
Gabriel

Reputation: 129

There is a VSCode extension to do this. It allows you to write code in VSCode and export it to Excel. This extension is very useful when you develop in VBA.

Here's the link to download the XVBA extension

Edit :

As Peter Macej said in comment, this solution only works for Visual Studio Code not for Visual Studio

Upvotes: 8

Mark Z.
Mark Z.

Reputation: 2447

You can certainly add and edit a VBA file (.vb) in your Visual Studio solution, but the intellisense will be worthless/screwed up. This extension for VScode would probably provide a much better experience: https://marketplace.visualstudio.com/items?itemName=spences10.VBA

If your goal is have your VBA code exposed to source control so you can track changes, then it's still worth it to include in your Visual Studio solution, but just store that VBA code in a plain text file and then use the Excel interop to load it into the appropriate module within the excel workbook, e.g.:

xlWorkbook.VBProject.VBComponents["ThisWorkbook"].CodeModule.AddFromFile(@"C:\PathToYour\VBAcode.txt");

And there are other methods to delete/replace code lines, etc....

Upvotes: 1

Ade Miller
Ade Miller

Reputation: 13723

VBA code for Excel can only be written inside Excel using the VBA IDE. VBA projects are stored as part of the Excel file and cannot be loaded into Visual Studio.

However, you can write VSTO (Visual Studio Tools for Office) managed add-ins for Excel using Visual Studio. The following MSDN page covers both developing with VBA and VSTO.

Excel for developers

You could also use the interop features of VBA to consume a (COM) object written in Visual Studio from your VBA code.

Upvotes: 48

nora
nora

Reputation: 161

I've been looking for an answer to this question myself.

Best I've found myself is the option of exporting a Module ect from Excel with the code you've already written (or blank) and load that up in the Visual Studio Environment.

It doesn't offer much, but the highlighted text and auto indenting is nice and makes it much easier to read compared to the standard VBA environment.

Then once you're done just import it back into Excel.

Upvotes: 6

Related Questions