Is it possible to write Excel VBA Code in Visual Studio
VbaVisual StudioExcelVba Problem Overview
Is there a way to write VBA Code in Visual Studio. If not is there any other alternatives?
Vba Solutions
Solution 1 - Vba
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...
-
In the VBE go to Tools > Options > Editor tab.
Turn off 'Auto Syntax Check'. You still get code highlighted errors but no annoying popups. -
Go to the Editor Format tab and change the Font to
Consolas (Western), Size11. -
For code indenting install the awesome, free, Code Manager. It adds some sick keyboard shortcuts.
-
Make the Edit toolbar easily accessible for code commenting/uncommenting.
-
Use Rubberduck to add unit testing, source control, code inspections and refactoring functionality.
With those simple changes you end up with a half way decent, useful, and keyboard friendly environment to write your visually appealing code. :-D
Solution 2 - Vba
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.
You could also use the interop features of VBA to consume a (COM) object written in Visual Studio from your VBA code.
Solution 3 - Vba
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.
Solution 4 - Vba
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](https://marketplace.visualstudio.com/items?itemName=local-smart.excel-live-server "VSCode Marketplace")
Solution 5 - Vba
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....