AnalystCave Post Archive

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 4.00 out of 5)
Loading...
  • Unhide Sheets in Excel 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 ...
  • Excel AutoFit Excel Rows and Columns 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 ...
  • excel keyboard shortcuts 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 files header 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. [crayon-5c17f9f0ee31d385143361/] Other VBA File resources VBA File Functions VBA Reading Files VBA Writing ...
  • VBA Tricks 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 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 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 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 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. We will start with the basics and then move on to more elaborate examples and uses of the ...
  • excel vba colorindex 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... The VBA ColorIndex property The VBA ...
  • vba ftp 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 map 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 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 vba camera tool 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 Let us start with exploring how to ...
  • excel count formula 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 an MS Query in Excel 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 featured 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 alerts and notifications 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 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 ...
  • vba last row featured 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 ...
  • application filedialog featured 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 tutorial 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 Lets start ...
  • excel webservice function Excel WEBSERVICE and FILTERXML functions explained - One of the incredible new features coming to Excel 2013 are the Excel WEBSERVICE and Excel FILTERXML Worksheet functions. Excel 2013 introduces over 50 new Worksheet functions but I will tell you why the 2 above-mentioned rock my world ever since I learn of them. It is incredible that the introduction of this awesome function ...
  • visual basic for applications 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 ...
  • vba option explicit 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 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 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 ...
  • excel substrings 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 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 translate addin 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 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 ...
  • vba excel range object 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 ...
  • VBA Comments Making proper VBA Comments - Today's Tip of the Day will focus on VBA Comments, how to make comments in VBA and how to make the most of them. 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 ...
  • VBA Function vs VBA Sub 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 ...
  • VBA Type The VBA Type – custom variables without procedures - 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 ...
  • VBA Web Scraping Kit 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 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 ...
  • VBA Time Saver Kit 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 VBA Sleep vs VBA Wait – Usage and Differences - Idle time is often wasted time, however, sometimethirs 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 ...
  • vba image 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 Poll 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 ...
  • MsgBox vs InputBox vs UserForm 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 VBA InputBox – How to request user input? - The VBA InputBox is a very useful 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 shown above. Use ...
  • vba proxy server 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 VBA Calendar 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 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 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 vs For Each VBA For Loop vs For Each Loop - 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. Want to ...
  • Excel to PDF 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 InStr Tutorial 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 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 ...
  • ActiveWorkbook vs ThisWorkbook 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 ...
  • VBA ParamArray 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 VBA Collection Tutorial - The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. VBA Collections are more flexible than VBA Arrays as they are not limited in their size at any point in time and don't require manual re-sizing. Collections are also useful when you don't want to leverage ...
  • Short VBA Code 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 ...
  • VBA Compiler VBA Compiler Add-In (to VB.NET) - Lately I have been working hard on a completely new project - compiling VBA to VB.NET (VBA Compiler). If you've had your way around my blog a little bit you might notice I am little obsessed with VBA code performance and VBA multithreading :). Well... I always had a soft spot for .NET in my ...
  • vba error handling 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 events 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 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 ...
  • vba array 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. VBA Arrays can also be 1 or multi-dimensional. Navigate Other VBA Data Structures VBA Array VBA Collection VBA ...
  • scrape html add-in 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 ...
  • excel data analysis 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. Data Analysis Excel Tools Sort & Filter PivotTable What If Analysis ...
  • autofilter featured 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, Custom Classes and Objects - 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 in Java or C#. But what is a Class? A Class allows you to encapsulate any level ...
  • debug vba featured 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 ...
  • merge worksheets 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 ...
  • cascading drop-down 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 ...
  • PivotTable 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 ...
  • Excel Optimizer 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 ...
  • VLOOKUP 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 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 ...
  • VBA XML 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 ...
  • VBA PageSpeed 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 ...
  • CPU Usage 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 ...
  • pivot table data Reverse engineering an Excel PivotTable (recovering Pivot Table data) - 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 ...
  • xlsb vs xlsx XLSB vs XLSX. 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 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 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 ...
  • vba data 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 ...
  • regex 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 ...
  • vba write file 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 VBA Read file in Excel (txt, xml, csv, binary) - You can VBA Read file binary or text data using using the Open operator. For CSV files and other structured data schemas you can use the Jet.OLEDB driver or Microsoft Queries. Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Looking at various resources I missed a single ...
  • powerpoint timer 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 ...
  • vba msgbox 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 ...
  • survey in word 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 ...
  • excel download 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 ...
  • distinct list 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 ...
  • Excel Top 10 Features 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 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 ...
  • dynamic excel charts 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 ...
  • save lync conversations 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 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 Tutorial 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 ...
  • split cells in excel 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 ...
  • hta example 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 IE automation 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 ...
  • hta example 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 ...
  • VBA performance 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 ...
  • vba multithreading tool 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 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 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 ...
  • Native VBA Multithreading 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"? ...
  • Native VBA Multithreading 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 ...
  • Native VBA Multithreading 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 ...
  • excel c# multithreading 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 ...
  • c# excel 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 ...
  • email from excel 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 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 ...
  • excel dynamic named range 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 ...
  • Excel to HTML 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 ...
  • vba with vbscript 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 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 ...
  • vba data 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 ...
  • vba application ontime Excel VBA Application OnTime – Scheduling VBA Procedures - One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime event. 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 ...
  • scrape html add-in 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, ...
  • invoke vba 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 ...
  • scrape html add-in 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 ...
  • regex 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 ...
  • scrape html add-in 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 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 ...
  • vba progress bar Animated VBA progress bar for Excel and Access - 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 end users who usually do not know how long processing the calculations/macros will take. In such cases it is important ...
  • scrape html add-in 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 ...
  • map 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 ...
  • SQL in VBA 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 ...
  • outlook users 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 ...
  • google translate 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 ...
  • map 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 ...
  • google maps distance 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 ...