XVBA - Supercharge Excel VBA Development

XVBA is a toolkit for boosting productivity and enhancing the coding experience when developing VBA applications in Excel.

XVBA Resume

Introduction

XVBA (Excel VBA) is a comprehensive development toolkit designed to supercharge VBA (Visual Basic for Applications) development in Microsoft Excel and Access. It provides an enhanced coding experience, boosts productivity, and offers advanced features for VBA programmers.

?? Key Features

• Edit VBA in VSCode: Take advantage of the powerful Visual Studio Code editor to write and edit VBA code seamlessly. Benefit from features like code navigation, debugging, and version control integration, elevating your development workflow.
• Namespace Organization: Organize your VBA files into logical namespaces (folders) to create a structured and modular project. This helps in better code management, easy navigation, and improved collaboration.
• Live Server: Execute VBA macros directly from within VSCode with the live server feature. Get instant feedback, error logging, and real-time results, enabling efficient debugging and troubleshooting.
• Auto Complete: Accelerate your coding process with intelligent code completion. XVBA provides suggestions and auto-completion for VBA code, reducing errors and saving valuable development time.
• Syntax Highlighting: Enjoy syntax highlighting for VBA code in VSCode, making it visually appealing and improving code readability. Easily identify code elements and catch syntax errors during development.
• Snippets: Access a collection of VBA code snippets (prefixed with "x-") that serve as ready-to-use templates for common tasks. Use them to quickly add functionality and speed up your VBA coding.
• Code Formatting: Maintain consistent code style and readability with the built-in code formatter. XVBA automatically formats your VBA code based on predefined rules, ensuring clean and professional-looking code.
• Custom Ribbon Menus: Create customized ribbon menus with custom images to enhance the user interface of your Excel or Access applications. Add functionality and improve user experience with personalized ribbon menus.
• XVBA-CLI: Automate tasks, run macros, and interact with the XVBA extension from the command line using the XVBA Command Line Interface (CLI). Integrate XVBA with other tools and workflows seamlessly.
• Type Definition Files: Create type definition files in "vb" format to enable auto-completion and enhance code navigation within VSCode. Define custom types, objects, and their properties for improved coding efficiency.

?? Benefits

• Enhanced Development Experience: XVBA provides a modern and feature-rich development environment for VBA programmers. With VSCode as the editor, you get a familiar and powerful coding experience along with advanced features specific to VBA development.
• Improved Productivity: XVBA streamlines the VBA development workflow, offering features like code navigation, auto-completion, and code snippets. These tools help you write code faster, reduce errors, and increase productivity.
• Better Code Organization: The namespace feature in XVBA allows you to logically group and organize your VBA files. This improves code maintainability, facilitates teamwork, and enables efficient project management for large-scale applications.
• Efficient Debugging: The live server functionality in XVBA enables you to run VBA macros directly from within VSCode. This provides instant feedback, real-time error tracking, and efficient debugging, leading to faster issue resolution.
• Community and Support: XVBA has a vibrant community of users and developers who actively contribute to its development. Access online forums, documentation, and tutorials to seek help, share knowledge, and stay up to date with the latest advancements in VBA development.

Beta Version Obs:

First Make a backup for test

Some times Excel hides the sheets. For fix that, just go to view menu and unhidden than.

Last Features

- Rebuild Treeview

- Click on the Macro List to Navigate in the Code

- Fix Goto definition on Hover

- Add Button for open Ms Office Applications

- Reduce extension size in 63%

- Add New Annotations for new features and make code more readable

- Now with: Immediate Window Simulate in VSCode Output Channel Xdebug

- Improve Load Macros from VSCode

- Add export files to Excel with Lines Numbers Tagged

- Add Change Formatter tab size

See CHANGELOG

Next Features

• Code Explorer
• Implement the TDD like PHPUnit
• Debug
• ...

Support XVBA

If you find it useful, please consider supporting it for help me to create a FREE face-to-face SCHOOL for programmers in the small town of Vargem Bonita Brazil and create more tools and improve the Youtube channel

For beginners it is difficult to learn programming through tutorials

• Become a Sponsor join the growing group of generous backers

The list below is some off new features vba language will have with XVBA

• 1 Edit VBA from Excel And Access files in VSCode www.xvba.dev
• 2 *Namespace (Organize files in folders)
• 3 Live Server (With error log)
• 4 Run Macros from VSCode TreeView (With error log)
• 5 Auto Complete (Language Serve, in progress)
• 6 Syntax Highlighting
• 7 Snippets (starts with x-)
• 8 Auto Indentation Rules
• 9 Create Your own package and share in www.xvba.dev
• 10 Stop the Live server and load VBA files to Excel Manually
• 11 XVBA-CLI Command Line Interface for XVBA VSCode extension (@localsmart/xvba-cli)
• 12 Code Formatter - Got to settings search for formatter and set to local-smart.excel-live-server
• 13 Create Custom Ribbons Menus with Custom Images - See example
• 14 Add Hover functionality
• 15 Add Go to Definition (Hold Ctr and Click on function/sub/param)
• 16 Add Build and Production functionality (Files on folder xvba_unit_test will export to Excel/Access just on Production Mode )
• 17 Immediate Window Simulate in VSCode Output Channel (Use Xdebug package) Xdebug
• 18 Add export files to Excel with Line Numbers Tagged
• 19 Create type definition files in "vb" for auto-complete and share in www.xvba.dev

System requirements:

• This extension was tested on Windows 10 and Office 2019
• This extension was not tested on Mac or Virtual Machines
• @localsmart/xvba-cli works on nodejs 12.18.4

Youtube

Youtube Chanel For Tutorials (Click Here)

• How to install - First Steps
• Namespace - Put File in Sub-folders
• Create Custom Ribbon Menu
• Use Production Mode or Build Mode
• How to Formatter Code With XVBA
• How to use Git With VBA
• Create Comment Block for VBA with XVBA
• Create Type Definition Files
• How to install node and use xvba-cli
• Use SQLite With VBA
• VBA Tutorials

In time, more Videos will came for Javascript/Typescript/React/Angular..

Create, install, and share VBA Packages With Xvba-cli and Xvba Repository:

• Xvba Repository : www.xvba.dev
• Xvba-Cli : @localsmart/xvba-cli

Comment Block

Obs: The first comment block in the file is for modules/class namespace

'/*
'
'Comment block Example 
'
'*/
Public Sub main()
 
End Sub

• For use excel Objects auto-complete install the package excel-types with @localsmart/xvba-cli See excel-types package on github

Contributing

Please contributions are welcomed and appreciated.

• See excel-types
• In Time all code will be free

All feedback are welcome.

• Mail : alberto.aeraph@gmail.com
• WhatsApp: +55 31 9 9229 5626
• Also please write a review, star me on GitHub, and follow me on github
• Github account: Aeraphe

Installation

(Save your work and create a copy, before setting up the server)

• First, set your excel security to run macros on open

• Second, click in the Bootstrap icon on XVBA (TreeView)

• This command will create a user config file (config.json) In this file you set the name of your excel file (xlsm,xlsb or xlam) and the project root folder (Where your vba file will save)

{
  "app_name": "XVBA",
  "description": "",
  "author": "",
  "email": "",
  "create_ate": "",
  "excel_file": "index.xlsb",
  "vba_folder": "vba-files",
  "xvba_packages": [],
  "xvba_dev_packages": [],
}

Obs: vba_folder path are relative to workspace and can't accept absolute path like : "c:\xx\xx"

• Now Import VBA files, click in the import icon on XVBA (TreeView) (if excel file has vba code)

XVBA Live Server

• Click on Button to run XVBA Live Server (For edit files in VSCode and auto update Excel file)
• If you don't want to use Live Server just click on button "Load VBA on Excel" to save your code changes on Excel when you want.

Use Git to track changes on your code

• Go to vscode terminal and type de command: git init (Press enter)
• See on: https://git-scm.com/

Feature List

Import excel VBA code to files (With error log)

• XVBA will import and create files for edit in VSCode. Obs: XVBA only import modules/class's/forms. Code inside sheets will not import Obs: modules name can not have accentuation

*Namespace

• Define the folder where your vba file (Class, Module,Form) will live
• For that, use the tag "namespace" no vba files like:

'namespace=rootfolder/folder/subfolder

• You can use namespace snippet

Live Server

• If you save VBA files changes in VSCode, it will automatically update your Excel file (like hot reload)

Obs: Import vba files button will stop Live Server

Code Navigate in TreeView and Open Office Application Button

• Right click on macros in treeview go to code

Run Macros from VSCode TreeView (With error log)

• Right click on macros on "LIST VBA MACROS" TreeView and select the option run
• The Macro will run on excel file Obs: Macros that receive params can not be run direct . Create a test file for that

• A Log file "excel_exec.log" wil create on workspace. Where you can see the success or error on run Macro

• To see Errors on VSCode, use On Error Resume Next on the first line of the VBA code Set the error source name Err.Source ="func"

  Function func()
  
     ' If an error occurs, pass error to VSCode
     On Error Resume Next    ' Defer error handling.
     'Set Error Source
     Err.Source="func"
  
  End Function
  

Auto Complete

• Use the auto-complete code by a Language Server (not snippets)

Syntax Highlighting

• This package provides syntax highlighting VBA.

Snippets

• All xvba snippets starts with x-

Example (Block Comment): x-blc

Dim declarations

Sub/function

Module/Class/Form Files

• if/else
• for/while
• case
• etc...

Auto Indentation Rules

• VBScript by Darfka (Auto Indentation Rules)

• Set *namespace for all your files to (sub-folder = your package name)

'namespace=xvba_modules/sub-folder

• Just commit your changes and push to git repository

git push

Stop the Live Server and load VBA files to Excel Manually

XVBA-CLI Command Line Interface for XVBA VSCode extension

• Add third packages to your project with command line interface
• Create Your own package and share xvba.dev
• Type on terminal and press enter for install the xvba-cli Last Version

 npm i @localsmart/xvba-cli@latest --save-dev

See XVBA-CLI

Code formatter

• Code formatter Got to settings search for formatter and set to local-smart.excel-live-server For format code short keys (alt+shift + f)

Immediate Window Xdebug

• Immediate Window Simulate in VSCode Output Channel (Use Xdebug package) Xdebug

• On Error Handling :
  • To prevent error-handling code from running when no error has occurred, place an Exit Sub, Exit Function, or Exit Property statement immediately before the error-handling routine, as in the following fragment:

Add export files to Excel with Line Numbers Tagged

Code Annotations for new features and make code more readable:

• @ignore-comment (Ignore Comment to show on completion/hover functionality)
• @module-comment (Define the comment block for module ".bas" and ".frm" files)
• @class-comment (Define the comment block for module ".cls' files)

Other Stuffs:)

Troubleshooting

File Encoding Issues

• If you see special characters displayed incorrectly, it's likely an encoding issue. VBA files use Windows-1252 encoding by default.

To fix:

1. Open VSCode settings (Ctrl + ,)
2. Add these settings:

"files.encoding": "windows1252" "files.autoGuessEncoding": true

This will set the default encoding to match VBA files.

This package is built with this plugins:

• VBScript by Darfka (Auto Indentation Rules)
• luggage66/vscode-vbscript (Syntaxe Highlighting)

Versions Updates

• Move to CHANGELOG File

Bug Fix

• Move to CHANGELOG File