Hello r/vba, today I wanted to share my free and open-source VBA function library, XLib. XLib includes around 120 functions, is very small in size (around 60 KB), and is written in pure VBA, so you can easily add it to your Office file by simply copying and pasting the source code into a Module, or by importing the Xlib.bas file. XLib has been tested on and works with VBA for Word, Excel, PowerPoint, Access, Outlook, and Publisher.

Some functions in XLib include:

• Len2 -> Returns length of Strings, Arrays, Dictionaries, Collections, and any other objects that implement the property .Count, including Workbooks, Sheets, Worksheets, Ranges, Documents, Presentations, Slides, and many other Office Objects

• Sort -> Sorts an array in ascending or descending order

• Max/Min -> Max and Min functions that work in all the Office programs

• Large/Small -> Same as the Large() and Small() functions in Excel, but can be used in Word, PowerPoint, etc.

• SubstrFind -> Returns all characters between two substring

• IsInArray -> Returns True if the value is found in an array

• Quarter -> Returns the quarter of the year

• RandBetween -> Same as Excel RandBetween(), but can be used in Word, PowerPoint, etc.

• RegexTest -> Tests if the regex is found in a string

• Jsonify -> Converts arrays into JSON format

• Http -> Performs a web request and returns the response, with options to set headers, send post data, etc.

• ReadFile -> To easily read files

• WriteFile -> To easily write files

Hopefully this library can help make programming in VBA a bit easier, and if you have any ideas for new functions please feel free to reach out. Also, if you want to support the project, please consider giving it a star on github

I’ve been building and collecting VBA macros over time to handle common Excel and Outlook tasks that tend to be repetitive or time-consuming. I recently organised them into a single library and thought others here might find them useful.

The macros cover things like: • Working with Word, PDF, and Excel files • Handling Outlook emails and attachments • Data cleanup and manipulation in Excel • Creating draft emails and documents automatically

Hi /r/vba!

I created a series of VBA cheat sheets / lists for common VBA tasks. Including tasks related to:

• Sheets

• Cells & Ranges

• Rows & Columns

• Workbooks

• Settings

• Errors

• Files

• Arrays, Collections, & Dictionaries

You can see the lists available on the webpage (Hint: Use CTRL + F to search), but I also created a PDF that you can download (it's free).

I'd love to hear your feedback!

• Am I missing anything?

• Would you be interested in more comprehensive "cheat sheets" for specific topics?

Edit: Fixed some of the issues both on the PDF cheatsheet and on the webpage.

When Excel 5 introduced VBA 25+ years ago, I wrote a few tools that were widely used by VBA devs but never migrated to 64-bit. After a flurry of emails last year, I bit the bullet and rewrote them in C#. There’s now been 500+ installs so I’m hoping the initial porting bugs have been resolved:

Smart Indenter for VBA reindents your code, with loads of options to fine-tune what it does. Now with AI - Auto Indent - to apply the indenting as you type; when you press Enter, the cursor is right where it should be on the next line.

VBE Tools adds a pixel-by-pixel user form control nudger, performance measuring, Record at Mark and a few other niceties.

Both available from Officeaddins.co.uk.

If you do a lot of copying and pasting from excel to other apps, you might also find my new ‘Copy as List’ addin useful, copying the selected cells as a preformatted list, suitable for where you’re pasting it. That’s available from AppSource.

When I started my last project, I saw VBA as a slightly obscure way to automate repetitive Excel work and push formulas a bit further.

Then every time I thought I had reached its limit, I found another door.

What began as a few scheduling calculations gradually turned into dependency graphs, critical-path analysis, incremental recalculation, automated tests and an interactive Gantt built with Excel shapes.

The real turning point was when I stopped treating Excel as the application and started treating it as the interface. The workbook handles inputs and outputs, while most of the actual logic runs in memory through arrays, dictionaries and separate modules.

VBA is still old, awkward and occasionally infuriating. But I expected it to do a little more than Excel formulas.

It turned out it could do a lot more.

I’m curious: what did you build that made you realise VBA was more capable than you initially thought?

I've got a serious problem... I have realized that I actively look for, and sometimes create, reasons to build/revise codes...

My job description says absolutely nothing about the need to have VBA knowledge, but everything that everyone on my team of six co-workers does flows through one or more of my macros and after 3 years, it's safe to say that they're vital to the operations of my entire department, and have a critical impact on the departments that they interact with down the line.

This post wasn't intended to be a brag, but as of a year ago, I made a conservative estimate that for my department alone, I've saved us 450+ labor hours a year, and that doesn't account for the dozens of times reports (and thus macros) have to be run additional times for a single project, or for the time saved due to inaccuracies/human error. Since that time, I've added functions to existing macros, and built new ones to address other needs. In the last 3 years, I can say that I designed code that avoided near work stoppages twice.

My actual duties are to design what grocery store shelves look like. Most people think it sounds interesting, and for the first year or so, it was. Now though, it is tedious and monotonous and the days I get to work on codes are the only ones where I truly enjoy coming to work, and I don't want to leave when the day is done. I'd love to have a career that revolved around VBA entirely, but I have no degrees/certifications remotely related to it, so that is highly unlikely.

Am I the only one who has become consumed by the fun of working with VBA??

I am using/learning VBA since the Start of the year and as of today, I built a conplete CRM-/reporting System using MS Access, Sharepoint/OneDrive, Teams and Outlook.

With the help of my tools I was basically able to save the company 90 hours of work a week - only in my department.

When I first got into VBA everyone told me that I'd just waste my time, but since I enjoyed it I proceeded to learn it anyway.

IMO it's a really great way to get started with programming.

I just wanted to brag a little - I'm sorry.

Since my employer saved so much money, we are switching to Salesforce next year - which stings a little bit to be honest :D It's clearly the smarter choice tho :D

I have now spent some time trying to give Office Scripts a fair chance to act as a substitute for my VBA code. I am not impressed and am frankly dumbfounded.

The code "editor" is absolutely horrible: it's basically a notepad with minimal functionality. There's no way to organize code into modules - so any large codebase is not nearly as easy to navigate as in VBA. Cutting and pasting code is more awkward also. It is shocking that Microsoft could neglect the VBA IDE for years and then introduce an Office Scripts editor that has practically no functionality whatsoever. A big step backwards for the end user's ability to automate things in Office.

As far as functionality, I very quickly ran into things that I could very easily do with VBA but that I found virtually impossible or outright impossible to do with Office Scripts.

Could someone please explain to me what Microsoft's strategy is here? VBA seems to be a *far* superior way to automate things in Office. Why would Microsoft literally make its automation solutions much worse than they are in VBA?

I know so many people have said that: „VBA is old as fuck, looks like from 1902 and isn’t really programming“ but i mean it works and so many industries are using it - why is there no interest to update it, i mean at least the Editor

'Adjusts Excel settings for faster VBA processing
Public Sub LudicrousMode(ByVal Toggle As Boolean)
    Application.ScreenUpdating = Not Toggle
    Application.EnableEvents = Not Toggle
    Application.DisplayAlerts = Not Toggle
    Application.EnableAnimations = Not Toggle
    Application.DisplayStatusBar = Not Toggle
    Application.PrintCommunication = Not Toggle
    Application.Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic)
End Sub

This subroutine is useful for when you have a large VBA macro that needs to make a lot of changes to your workbooks/worksheets. Here's a breakdown of what each of the settings does, and the benefits it brings when toggled.

• ScreenUpdating This makes Excel not update worksheets when you make changes to its contents. This saves your computer from having to spend precious time drawing everything to Excel when you make changes to your worksheets.

• EnableEvents This prevents Excel from needing to listen for event triggers, and then having to execute those events. This doesn't have as much of a large effect on cutting down VBA processing time, but it's useful if you're working with code that does make other events fire, because Excel doesn't need to "listen" for those events.

• DisplayAlerts This prevents Excel from displaying default alerts that are not security related. This means that if you made a macro that deleted a worksheet, your code wouldn't be interrupted by a confirmation pop up waiting for user interaction.

• EnableAnimations With the update to Office 2016 (or so) Excel began to have pretty animations regarding animating the selection box across the screen, versus instant changes to the selection box. Disabling animations lets Excel not have to spend time showing these animations, and further allowing VBA to be processed faster.

• DisplayStatusBar This one doesn't make Excel save as much time as other settings, and it's a somewhat useful setting to use if you require displaying code progress. This line can be removed if you do require using the status bar for displaying information.

• PrintCommunication This is somewhat similar to the ScreenUpdating setting, where you can alter page setup settings without needing to wait for the printer to respond. Once page setup settings have been configured to the way you require, enabling this setting will then apply the updated settings all at once.

• Calculation This setting toggles the method of automatic calculations that Excel normally performs when worksheets are changed. This setting when disabled, changes the automatic calculations to manual, meaning you have to explicitly tell Excel to perform calculations to update any volatile formula on that worksheet. This can save you a tremendous amount of time when processing VBA code, as any changes your code makes to a worksheet would normally trigger a calculation event. Calculation events, depending on the complexity and quantity in your worksheet can slow Excel down to a crawl, which means VBA gets executed that much slower.

Notes:

• VARIABLE = IIF(TRUE/FALSE , TRUE VALUE , FALSE VALUE)

• Excel processes Formula using multiple threads (multi-threaded) but processes VBA using a single thread. A faster clocked CPU means VBA can be processed faster, but these settings will help far more than a super fast processor.

• This subroutine can be enabled using Call LudicrousMode(True) and disabled using Call LudicrousMode(False)

This subroutine should only be called within your main sub. Generally, functions are called by other code, so you would not want to toggle these settings within functions. Repeatedly toggling these settings can slow Excel down, hence the recommendation to only toggle these settings from your main sub.

I majored in economics in college, so I had a taste of working with R and Tableau. I always wanted to learn how to code.

Been messing with VBA for a year or more, but decided to get serious 6 months ago.
I work in corporate finance and when I started my current job, I saw this file that had a macro written on it that blew my mind. (My boss and another guy cobbled together the code)

I was jealous, amazed, terrified at the complexity but also inspired and decided to start getting serious and needed to specialize in something, since everyone at my job is either a CPA, or has amazing soft skills, etc. I needed to know something that other people didn't. I'm already pretty good at Excel (working on getting MS-201 certification) but the ceiling for Excel is nowhere near as high as a programming language.

Fast forward to now... I don't think I'll ever be a VBA power user, since I don't have a programming background. Comparing what makes someone "advanced" in VBA when comparing an analyst vs. an actual engineer is a bit unfair.... But after about 150 hours of practice I am pretty damn comfortable with the fundamentals (variables, object model, loops, error-checking, controlling flow-of-code). I have been able to automate a ton just with this. So much so that I decided to take a stab at that formerly insane-macro my boss wrote. I re-wrote it in about 35 min, for about 40 lines of code (vs around 200 for theirs). Their code, which seemed extremely complex at the time, is not very good and terribly inefficient. I am proud and humbled to have gotten to the level of skill where I am at, even if I'm still in relative infancy compared to seasoned programmers.

Anyway, this post is just to say: Practice, practice, practice. It pays off. And thank you so much to you guys for being the source, I have learned a ton through here.

Our VBA, who art in Excel,
Hallowed be thy Code.
Thy IDE come,
Thy Macro be run,
In Access as it is in Excel.
Give us this day our daily DoEvents,
And forgive us our GoTos,
As we forgive those who label against us.
Lead us not into Python,
But deliver us from JavaScript.
For thine is ThisWorkbook,
The ActiveSheet, and the Range,
Forever and ever,
End Sub.

If your macro takes more than a couple seconds to run, just put this at the end of the code:

MsgBox "Done."

It's simple and your users (and your future self) will thank you.

Hi!

Lurking a lot here, but now i am posting.

First: I am an mechanical engineer and not very good in programming.

I wrote a Programm which searches for pictures with some rules and copys them into an excel sheet. This was done by hand for more than 20 years. Now everyone is excited because it saves hours of time.

By the way i did it together with AI. Helped a lot, couldnt do without it. But it is doing some bullshit very often 😅

I really liked the vba experience because it can be easily spread through the company without any extra software!

Do you have some advice for the best AI experience with programming?

I listen to it every day. VBA is only for junior programmers, Excel is for beginners, Java or Python is the most important. Then I go among the rank-and-file employees and each of them has Excel installed on their PC. The json format doesn't mean anything to them, and the programming language is a curse for them. The control software of the entire factory? Xls file with VBA software connected to production line databases. Sensitive data? Excel in the HR folder. Moving from one database to another? Excel template or csv. Finaly at the end of the day, when the IT director and his talk about canceling Excel leaves, a long-time programmer comes and adjusts VBA in Excel so that the factory can produce and managers will get their reports the next day without problems… My question is how many of you experience this in your business? When excel and VBA are thrown down and claimed to be unsustainable at the expense of applications in Java or python…

Hi everyone,

I wanted to share my Resident Evil project. While my other projects like Lemmings or Cookie Island were actually more challenging in terms of complex logic and algorithms, this one was a massive marathon in rendering and visual structure.

Technical Highlights:

• Rendering: Using the Excel grid as a canvas with over 200+ hand-drawn worksheets.

• 64-bit Compatibility: Fully overhauled with PtrSafe declarations and LongPtr.

• The Engine: Pure VBA logic. No external DLLs.

• Origin: Self-taught, started with BASIC on the C64.

The project is now fully unlocked for anyone who wants to deep-dive into the code.

Gameplay & Download: https://cookiesoft.itch.io

I’d love to hear your thoughts on the structure. Happy to answer any technical questions!

WinVBA - an IDE for Visual Basic for Applications

Today we would like to announce the first public release of WinVBA, a modern IDE for Visual Basic for Applications (VBA) development. The current version is still under development, but we wanted to share it with the community and get feedback from users.

Disclamer

This product has been developed by: https://winvba.com/

This product is still a development release.

This product is provided as is. By downloading this product you agree to the terms of the license agreement. You agree that WinVBA and the developers are not responsible for any damage caused by the use of this product.

Products

WinVBA

The main product, a modern IDE for VBA development.

WinVBA Light Theme

WinVBA Dark Theme

WinVBA Add-in

An Office Add-in that allows you to open the WinVBA IDE from within Excel. In addition this will add WinVBA to the context menu when right clicking on a sheet tab or button.

WinVBA AddOn RibbonBar

WinVBA ContextMenu entry

(Currently only works when WinVBA.exe is placed on the desktop)

Features

This is a list of some of the features that are currently available in WinVBA:

Editor:

• Tabbed interface

• Syntax highlighting

• Code auto completion

Code navigation:

• View Sub/Functions in treeview

• View variables in treeview

• View references in treeview

Source control:

• You can easily export the project or individual modules to disk as text files or ZIP archive.

Limitations

• Currently the product only works with Excel. In the future the product will be expanded to work with other Office applications

• In FormControls (AddOn) there is no custom Assign Macro option (right click on button to assign macro)

• Forms are not supported yet (working on it)

• Only one workbook can be opened at a time

• Many buttons and options don't work yet

• Debugging is not supported yet (Run Macro works)

• Immediate Window works but has limitations and known bugs

• Many known bugs and stability issues

• Speed and performance can be improved

• Themes are not saved yet thus you will have to set your theme every time you start the application

We are working hard to remove these limitations in future releases.

Known bugs

Currently this is a development version, so expect A LOT of bugs. Some of the bugs you may face will be:

• Crashes

• Null pointer exceptions

• Slow performance

• Unimplemented features

• Partially working features

• Excel doesn't always close on exit

Version

The current version is 0.2.0 and should be considered as an alpha release. This is a development release and is not intended for production use.

Installation

To install this product simply download the latest version from https://winvba.com/download/ Extract the ZIP file to a folder of your choice.

1. Copy the WinVBA.exe file to your Desktop.

2. Install the Office Add-in by opening the WinVBA Add-in folder and running Setup.exe This will add 3 buttons in the Developer tab in Excel: WinVBA Code, Macros, Settings.

3. Open the WinVBA.exe file to start the application or open your workbook and select WinVBA from the Developer tab.

Future plans

• Remove the limitations listed above

• Support for other Appllications

• Support for UserForms

• Ability to debug the code (set breakpoints, add watches, step through code, etc.)

• Full git integration

• AI assistance for code generation

• Speed improvements

• Stability improvements

• Many more new features which will currently not be disclosed...

Requirements

• To use this product you need to have Microsoft Office installed on your computer and have a valid Office license.

• Windows 11 (the exe is Self-Contained thus it may work on Windows 10)

License

Currently the product can be downloaded for free with all (limited) working features enabled. A licensing system will be integrated in the future, this will include a free version for personal use with some limitations and commercial licensing.

---

Feel free to leave feedback in the comments below. We will be answering questions during the next few days.

Head over to https://reddit.com/r/WinVBA to discuss this release and provide feedback!

I'm curious to hear what VBA projects that you consider the "crowning jewel" in your portfolio. If you want to include what you do/did for a living as well, that would be awesome.

I'm an accountant. I once made a playable version of Flappy Bird in my spare time... not necessarily what I'm most pleased with, but it's fun to show people haha.

According to this post click, the Microsoft is shutting down the VBScript library on Windows OS within next few years. The major features that no longer will be available are:

1. Executing .vbs files in runtime,

2. File System Operations [File System Object for instance].

3. RegEX (fortunatelly it will soon be available natively in Excel),

4. Dictionary Object,

5. Shell and Enviromental Interactions (Shell Object).

If you are developing some long-term projects, you might want to take it into account.

Edit: Sorry for bringing panic, as some of you down belown explained that only Regex is being dependent on VBScript, therefore only it is being removed. For intelectual honesty I will not redact the higher part of post. Thank you for correcting me.

In a previous publication, I showed a class module that allows users to emulate certain functionalities of a TextStream object, but using, exclusively, native VBA functions. On that occasion, u/ItsJustAnotherDay- asked about the functionality that the proposed piece of code could have, given the existence of various utilities that pursue the same purpose. Today, I want to take advantage of this space to display one of the fields in which the ECPTextStream module is useful.

In Reddit, I searched suggestions to split a CSV, or text, file from VBA. The search took me to this post and to this other, in which the need to divide files of considerable size into a sequence of files with a specified number of lines is made. The given solutions promote to learn a different programming language rather VBA, and I start to think that these is the reason for which both threads keep the [unsolved] flair until this date.

Here I leave you an Excel Workbook that has the ability to slice text files, or CSVs, up to 2GB in size. If you would like to know a little more, please visit this link.

First off - I'm not a programmer. I am, however, someone who hates repetitive tasks with a passion. I have a few tasks in my job (as a mechanical engineer) that required me to browse to ~100 different company intranet pages (reports) and pull data off those pages. Our IT group is great, but they are also way over-worked, so getting custom reports generated for little-ole-me is pretty impossible. Enter Web Scraping!

Unfortunately for me, all our company computers are locked down and are unable to run any non-whiltelisted .exes. Since we're a manufacturing company, no one uses Python or any other programming tools. I tried to get Python whitelisted but was unsuccessful. Enter VBA Web Scraping!

Enough story - the purpose of this post is to consolidate a few resources I've found and summarize my methodology because documentation and examples are hard to come by. I hope this is helpful to the next guy who is stuck solving this problem in the only programming language available to him - VBA.

First up: Before you write any code - Learn to use your browser developer tools (F12 in Chrome and IE). These are priceless. Here's what I use them for:

• Jumping right to the relevant HTML Element in the page source ("Elements" tab - the mouse-pointer icon. Using that tool, click the element you want to inspect and the developer-tool frame will jump to that element in the HTML.

• In the network tab, you can inspect the HTTP requests for any documents pulled from the server. Get/Post data can be viewed here along with HTTP Headers.

  • Example - Some Javascript on the page updates a table when you change a selection in a combo box. If you record the network traffic while changing the selection, you can find all the data on the HTTP request that the javascript sent to the server to get the updated table. You can even see what the received file contains.

Second: I've seen several folks use the Internet Explorer object to do their scraping. I prefer to use XMLHTTP. I think it gives more flexibility, and is similar to how you'd do a scraping project in python with Beautiful Soup. Basically, the XMLHTTP60 object becomes your handler for sending and receiving all server requests. The XMLHTTP60 reference isn't loaded by default, so you will have to turn it on in the VBA IDE if you want Intellisense to work. I can't work without Intellisense. Go to the tools menu -> References and select "Microsoft XML, v6.0"

Here's a basic example of syntax:

Dim xmlhttp As New XMLHTTP60
Dim myurl As String

myurl = "www.PageYouWantToScrape.com"

' This is what an HTTP "GET" Request looks like
With xmlhttp
    .Open "GET", myurl, False
    .setRequestHeader "Header1", "Header1 Value" ' Use the Developer tools to figure out what headers you need to send
    .setRequestHeader "Header2", "Header2 value"
    .send
    
End With

' This is what an HTTP "POST" request looks like
Dim postData As String

postData = "searchterm=itemyousearchedfor&results=100" ' Use The Developer tools to see what this needs to be.

With xmlhttp
    .Open "POST", myurl, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" ' Again - check this with the developer tools.
    .send postData

End With

This Page has a good example of how you would save HTTP response headers and send cookie values to keep a persistent login across multiple pages.

Third is actually working with the HTML data. I use the Microsoft HTML Object library. If you want Intellisense to work for this, you'll have to add the reference to your VBA project as well (Tools -> References, then select "Microsoft HTML Object Library")

Basic Code Example (insert after previous code):

Dim htmlDoc As New HTMLDocument
Dim elements As IHTMLElementCollection
Dim link As IHTMLElement

'this will get all the links on the page
Set elements = htmlDoc.getElementsByName("a")
' I use Intellisense here a lot to see all the available methods

For Each link In elements
    'This will display a message box with the link URLs
    MsgBox link.getAttribute("href")

Next link

Unfortunately the documentation on syntax for all these HTML functions is severely lacking. wiseowl has a decent page I just found, but the best I've been able to do is use intellisense to list the methods, then do a google search for that method. Thankfully the names are pretty well defined and usually someone on StackOverflow has given an answer with that particular method. Typically with 'getElementByID' or 'getElementsByName', you can get most of the way there. I've also done long chains of element.firstChild.nextSibling.nextSibling to get from some element that has an ID into the specific piece of data that I want. I'll throw in there that there is an "HTMLTable" object that allows you to use rows/columns etc. for navigating tabular data on pages.

Hopefully this was helpful to someone out there. Have a great day!

Edit: can't believe I forgot to pay the dog tax