Struggling To Excel

I work on Excel a lot, and I realized that writing a few lines of code could save me the trouble of doing mundane procedures repetitively. I intend to post the Functions, Procedures, Tools and Add-ins that I write, in this blog, so fellow strugglers like me can benefit from my experience. I also hope to get help from experts and improve my codes through this blog. I expect it to take a while before you find this blog useful. At the least, this will be a fun way to store my codes.

[strugglin2xl]

English Ordinal Indicators in Excel using VBA

I often require to get the ordinal indicator (st/nd/rd/th) associated with a number, especially when I am working with dates. In this post, we will learn to write a simple user defined function in VBA to identify the ordinal indicator. #Excel #VBA

I often need to determine the ordinal indicator (st/nd/rd/th) associated with a number, especially when I am working with dates. In this post, we will learn to write a simple user defined function in VBA, which will identify the ordinal indicator of an integer. Since the logic is quite straightforward, let us see how we can use the different control structures available in VBA.

A quick refresher…

View On WordPress

[strugglin2xl]

Remove Filters in an Excel Sheet in VBA

Remove Filters in an #Excel Sheet in #VBA

Has your data import procedure ever failed due to users applying filters to the data? This happens quite often, especially when you have to distribute multiple files, which you intend to stack later. Copying filtered data copies only works the visible cells; and you end up loosing out the hidden rows if the user applied a filter before saving the file. Let us take a look at how to remove filters…

View On WordPress

[strugglin2xl]

Announcement: Dropbox Links Out of Order

Announcement: Dropbox Links Out of Order

The Dropdown links are not working due to excessive downloads. WordPress.Com does not allow Excel Documents to be hosted on their site. We have decided to replace the DropBox links with OneDrive links for the time being. The links have been updated for most of the recent posts. We will update links for all the posts soon.

View On WordPress

[strugglin2xl]

Removing non-printing characters from text in #Excel using #VBA

Removing non-printing characters from text in #Excel using #VBA

Have you ever encountered a perfectly constructed MATCH or a VLOOKUPfunction failing for no apparent reason? Have you had VBA tell you that a file does not exist even though it does? You may have non-printing characters to blame. There are notorious space-like characters that are invisible to the naked eye, which sneak in when you are importing data from Web Pages, Word Documents or PDFs. In this…

View On WordPress

[strugglin2xl]

Is the WorksheetFunction property really useful?

Is the WorksheetFunction property really useful?

Excel’s Application object’s WorksheetFunction property is a container for Microsoft Excel worksheet function. This property returns a WorksheetFunction object that allows VBA access to the rich set of functions that are built into Excel. While VBA has some generic functions of its own, Excel’s set is much bigger, and more suited for (you guessed it) Excel. As soon as you type in the dot after…

View On WordPress

[strugglin2xl]

Fit Worksheet to Window in #Excel #Add-In #VBA

Fit Worksheet to Window in #Excel #Add-In #VBA

Have you ever felt Excel needed a Fit-Document-to-Window-Width feature like the millions of PDF Readers out there? Wouldn’t it be great if you could automatically fit your document to your current window size? The irony is, Excel already has all it needs to deliver that feature to you. Since spreadsheets come in various shapes and sizes, you might want to fit the width, or height, or the entire…

View On WordPress

[strugglin2xl]

Duplicate a Sheet and retain a reference to it in #Excel #VBA

I recently worked on a project where my client had a Master template-sheet that needed to be duplicated and populated repeatedly. I needed to duplicate a sheet, and retain a reference to it, so I can rename it, reposition it, and pass it to another sub that populates it with data. I assumed there must be a very straight forward way to that, but was utterly disappointed when I found out there…

View On WordPress

[strugglin2xl]

How To Quickly Get A List All DAX Formulas From Your #Excel 2010 #PowerPivot Data Model

How To Quickly Get A List All DAX Formulas From Your #Excel 2010 #PowerPivot Data Model

It is good practice to keep track of all the formulas you write in a PowerPivot Data Model. I suppose you’d even be required to document the formulas at work, for the audit trail. I was working on a rather large data model, and was manually copy-pasting each and every DAXformula into an Excel sheet; and updated the sheet every time I tweaked a formula. It was frankly a very annoying necessity,…

View On WordPress

[strugglin2xl]

Are you Sure? Get user's consent before running a Macro in #Excel #VBA for Beginners

Are you Sure? Get user’s consent before running a Macro in #Excel #VBA for Beginners

I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this…

View On WordPress

[strugglin2xl]

When to use the Sheets Collection in #Excel #VBA?

There are two collections in Excel-VBA that lets the user access sheets in a workbook: the Sheets collection and the Worksheets collection. You can use these collections interchangeably in most situations, but they were each created for a specific purpose. Read on if you’d like to know more about why Microsoft created those two collections and their purpose.

(more…)

View On WordPress

[strugglin2xl]

First Class Progress Bar for all your Macros #Excel #VBA

First Class Progress Bar for all your Macros #Excel #VBA

I believe I owe you an explanation; about this post’s title. At first you might think I am a pompous prick, branding my work myself as First Class. This Progress Bar was my first project after I learned how to set up class modules: hence the name. The progress bar that I developed earlier, is one of my most visited posts, and I thought I have to reward my readers with a progress bar that is much…

View On WordPress

[strugglin2xl]

Struggling To Excel reaches 30,000 visits since Inception! http://strugglingtoexcel.wordpress.com/

[strugglin2xl]

Do You Hate The Numbers Stored As Text Error? - #Excel #VBA

Do You Hate The Numbers Stored As Text Error? – #Excel #VBA

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your lookups and Match functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge,…

View On WordPress

[strugglin2xl]

Struggling To Excel reaches its 20,000 hits milestone today. I'd like to take this opportunity to thank all my readers for the support. Only on 21 April 2014, my blog hit 10,000 views; ten thousand visits in about sixty days, thank you all!

[strugglin2xl]

Get Only The Used Areas In A Range - #Excel #VBA

Get Only The Used Areas In A Range – #Excel #VBA

I recently published a post about automatically formatting a table in Excel using VBA. That got me thinking, how awesome it would be, if we could format all the tables in a sheet, with a single click. For that idea to work, we need to get all the used areas in a worksheet; and then use the Areas Collection to loop through the tables. We can access the Areas Collection through the Areas property…

View On WordPress

[strugglin2xl]

Format Tables in Excel using VBA - #Excel #VBA

Format Tables in Excel using VBA – #Excel #VBA

Excel Tables make analyzing data, a breeze. It surprises me that it is not used as often as it should. It automatically “includes” new data you add to your spreadsheets, it automatically drags down formulas for you, it automatically formats the tables for you. In addition to that, you can use structured references that make your formulas tractable without having to name each range. You can also l…

View On WordPress

[strugglin2xl]

Get the Actual Used Range in a Spreadsheet - #Excel #VBA

Get the Actual Used Range in a Spreadsheet – #Excel #VBA

In one of my posts, I wrote about the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to, when you press Ctrl+End.

You may get to the last cell in a sheet using VBA by using the SpecialCells

View On WordPress