Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 676
Dblock247 Avatar asked Apr 19 '14 18:04

Dblock247


People also ask

Can I use VBA in Visual Studio?

You can call VBA code from Office solutions created by using Visual Studio, and you can also call code in Office solutions created by using Visual Studio from VBA.

Is Visual Basic the same as VBA?

As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library. However, VBA code normally can only run within a host application, rather than as a standalone program. VBA can, however, control one application from another using OLE Automation.

Is Excel written in Visual Basic?

VBA is used to write programs for the Windows operating system and runs as an internal programming language in Microsoft Office (MS Office, Office) applications such as Access, Excel, PowerPoint, Publisher, Word, and Visio.

Is .NET similar to VBA?

No, the only similarity between VBA and VB.NET is the similar-looking syntax. VBA is similar to Visual Basic 6, which is obsolete since about 1997 (?), so VBA is not a very modern language. VB.NET is a modern language, having the same capabilities as C#, but the syntax is terrible, so I personally prefer C#.


2 Answers

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

like image 72
GollyJer Avatar answered Sep 19 '22 12:09

GollyJer


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.

like image 41
Ade Miller Avatar answered Sep 21 '22 12:09

Ade Miller