A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.
The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.
To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks. Continue reading →
When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.
Finding the Max and Min Values
To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart.Continue reading →
In this blog post we will explore how to pick names at random from a list using Excel VBA.
Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.
These names must be selected at random and be unique. You cannot select the same name more than once.
The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.
This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. Continue reading →
If you are responsible for analysing a list of URL’s in Excel, then you may need to extract the domain from each URL. After extracting the domain you can perform useful reporting tasks such as grouping the domains, or counting the number of unique domains in the list.
This can be quite a difficult task especially if the URL’s are not consistent. In this article we explore a few different approaches to extract the domain from a URL. The method you choose will depend on the appearance of the URL’s in the list and the format you would like to extract the domain in.
This article covers;
Extract the domain including the URL protocol.
Extract the domain ignoring URL protocol.
Extract the domain without the www.
Extract root URL’s that don’t end with a slash (/).
Using helper columns to break up complex formulae.
This blog post looks at some productivity hacks for creating professional, consistent and awesome MS Word documents.
These 10 skills are sure to save time and improve overall efficiency in MS Word.
1. Quick Parts and Building Blocks
Building Blocks is a fantastic feature in MS Word where you can store frequently used content for quick insertion to a document.
There are many galleries to the Building Blocks feature including cover pages, tables, headers, footers and text boxes. Quick Parts is the gallery for miscellaneous content that does not fit any of the other galleries.
For example, say I work for a company with 4 different offices. I frequently have to enter the address of these offices into documentation. This entails looking up the correct postcode etc. and then typing and formatting it. With Quick Parts these can be stored for easy re-use. Continue reading →