Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
To start with let's understand that I'm talking about the 'old' style comments like the one on the left. In Office 365 we now have Threaded Comments which are also being referred to as comments, with the old style comments being referred to as Notes in O365. But if you are not using O365 then …
If you are working with several variables that relate to one thing, for example you are working with employee data, and every employee has a first name, last name, location and salary, you could set them up like this But if you want to work with data relating to several employees then you're going to …
I've written about web scraping before using VBA, but never about interacting with the website to do things like filling in forms. We had a forum question asking how to do exactly that so I tried using the same approach as I had previously with the HTML Object Library, but when it came to grabbing …
If you've worked with merged cells you may know that they can cause issues with things like copying and pasting, sorting, and counting cells. Merged cells can cause VBA to fall over too, so it is best to avoid them. Using Center Across Selection is a better option. CTRL+1 is the shortcut to open Format …
Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a value after your code has finished. Let's say you want to keep track of the number of times something happens, like how many times someone clicks on this button they are not supposed to.
If you need Excel to run some VBA at a specific time, or repeatedly at set intervals, you can use the Application.OnTime method. A basic call to Ontime requires that you supply a time when you want the code to run, and the name of the macro you want to run. The argument EarliestTime is …
RegEx stands for regular expression and is a sequence of characters that defines a pattern that can be used to match against strings. Or, in simpler language, I can create a regex pattern and use it to search a string for that pattern. I can also use the regex pattern to validate input. A simple …
The VBA Like operator allows you to compare strings against a pattern. You can search a string to check if it contains another string, or you can check if a string conforms to a certain format. By searching if a string contains another string, Like does a similar job to the InStr string function, but …
We've had a few forum questions recently asking how to play audio files in Excel. Normally I'd just use my media player for this but I can see that you might want to have an audio or video file that may explain something about your workbook. Or you could use Excel to play videos in …
You might want to save your charts as images that can then be used in other applications, like email or displayed on a website. Selecting a single image then copying/pasting or saving it, isn't too much bother. But if you want to do that for several images it quickly becomes tiresome. I've written some VBA, …
