Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Mynda wrote a post about line charts vs dot plots where one of the steps in setting up the dot plot was to format the series markers, by hand 🙁 . Not surprisingly we had a few people ask if there was a quicker way to do this formatting, and Mynda volunteered me to write …
My last post looked at web scraping using VBA, where I loaded a single page and extracted content. In this post I'm looking at loading multiple pages from a site and getting the content I want from each page. Related Topics Web Scraping – Filling in Forms Web Scraping With VBA Let's say I want …
We can use VBA to retrieve webpages and comb through those pages for data we want. This is known as web scraping. This post will look at getting data from a single web page. I've written another post that deals with getting data from multiple web pages. Related Topics Web Scraping – Filling in Forms …
Let's say you have a range that you want to convert to a table Something like this that has fills, font colors, font weight, borders and number formats. If you convert this to a table this formatting remains. To remove this formatting and preserve the table formatting, we can use some VBA code. Download the …
If you end up with a workbook that contains custom styles, removing all of these may be a pain depending on how many there are. With a little VBA we can easily list and delete all custom styles. In my workbook I have three custom styles, Cool Style 1, Gaudy Style and Sunglasses Required. The …
If you've written subs or functions in your own add-in, you can use them in your worksheet as you would with any other sub/function. Subs can be called using shapes or buttons, the QAT, the Ribbon, or from the list of macros brought up by ALT+F8. Functions can be called by typing the function name …
We had a post on the forum recently asking how to create a timer in Excel, to record how long things took to happen on a model railroad. You can use the same code to time anything of course, like how long your code is taking to execute. A follow up request asked for code …
Excel has the concept of volatile and non-volatile functions. Microsoft define a volatile function as one that is recalculated every time calculation occurs in any cell in the worksheet. There's actually a bit more to it than that but for the sake of keeping it simple we'll go with that definition. A non-volatile function is …
My last post looked at using Python to plot equations in Excel with DataNitro. In this one I want to look at writing UDFs in Python.with xlwings. Why Use Python for UDFs ? This is a question not just about UDFs but why use Python at all in Excel? I guess this depends on what …
Before VBA there were XLM macros. VBA was introduced in Excel version 5, so these Excel macros are also known as Excel 4 macros. Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a …
