VBA to Python – 10 Simple Python vs VBA Examples - The way Microsoft is moving with introduction of Office Scripts it does seem like the Redmond giant will not be planning to invest anymore in expanding VBA, however, will leave it as the designated offline scripting language. The challenge there is that while Office Scripts will become more mature we still need to wait a… ...
Office Scripts vs VBA – Microsoft’s take on VBA in 2022 - For years now Excel lovers have been anticipating what Microsoft's move will be in terms of scripting within Microsoft Office. Many users till date are creating automations and small office use applications on top of Excel and other MS Office solutions for the better or worse, simply due to the ease with which you can… ...
How to create a Twitter Power BI dashboard (with Azure) - Twitter is currently the 7th most popular Social Platform with millions of Tweets being created daily. Often we can hear on the news from Twitter before it gets published to over news sites. It provides a plethora of data that we as analysts can use to understand peoples sentiment as well as create early warning… ...
Excel VBA Fuzzy Match text against a table - Fuzzy text matching is very useful when you want to compare a text string against other strings that don't have to be identical. You still, however, want to find the one that is closest in terms of words. Fuzzy matching is very useful when e.g. you want to compare a user question against a database… ...
Excel UDF Tutorial – How to write Custom Excel Functions - Excel UDF aka. User Defined Functions are somewhat of an underappreciated feature in Excel in my own opinion. With the recent announcement and release of the Excel LAMBDA function it almost seems like only now are we able to create our own Excel functions - leveraging on a combination of already existing Excel functions. Let… ...
Bitcoin Price in Excel – How to get Bitcoin price in Excel? - Recently the price of Bitcoin has skyrocketed again and many people keep refreshing their phones and browsers to be on top of current price fluctuations. However, why not use Excel - and for that purpose, how to get Bitcoin in Excel using a formula? There is no ready formula in Excel but it turns out… ...
Excel MEDIAN IF – Median with criteria in Excel - The MEDIANIF / MEDIAN IF formula is not natively available in Excel as opposed to familiar functions in Excel such as AVERAGEIF or SUMIF. The MEDIANIF function should provide us, for a population of numbers, with the middle number that splits the population into 2 halves. Additionally it should allow us to apply a filter… ...
Excel LAMBA Function – Create your own Excel Functions! - The Excel Lambda Function is a new powerful addition to the Excel suite of functions, that Microsoft announced not long ago. The Lambda function allows you to define a new Excel function with one or multiple parameters. I see it being especially useful in case of string manipulation or definition of new mathematics/statistical functions. What… ...
Excel VBA Find – Values, Formulas, Comments in Excel - You will find everything you need to know on the Excel VBA Find function. The Range Find function allows you to find cells within your Excel worksheet (and workbook) that contain a certain text or value. In this post let us explore the many ways in which you can use the Find function. Looking to… ...
VBA Implements Tutorial – Defining a Class Interface in VBA - The VBA Implements statements specifies that the current VBA Class implements a different Class or Interface. Classes and Interfaces are a must for Object Oriented Programming (OOP). If you are not aware of OOP concepts start with my VBA Class Tutorial first. What is an Interface? Before we start with an example I want to… ...
VBA GoSub – How to use On…GoSub….Return (VBA) - The VBA GoSub statement allows you to jump to a specified line label basis the numeric value of the provided expression. The expression must be numeric and evaluate to only numbers between 0 and 255 (others will cause exceptions). We will start with a simple example: The output will be: As you will notice the… ...
VBA Copy Sheet – Copy Data from one Worksheet to another - Copying data from one worksheet to another is a pretty often and routine scenario. Fortunately, we have a way to VBA Copy Sheet data from one to another using Excel VBA Macro. What is more there are many ways for us to approach this presumably simple problem. VBA Copy using Range Copy Function A simple… ...
Excel XLOOKUP vs VLOOKUP in Excel – Which is better and why? - The choice between using XLOOKUP vs VLOOKUP (or XLOOKUP vs INDEX MATCH if you prefer) in Excel will be an easy one it seems... Let me explain. The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. With it came also other less known functions such as the… ...
Why is my Excel file so LARGE? Learn how to reduce Excel file size! - In this post I will explain various ways to reduce Excel file size. Large Excel files slow down your system, increase the likelihood of the file crashing as well as obviously use more drive space. Microsoft Excel was designed to be a tool for managing relatively small datasets for Finance & Accounting purposes, today we… ...
Find and Replace in Word – Using with Wildcards and VBA - The Microsoft Word Find and Replace feature is very powerful and a great time saver for the more skilled user. You can use Find and Replace to locate exact words, phrases and even patterns matching various scenarios. Word Find and Replace Let us start with exploring how to do a regular Find and Replace in… ...
VBA Timer: Create a Stopwatch in Excel VBA - In this post we will explore the various ways we can set a Timer in Excel VBA as well as how to build your own VBA Stopwatch. Setting timers can be extremly useful if you want to run code as specific time intervals. On the otherhand a practical example can be a VBA Stopwatch which… ...
Dynamic Charts in PowerPoint – Self-refreshing Charts using VBA - Technically in PowerPoint you can link an Excel file to a PowerPoint presentation. However that will not make the Chart dynamic, and sometimes it is hard to say when the Chart will be updated. But what if we want even more - what if we want the chart to refresh during a Presentation? I can… ...
VBA Paste from Excel to PowerPoint - In this post we will explore how VBA paste from Excel to PowerPoint objects such as a Range, Chart or other element. Below you will find working code snippets. We will learn also to modify this routine to address different VBA Copy Paste from Excel to PowerPoint. VBA Paste Range from Excel to PowerPoint We… ...
Random Number Generator in Excel / VBA - We will explore the options to create your own random number generator in an Excel Worksheet or in VBA (Macro). You can generate randoms in 2 ways: Using Excel functions i.e. using the RAND or RANDBETWEEN functions Using VBA (Visual Basic macro) using the RANDOMIZE and RND functions Random Number Generator using Excel functions To… ...
VBA Run Macro on All Files in a Folder / All Worksheets in a Workbook - A very popular Excel automation scenario is the need to a VBA run macro on all files in a folder or running VBA on all Worksheets in an Excel Workbook. This is a very typical case where you process similar data dump files and want to extract data or transform the workbook. In this post… ...
Get VLOOKUP Multiple Matches – Multi INDEX MATCH in Excel - The Excel VLOOKUP function by default allows you to find only a single match and will return the corresponding row of a selected column value. What if you want to find VLOOKUP multiple matches, not just the first one? In this post let us explore this more complicated scenario. Instead of VLOOKUP however we will… ...
How to unhide sheets in Excel? Unhide all Sheets in Excel VBA - To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window. I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to… ...
AutoFit Excel Rows and Columns - Excel rows and columns sometimes needs to be expanded to fit their content. How to AutoFit in Excel? Fortunately Excel allows you to AutoFit Excel rows or Excel columns to its contents readjusting the height or width. In this post I will start with showing how to resize Excel rows and columns. Then I will… ...
Microsoft Excel Shortcut Keys - Below are common used Microsoft Excel Shortcut Keys grouped for depending on usage - from Excel Workbook shortcuts to VBA Macro Excel Shortcuts. Use the Table of Contents on the right to navigate. Excel Workbook Shortcuts ShortcutsDescription Create a new WorkbookCtrl+N Save the Active WorkbookCtrl+S Save As option for Active WorkbookF12 Open a WorkbookCtrl+O Close the… ...
VBA Delete File – Deleting files using VBA - To delete a file using VBA you need to use the VBA Kill Function. The problem with the VBA Kill function however is that it will not check whether the file exists in the first place, and instead will throw and error. Other VBA File resources VBA File Functions VBA Reading Files VBA Writing Files… ...
VBA Tips and Tricks - Instant Print in the Immediate Window Use the "?" preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below: This is a VBA Trick I was not aware for a lot time but saves a lot… ...
Visual Basic Editor Tutorial for Excel – How to use the VBE? - The Excel Visual Basic Editor is also sometimes referred to as the VBA Project window. The Visual Basic Editor (VBE) is a simple developer environment available in Excel, Access, PowerPoint, Word and all other MS Office applications. It allows you to code simple Visual Basic for Applications macros. If you are new to VBA checkout… ...
Excel to Image – VBA Save Range or Workbook as Image - How to save an Excel Range as an Image file (JPG)? Today's post is inspired by my readers emails. In some of my previous emails I considered other Excel Image scenarios such as Adding Images to Excel using VBA or using the Excel Camera Tool to create an image snapshot in Excel. However today I… ...
Excel Gantt Chart Tutorial - Straying from usual VBA topics let us explore today another tool that can be easily created in Excel. Who said you need MS Project to play with Gantt Charts? Today we will explore the Gantt Chart and answer questions like "How to create a gantt chart in excel?". You will find a useful Excel Gantt… ...
Excel VBA Evaluate – Tips and Tricks to use Application.Evaluate - Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence - today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function. Evalute is a great if you want to easily reuse your Excel formulas in a context of a… ...
Excel VBA ColorIndex vs VBA Color property - There are two ways of setting colors of Cell Backgrounds, Fonts or Borders in Excel. One of them is the VBA ColorIndex property and the other of them is the VBA Color property. But what is the difference between the two, and which is better! So let us explore... Be sure to also check out… ...
VBA Download / Upload File using VBA FTP - Today we will learn how to download and upload files using a VBA FTP script. FTP is a great way to keep your Excel files connected to your file servers, back up your files and worksheets automatically or simply download/upload document without any additional manual hassle. Other VBA File resources VBA File Functions VBA Reading… ...
Excel Character Codes and using CHAR / CODE functions - Often we look to certain symbols in Excel that are hard to locate on the keyboard. Or the opposite - we look to find the Excel character codes for certain characters. In such cases we must usually revert to using the CHAR Excel function or the CODE Excel function. Let us explore these functions and… ...
VBA VLOOKUP – Using VLOOKUP in VBA - The Excel VLOOKUP function allows you to lookup a value in a table based on a corresponding lookup value in the first column of a table. Below we will explore how to use VLOOKUP in VBA using tWorksheetFunctions as well as how to use the VBA Dictionary as an alternate approach. VLOOKUP is easily used… ...
Excel Camera Tool – create an Image snapshot in Excel - Did you know you can use Excel to Image snapshots of your Excel spreadsheets ? Sure you can. What is more you can use this feature directly from VBA to achieve some impressive feats. Today we will learn how to use the Excel Camera Tool! Excel Camera Tool Using the Excel Camera Tool[/caption]Let us start… ...
Excel Count Cells with Text and Formula – Excel Stats - In Excel we often like to Count Things. Sometimes those things are Cells with Text, Formulas or Formatting. Other times we want to Count Blank or Non-Blank Cells... and so on. Today I will teach you All About Counting Things in Excel. Excel Count Functions are an obvious option to go with, but there are… ...
How to create a Microsoft Query in Excel (Excel Query) - You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel. Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets… ...
Excel Pictograph – Charts with Pictures - Today something much lighter than usual, more to do with presenting data than just crunching it - How to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images! Images are Powerful Stuff! Our Brain processes Images 60 Thousand Times Faster than Text! For us Consultants, Marketers and… ...
VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA - Some VBA Macros run for ages - be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time -… ...
How to Find Duplicates in Excel. Remove Duplicates in Excel - How to find duplicates in Excel? Today we will explore this question in and out. We will start first by understanding How to Highlight Duplicates in Excel. Our second objective will be learning How to Remove Duplicates. I will explore several approaches to this task - using the Data ribbons Remove Duplicates feature, but also… ...
Excel VBA Last Row, Last Column, Last Cell - Today I am going to take on one of the most frequent question people ask about Excel VBA - how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I… ...
VBA FileDialog – Opening, Selecting and Saving files and folders - Often in VBA we need to ask the users to select files or directories before we execute the actual functionality of our macro. Welcome to the VBA Open file dialog post. Today we will learn how to use the Application.FileDialog, to understand the various msoFileDialogFilePicker file dialog picking options and how to properly manage these… ...
Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc. - Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets... and everything else you need to know about the VBA Worksheet in general. ThisWorkbook vs ActiveWorkbook Some Excel… ...
Excel WEBSERVICE and FILTERXML functions explained - The Excel WEBSERVICE and Excel FILTERXML Worksheet functions can be used to pull Internet data from a URL into a cell in a Excel spreadsheet. While the first pulls raw HTML data the second allows you to filter XML formats. Excel up till now has been mostly an offline application. Although, you can use VBA,… ...
Do you really need Visual Basic for Applications macro for that? - Visual Basic for Applications (abbrv. VBA) is often misused causing issues with maintaining code, backward compatibility with older Excel version and other issues. Reason being is often that some of us don't know, or better still, don't make the minimal effort to learn ready available tools in Excel or other Office applications. From time to… ...
Excel VBA Option Explicit – explicit variable declaration - The Excel VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do? What does Option… ...
Merge Excel files – How to merge multiple Excel files - Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that - how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in. Want… ...
Convert CSV to Excel – How to open CSV and save as Excel - CSV files are commonplace nowadays. Hence knowing how to convert CSV to Excel is a useful skill. CSV files are especially useful when wanting to save a table worth of data in a simple to read format. CSVs are also easy files to create and read from - as they are basically text files with… ...
VBA End – Exit or Reset VBA macro - Often we think about beginnings not endings. It is only when we get somewhere it is when we realize we hadn't thought through our destination. Same goes for writing VBA macros. Fortunately the VBA End statement is here to save the day and help us put that full-stop in the right place. Today I want… ...
VBA Substring vs Excel Substring – How to get a Substring in Excel? - Strings are not easy to manipulate and often we need to create a Excel Substring or VBA Substring. Excel and VBA in fact have multiple functions that support obtaining substrings. VBA Strings Manipulations Resources VBA SubString VBA InStr / InStrRev VBA Like Operator (Wildcards) VBA Regex VBA String Functions Excel vs VBA Substring functions In… ...
VBA Like Operator – Using Wildcards in Conditional Statements - The VBA Like operator is something so useful I am often surprised how rarely it is used in Excel and Access VBA. I often tend to see the Like operator as the last resort before using Regular Expressions in VBA. It replaces greatly the VBA InStr function when needing to check if a certain substring… ...
Word Translator AddIn and Excel Translator AddIn - Working in multinational organisations many of us struggle with ad-hoc translations of Word documents or Excel spreadsheets. Translations are a boring task. Usually what most of us have mastered is a simple process of copying texts from documents to Google Translate and back. If you have my VBA Time Saver AddIn you probably already have… ...
Merge CSV files or TXT files in a folder – using Excel or CMD - Often we face the challenge of having to merge csv files or txt files in a folder, into a single file. Excel is the obvious tool for such tasks and today I will show a couple of easy ways for merging multiple files, in a single or even a whole structure of folders, into a… ...
Excel VBA Range Tutorial - The VBA Range Object The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells. Contents Worksheet Range The Range is a Worksheet property which allows… ...
Making proper VBA Comments - Today we will focus on VBA Comments i.e. how to make comments in VBA or disable lines of macro code. Comments are needed in any script to explain what the code is supposed to do or provide some verbatim on certain lines / blocks of code. Scripts without comments are a nightmare for many of… ...
Excel VBA Function vs VBA Sub – Excel Functions and Procedures - A VBA Function can accept parameters and return results. Functions, however, can't be executed directly. On the other hand a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values. We often use Subs and Functions often not thinking about their true potential and how much we… ...
The VBA Type – VBA Custom Type alternative to Class - When thinking about custom objects / types in VBA most users turn to the VBA Class and create additional modules that quickly start cluttering their VBA Projects. Often these turn out to be simply structures, for the sole purpose of grouping several variables within a single object, bereft of any procedures or functions. VBA does… ...
Web Scraping Kit – use Excel to get that Web data - I am proud to present the next Kit coming from AnalystCave.com! The Web Scraping Kit is a simple kit for VBA Web Scrapers, contains a set of ready examples for different scraping scenarios. The kit is equipped with several tools letting you leverage HTTP GET&POST, IE, proxies, XPath, Regex and more Web Scraping tools. Get… ...
VBA Enum – using enumerations in VBA - Enumerations are often used in various languages to list certain variable variations within a single object to make them easy to find or list. The VBA Enum is a equally valuable construct - allowing you to clean up your mess by grouping a collection of constants into a single object you can easily explore. In… ...
The VBA Time Saver Kit – code snippets, VBA reference and examples - Introducing the VBA Time Saver Kit - the kit that will save you time when developing in VBA. Want to save time when developing in VBA? Building the VBA Time Saver Kit I had in mind creating a collection of useful code snippets available from this site - aggregated in common code modules available easily… ...
VBA Sleep vs VBA Wait – Usage and Differences - Idle time is often wasted time, however, sometimes you just need to wait for certain events to happen before you can continue code execution. VBA extends a couple of approaches to managing your idle time - the most popular approach is the Sleep procedure. The VBA Sleep procedure pauses code execution for a certain amount… ...
Add an Image from VBA in Excel - Images are what enriches our content, visualizing data enables us to compare results, notice patterns and provide insights. Most Excel files are bereft of any images, presenting raw data supported by boring charts. Dashboard often could use a couple of images here and there to visual a metric or trend. Today shortly on how to… ...
VBA is dead? What’s the future of VBA? - As with most technologies Visual Basic for Applications (VBA), the scripting language available with most MS Office applications, is deemed by some to be already Walking Dead and soon to be deceased. VBA is a versatile and easy to use programming language allowing you to automate tasks and create simple MS Office applications. Visual Basic… ...
VBA MsgBox vs InputBox vs UserForm - One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and… ...
VBA InputBox – How to request user input? - The VBA InputBox is a very useful VBA user input feature along the VBA Message Box, before needing to reach out for VBA UserForms The InputBox allows you to create a simple pop-up message with a single user input textbox. A disclaimer to begin with: Refrain from using the InputBox for yes/no, binary questions, as… ...
Web Scraping Proxy HTTP request using VBA - Visual Basic for Application (VBA) is great for making your first steps in Web Scraping as Excel is ubiquitous and a great training arena to learn Web Scraping. Web Scraping comes down to making HTTP requests to websites. At some point however you will find that some websites will cut you off or prevent multiple… ...
Excel Calendar Generator – generate calendar in Excel VBA - It is 1 day before the end of 2015 hence I thought it might be interesting to create an automatically generated Excel Calendar using VBA that I could populate with holidays, anniversaries and other recurring events. But why repeat this manual process every year when we can force Excel VBA to do the work for… ...
VBA Dir function – How to traverse directories? - The VBA Dir function is crucial to exploring any file system with the use of VBA - whether in Excel or other MS Office applications. In some case true you can resort to a Select File Dialog, however, in other cases you need to be able to easily match files or directories in file systems… ...
VBA Select Case – all you need to know - Today's focus is on the VBA Select Case statement, which is often comfortable to use when you need to execute different code depending on the value of a certain expression. Usually you won't be using Select Case when you need to differentiate between less than 3 specific values of an expression. In this post I… ...
VBA For Loop vs For Each Loop - VBA For Loops are the basis of VBA automation. The VBA For and For Each loops are the most frequently used types of loops for repetitive procedures. In this post let's learn all there is to know about the VBA For Loop and the VBA For Each Loop, the advantages and the disadvantages of both.… ...
Excel to PDF exporter (single / batch export) - Today a simple use case example - Excel to PDF exporter using Excel VBA. PDF is the current standard printing format. Saving an Excel file to PDF is not hard feat, however, sometimes you need to repeat this operation multiple times or even have a whole lot of Excel documents that need printing. Why do… ...
VBA Searching strings using InStr and InStrRev - String manipulation is a key skill in VBA and other programming languages. Today let's focus on using string searching functions to extract SubStrings from other strings using the VBA InStr or the VBA InStrRev functions. VBA Strings Manipulations Resources VBA SubString VBA InStr / InStrRev VBA Like Operator (Wildcards) VBA Regex VBA String Functions VBA… ...
Scrape Google Search Results to CSV using VBA - Google is today's entry point to the world greatest resource - information. If something can't be found in Google it well can mean it is not worth finding. Similarly SEO experts blog and write about how to optimize your web pages to rank best in Google Search results often ignoring other search engines which contribution… ...
VBA ActiveWorkbook vs VBA ThisWorkbook - ThisWorkbook refers to the workbook in which Excel VBA code is being executed. ActiveWorkbook on the other hand refers to the Excel Workbook that current has focus, meaning is the front facing Excel Window. Often Excel VBA Developers mix these two common types of Workbooks in VBA. The problem starts to arise when you work… ...
The VBA ParamArray for a dynamic list of VBA arguments - Today's VBA Tip of the Day concerns dynamic parameter declaration via the VBA ParamArray. When declaring Functions/Procedures in some cases you may need to specify Optional arguments. Let's say we want to write a procedure that works similarly as other procedures you know natively from Excel like SUM: Now how would you create such a… ...
VBA Collection Tutorial - The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. As Microsoft documentation explains Collections are objects containing an orders set of items. They are more flexible than VBA Arrays as they are not limited in their size at any point in time and don't require… ...
Shorter VBA code! - Today let's expand on how to create shorter VBA code that is easier to read and manage. Writing VBA code that works is usually the tip of the iceberg for most. However, more importantly it is useful to be able to write VBA code that is clear and concise. Shorter and concise blocks of code… ...
Proper VBA error handling - Writing VBA code is hard, but properly debugging code is even harder. Sounds like non-sense? Well I dare say developers spend more time debugging code than writing it. Looking for errors is what developers do most of the time! A critical part of debugging is proper error handling (VBA error handling in our case). Debugging… ...
VBA Open Workbook and other VBA events - Often when working with Excel files we want to introduce certain features which are supposed to be fired in the circumstance of certain events. Say we have a Excel worksheet that is time-sensitive, that needs to be updated based on some web-resource or database. Should we hope that the user will leverage our Refresh button.… ...
How to use VLOOKUP in Excel! - The Excel VLOOKUP function (Vertical Lookup) is one of need-to-know features in Excel! I can't stress enough how many articles/posts out there have been published on using VLOOKUP. Probably rightly so. I couldn't however resist to make an my attempt at consolidating all the knowledge around how to use VLOOKUP and all the bits and… ...
The VBA Array tutorial - The VBA Array is a very convenient and efficient for storing multiple items of usually the same data type. The size of a VBA Array can be either fixed or dynamic depending on how it is declared. Arrays can also be 1 or multi-dimensional. In some cases however you might be better of considering other… ...
Web Scraping Tutorial - For years I have been reaching out to Web Scraping in order to download / scrape web content, however only recently have I really wanted to dive deep into the subject to really be aware of all the techniques out there. Ranging from the simple Excel "From Web" feature to simulating browser interaction - there… ...
Data Analysis Excel Tools - Excel is most appreciated for it's ease of use as a Data Analysis Tool. I mean to explore the basics, as well as the more advanced Data Analysis Excel Tools. Be sure to read through the Other Tools section below for other honorable mentions. The DATA ribbon tab As some of you already have noticed… ...
Automatic Excel Autofilter - With today's Excel tip of the day let's have some fun with learning how to create an auto-applying filter to an Excel Table (or range). Let's jump right to see how we expect our Excel Autofilter to work: We want to achieve the following behavior where our Table column is filtered as we type. This… ...
VBA Class Tutorial - The VBA Class allows you to define your own objects with unique properties and methods in VBA. Classes in at the core of all Object Oriented Programming languages. Although we can argue how much is VBA actually an Object Oriented Programming language, there is no doubt that VBA allows you to create Classes similarly as… ...
How to Debug VBA. Debugging VBA in Excel - Writing Visual Basic for Applications code is hard, but what about writing VBA code that works and to write it fast? Often I found many colleges struggling to get a few simple procedures to work. I was amazed that most of them preferred to keep at it, trying to pin down the one line of… ...
Merging worksheets / tables in Excel - Often we need to merge worksheets / workbooks containing lots of data. Imagine receiving periodical daily reports and wanting to quickly consolidate them to generate a weekly or monthly report... seems like a lot of work. Merging worksheets does not necessarily need to be hard... as long as you read through today's post. Looking to… ...
Excel Cascading drop-down (no VBA!) - Cascading drop-down are a very useful feature in Excel making it much easier to categorize your records. Say you have a list of records you want to associate with categories and subcategories. Normally you would start by assigning a category to each record and then have a problem with matching a subcategory. What is a… ...
Excel Pivot Table Tabular layout - Pivot Tables are a fantastic tool for slicing and dicing data. Sometimes, however, you need to spend quite a lot of time to achieve the right layout for your data. Today a short tutorial on how to achieve a pure pivot table tabular layout. Enjoy. Tabular Form vs Compact Form Let's assume we have a… ...
Optimize Excel formulas with the Excel Optimizer! - Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel… ...
Excel VLOOKUP vs INDEX MATCH vs SQL vs VBA - VLOOKUP vs INDEX MATCH vs SQL vs VBA - today you are in for the ultimate Excel Showdown. The VLOOKUP Excel function is one of the most popular functions, around which there has always been much debate. You will most definitely find an article about this function on almost every Excel blog site out there… ...
Native VBA Multithreading? There is only 1 but… - First I am sorry for making another post on VBA multihreading. Having written so many posts already it seems I am ranting on an on about this topic. I must subdue, however, and write shortly on one new way of almost Native Multithreading VBA using native kernel32 functions. If you've been around my blog you… ...- Excel VBA Cheat sheet - Welcome to my end-to-end VBA Cheat Sheet. Be sure to bookmark this page as your Excel VBA cheat sheet!. Visual Basic for Applications makes automation possible in Excel and other Office applications. The below Excel VBA Cheatsheet is your one stop shop for a variety of useful VBA automations. If you are new to VBA… ...
Working with XML files in VBA (VBA XML) - XML files are one of the most common type of data files apart from text and CSV (comma-separated values) files. Reading data files which are not hierarchical (as XML files or JSON) is relatively easy. You can read in the data row by row and process columns separately. With XML (and JSON) the task is… ...
Using PageSpeed Insights API with Excel VBA (VBA PageSpeed) - Google API are very useful and easy to use. Especially since queries are sent via GET and results are provided in JSON which is easy to scrape, from which data can be easily extracted. I always like to play with Google API as Google has such a vast range of different API addressing almost any… ...
Measuring CPU usage in Excel VBA (and other performance metrics) - VBA easily allows you to get your hand on your PCs performance metrics which often can prove useful when wanting to do some performance statistics. Having been playing with the VBA Multithreading Tool I wanted a way to see how well all my processor cores are utilized - especially when wanting to optimize the amount… ...
Reverse engineering an Excel PivotTable / Flatten Excel PivotTable - Today a quick tip that helped me out of a tight spot during one of my projects. The issue was that I received an Excel file with multiple PivotTables, but no source tables on which these PivotTables were built (these were in separate worksheets that were not shared). I needed the source data to produce… ...
Excel XLSB vs XLSX file format. The Pros and Cons of XLSB Files - An XLSB file is a Microsoft Office Excel Binary file. Information inside XLSB file is stored as binary data compared, while in usual XSLX files data is stored as XML files which are then compressed. Working with large Excel files is often a drag. They open slower, they take an eternity to save and they… ...
VBA Dictionary – Using the VBA Dictionary. Key Value pairs - Visual Basic for Applications (VBA) facilitates many useful data structures - its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList. But before we jump into explaining how these more complex data structures work and how to use them, let's start… ...
Programming languages analysts should learn - Programming is a skill often associated only with Developers and not so much with analysts or data scientists. Why learn to code when you have tools such as Excel with it's PivotTables, filtering and formulas. On the other hand, when thinking about analytic tools it seems like Tableu, ClickView, SAS or IBM SPSS is the… ...
Saving your VBA data (VBA data dump) - Sometimes VBA algorithms take a long time to execute and you would want to be able to maintain those calculations in memory when you close and reopen you Excel Workbook or Access Database. Why not save your VBA project data and recover it when reopening your VBA project? Actually you can do just that... Saving… ...
Excel Regex Tutorial (Regular Expressions) - Regular expressions are ubiquitous in the developer world. They are used to validate website input, search for word patterns in large strings/texts and for many other uses. In Excel, Regular Expressions (VBA RegEx or simply VBA Regex) are not much advertised. Most users are good with using simple LEFT, RIGHT, MID and FIND functions for… ...
Writing files in VBA (txt, xml, csv, binary) - Continuing on the topic of working with files using VBA. Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Other VBA File resources VBA File Functions VBA Reading Files VBA Writing Files Traversing Directories VBA Working with XML files VBA Downloading files VBA Versioning files VBA Merging CSV… ...
VBA Read file in Excel (txt, xml, csv, binary) - You can VBA Read file binary or text data using a couple of different approaches in Excel. VBA provides you a set of native statements like Open to open and ready files. However in this article aside from showing you these native approaches to reading files using Excel Macros you can read CSV files and… ...
Easy PowerPoint Timer - PowerPoint presentations often fail to be delivered well, due to presenters exceeding their time spent on single slides or simply. If you spend more than 5 min on a single slide you are at high risk of boring your audience to death ("death by slide"). Focusing on keeping your pace is not always an easy… ...
Mastering the VBA MsgBox - Today shortly on how to master the Excel MsgBox. Although, the MessageBox is a common control used in most Window applications many VBA developers underestimate its usage - often making custom UserForms for tasks where the Excel MsgBox could just as well do the trick. An example? User Interaction - when user input is needed… ...
Making a VBA Automated Survey in Word - Recently I stumbled upon an issue of creating a quick survey for clients/users. Usually you would want surveys set up in a dedicated web application like Google Forms or Survey Monkey. However, in this case I was limited to MS Word - the doc / docx survey was to be sent via email to clients/users… ...
VBA Download Files – download files in Excel using VBA - Today's post will touch on something most analysts and VBA developers might find useful: downloading files using vba from the Web (VBA Download). Excel VBA again proves to be a versatile tool for Analytics - in this case for extracting and storing data. Downloading files can be a useful way to save data extracted from… ...
Dynamic Distinct Column in Excel using Array Formulas - I see that often many users have issues with managing lists of values and translating them to unique/distinct lists where values do not repeat themselves. There are many way to tackle this problem and you would be surprised that there even is a formula to handle this task. Let's dive into this subject. What we… ...
Top 10 Excel Features – Most Useful Excel Features - Today on Top 10 Excel features you need to know. I am a frequent visitor to StackOverflow to see what is troubling the Excel community (and other developer/analyst communities). It is nothing incredible that many users have issues due to not being aware of some of the most significant features in Excel like PivotTables, Array… ...
Dynamic Row Numbers in Excel - Today I want to elaborate shortly on how to correctly and easily number rows in Excel by adding dynamic row numbers using simple formulas. Every neat data table in Excel should have a numbering column in place so that every row can be easily reference at least by the item number. One way of numbering… ...
Use Form Controls to make Dynamic Excel Charts (no VBA) - Dynamic Excel charts are so much appealing to users and more pleasant in interaction. Fortunately Excel has PivotCharts and Slicers. But what if you want to have more control over your charts and use other controls such as Dropdowns, ScrollBars and Radiobuttons? Today I will show you how to easily use Form Controls to add… ...
Automatically save Lync conversations (when feature is blocked) - Honestly, I cannot seem to explain why do some corporations block the Save IM conversations in my email Conversation History folder. What is more funny is that blocking this feature (at least in Ms Lync 2013) will not prevent you from saving your conversations manually (hit CTRL + S). Unfortunately there is no way to… ...
Word VBA Tutorial - Welcome to the Word VBA Tutorial. VBA is a great tool not only to be leveraged in MS Excel. Often it is worth to save some time doing repeatable tasks by adopting some VBA macros in Word or PowerPoint too. Today I wanted to focus a little bit on starting you off in Word VBA… ...
Web Scraping Tools for Beginners and the Advanced - Web Scraping is a very wide topic and almost a separate profession. It is especially a valuable tool for SEO specialists, data scientists, analysts and many others. Due to this there are tons of tools out there. Trying to find the right one can be a real nightmare. For those that don't have the time… ...
How to Split Cells in Excel. How to Split Excel Columns. - The easiest way on how to split Cells in Excel or split Columns in Excel, is to select the column you want to split. Next go to the Data ribbon and hover to the Data Tools group. Next Select Text to Columns and proceed according to the instructions. The above works for simple splits on… ...
Like VBA? You will love HTA! (HTA example using VBS) - Comfortable with VBA? Your clients/users need VBA solutions but want them to be lightweight GUI, not cumbersome Excel Workbooks with Macro restrictions? Well why not try HTML Application (HTA)? What's HTA? It's a simple HTML application embedded with Visual Basic script or Javascript, or even both if needed! Don't see the opportunities yet? Imagine a… ...
Multithreaded browser automation (VBA Web Scraping) - Web Scraping is very useful for getting the information you need directly off websites. Sometimes however simple browser automation is not enough in terms of performance. Having created both the IE and Parallel classes I decided to jump an opportunity of creating a simple example of how mulithreaded browser automation can be achieved. Daniel Ferry… ...- Simple class for browser automation in VBA - Web browser automation (using Microsoft's Web Browser) is not an easy task in VBA when doing some Web Scraping. Excel is certainly a great tool for building your web automation scripts but at every corner you find some obstacles. For me the most challenging was always the nondeterministic state of the IE browser control whenever… ...
Guide to Improving VBA Performance. Faster Excel VBA - There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works. That is why I gathered all VBA performance guidelines into… ...
Excel VBA Multithreading Tool - The VBA Multithreading Tool, from AnalystCave.com, is a free and open source tool (VBA) for enhancing your VBA Project with Mulithreading capabilities. It was designed to make VBA multithreading quick an easy and to minimize the complexity of your VBA code. VBA is natively singlethreaded hence will utilize only a single logical processor core. Why… ...
Invoke VBA functions and procedures from strings - First let me start with explaining what does it mean to invoke VBA functions and procedures from strings. Then I will follow with an example. Lastly I will conclude this topic showing how you can add arguments. Invoke VBA functions and procedures by string name Normally you would invoke a VBA function or procedure like… ...
Personal budget calculation in Excel - Today I decided to share something on the topic of managing your personal finances. Everyone should once in a while do a personal budget calculation. If you do not know how much you earn and spend it is a real challenge to manage your personal finances. How to estimate which car you can afford? How… ...- Multithreading VBA – VBA worker threads via VBscript - Yet again I am approaching the subject of multithreading in VBA. This time I am coming with an interesting alternative to multithreading in VBA compared to the ones I presented previously. So again let me start by repeating that multithreading is possible in VBA but not in "native" VBA. What do I mean by "native"?… ...
- Multithreading VBA – VBA vs. VBscript vs. C#.NET - I you were looking for an end-to-end analysis of the topic of multithreading VBA. You have come to the right place! So lately I have been really wearing out the topic of multithreading in Excel in VBA. Hopefully you have already read through my three posts on the various methods of achieving multithreading capabilities in… ...
- Multithreading VBA using VBscript - Recently I published the post on using C#.NET to do multithreading in VBA - find it here: EXCEL: Multithreading in VBA using C#.NET. In that post I stated that multithreading is not possible natively in VBA only using external libraries e.g. COM/dll in C#.NET. In result of that post my attention was drawn to an… ...
Multithreading VBA using C#.NET - Multithreading in VBA in Excel is possible! No wait... that can't be true right? Almost everyone knows that VBA runs in a single thread. So am I kidding? Not quite! Although this may seems like a workaround there is actually a way to multithread your VBA algorithms. There is only 1 condition... you must be… ...
Using C# in Excel VBA - Today's topic concerns using C# from within VBA code(Excel C#). Ever had a C#.NET library you wanted to use in Excel VBA (Excel C#)? Well, as a matter of fact it is possible using the Visual Studio Nuget Unmanaged Exports package. Now why on earth would you want to use C# in Excel by using… ...
Send email from Excel using VBA - Tired of writing tons of similar emails to your recipients? Preparing a personalized newsletter in Excel? Sending an email from Excel is a useful feature in case you need email notifications or you are building a mailing list in Excel. Below find a quickly drafted method for sending emails from your Outlook application. The prerequisite… ...
Lync Spy – monitor and get notified of anyones status changes - Lately I was having issues with getting in contact with some of my colleagues. The problem was that I could not contact them by phone and Lync (now Skype for Business) / Email were my only 2 options. Sending emails is not a quick way of contacting anyone and unfortunately you can't send a Lync… ...
Creating a dynamic named range in Excel - When making complex Excel solutions is often the case that you will need to manage many different lists of certain values e.g. for dropdowns, formulas etc. It is a real pain to manage lists that change very often. The clean solution is to define a dynamic named range which will adapt to the list of… ...
Export Excel to HTML – convert tables to HTML - I received an interesting question today - on how to easily publish an Excel file to a web page. Although there are a ton of ways to approach this problem (ranging from Excel Services in SharePoint to Excel Interop or ClosedXML) let us say we want to restrict to using only Excel and VBA. Printing… ...
Schedule VBA Macros to run periodically via VBscript - Some time ago I posted an article on how to run an Excel VBA Macro using VBscript and cmd to save time or to be able to run multiple Macros across a number of Excel files without actually needing to open them. Well, I decided to extend this example to allow periodic execution of Excel… ...
Automatic Updating of Excel Worksheet - Introducing the new extension to the Scrape HTML Add-In: Automatic Updating of Excel Worksheets. When configuring your Excel file to scrape data of HTML websites there is often the issue of how to refresh this data periodically. For example - you want to scrape stock price data and refresh it every 2 minutes. You can… ...- Save and Get Excel VBA settings - Ever had an issue of sharing data (VBA settings) between your workbooks? Not many Excel VBA users know that VBA allows you to easily save and certain variables to and from the Windows Registry, just as if setting permanent VBA settings within your VBA Project. What is more this is already built into VBA that… ...
Excel VBA OnTime Function – Make a VBA Alarm Clock - One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime function. It allows you to execute any Excel VBA procedure after the specified amount of time (delay). Using it recursively allows you to create a recurring / repeating event which lets you… ...- Excel Scrape HTML Add-In now with HTML caching - I am constantly extending the Scrape HTML Add-In when I have some spare time. Today's update adds Caching to the Get* functions. See the latest version of the Excel Scrape HTML Add-In here: Excel Scrape HTML Add-In Why caching? The primary concept of the Add-In is to reduce any need for writing VBA code. However,… ...
- Running Excel VBA from VBscript - Today's post is going to be very niche. But as I am often bored lately and playing around as a Data scientist in the Kaggle contests I occasionally run into these strange places where I need to make some peace of ultra-efficient code and needing to automate as many things possible. Run VBA from VBscript… ...
- Excel Scrape HTML Tool added to the Scrape HTML Add-In - As I mentioned I am planning to extend the Scrape HTML Add-In with additional tools and functionalities. Scraping HTML content should not require any VBA coding - this rule is guiding the further development of this tool. See the latest version of the Excel Scrape HTML Add-In here: Excel Scrape HTML Add-In Adding to the… ...
- Excel Regex Tester Tool - I have recently added the Regex Tester Tool to the Scrape HTML Excel Add-In. Also as some might have noticed I decided to consolidate my HTML scraping UDF functions into the new Scrape HTML Excel Add-In. What I was missing when using the UDF functions to scrape HTML content was some tool to test my… ...
- Excel Scraping HTML by Regular expression continued… - After my post on "SCRAPE HTML BY ELEMENT ID, NAME OR… ANY REGEX!" I have been thinking about tinkering the macros a little bit more to make scraping HTML content even easier and reducing any additional needs for writing VBA code. What was missing in the puzzle was additional parsing of the scraped content i.e.… ...
Versioning Excel files with Excel VBA - Usually when developing Excel solutions you want to version you file often to prevent data loss due to the application crashing etc. You will probably also want to keep the older versions of you files to be able to go back and recover any previously working code. When you do this once or twice in… ...
Animated VBA Progress Bar for Excel and Access - A VBA Progress Bar can be used to manage your users anxiety about the execution time of your VBA Macro. Sometimes there are very large and complex solutions built in Excel (which is a mistake mind you), where calculations or macro executions can take minutes or even hours. This causes many issues, especially for the… ...- Excel Scrape html by element id, name or… any regex! - Sometimes I have a need to quickly scrape some data from website to be able to work on them and update their values when needed e.g. stock prices, temperature, search results, statistics etc. From time to time I stumble upon similar issues. Below find 2 quick UDF functions (user defined functions) that you can use… ...
Start your Kaggle journey with R - As mentioned in my previous post I started playing with Kaggle competitions, which pushed me into learning R. What I want to emphasize is that you do not have to be a data scientist to start in a Kaggle competition. Some programming/analytical skills are however more than welcome. Generally as I see it Kaggle competitors… ...- Add the R language to your analyst toolbox today! - Recently, having some time on my hands, I stumbled on Kaggle. A site for data scientist and other enthusiast programmer who want to test their skills in building the best machine learning models. It seemed to me at first like something for only a small community of people. However, as it turns out most machine… ...
Excel Google Charts Tool - I always wanted to utilize the beautiful and interactive Google Charts in Excel. The Google Charts repository is constantly growing and sometimes Excel lacks those features. Hence I introduce the Excel Google Charts Tool to display a way to leverage some of those Google Charts directly in Excel. The WebBrowser control is no longer supported… ...
Using SQL in VBA on Excel. Run SELECT Queries from VBA - Many times I was irritated of the lack of some Excel functionality (or just I don't know there is) to easily transform data w/o using pivot tables. SQL in VBA was the only thing that was missing for me. Distinct, grouping rows of Excel data, running multiple selects etc. Some time agon when I had… ...
Get Outlook recipients information via VBA (Outlook users data) - Being a corporate Consultant I always thought of the vast amount of data stored in various corporate databases that could be put to good use. Lot of data is stored and is accessible via Outlook - although mostly not appreciated. I once received an assignment which required analyzing the data of all employees in our… ...
Excel Google Translate functionality - Translations are not a fascinating task for me. I once received a request to help out a colleague in translating an Excel file to English. Copy to Google Translate, translate, copy back to Excel and again... After going through part of the file we quickly extrapolated that the whole translation would take at least a… ...- Get Google Maps address coordinates (latitude & longitude) in Excel VBA - Continuing my last post, on how to get the distance between any addresses using VBA, I wanted to add a capability to my previous Excel file that would allow me to get any address coordinates. How to quickly get the lat. and long. of an address? Again why not use Google API... Address coordinates in… ...
Excel VBA Calculate distance between two addresses or coordinates - Ever wanted to calculate the distance between two addresses in Excel? I recently had the following issue: from a list of over approx. 50 administration offices across my city I wanted to find the one that is closest to my workplace. Of course open Google Maps and type each location one by one... and then choose the… ...
Download VBA Time Saver
Ready VBA Code snippets for Arrays, Strings, File Dialogs, Read/Write Files, Timers, Progress Bar
Download LOOKUP Wizard
Lookup data from external Excel files and worksheets with the Excel LOOKUP Wizard AddIn!