One issue that I’ve had a few times is trying to create a list of files. Why would I need to do that? Because then I can create a macro that will go through all of those files one by one to do specific operations. How do I do it? First, I’ll open a DOS cmd window by clicking the start button of my pc and typing “cmd”.

Then, I can simply type the following to get the list of files in my L: drive for example:

L:

Then I could go to a subdirectory such as ETF by typing:

cd ETF

Then, I’d type:

dir > list.txt

I’d then get the list of files in that directory in a file named list.txt, which I can open and start playing around with in excel. Easy enough? See how I did it here (given the fact that a .txt file is not as easy to use.

One question that I’ve gotten a few different times is how to easily determine if a student has failed or passed. Obviously, if you have 2 or 3, it’s very easy to determine. But when you are dealing with a few hundred or thousands, it can become a lot more complex. Here is one question that I got:

“Can any one suggest a formula for below results. i want display if all cell marked as pass then Pass should display in the Final Result, if any one result is Fail then Final result should display Fail

Pass
Pass
Pass
Fail
Pass
Pass
Final result = Pass or Fail

Please help”

One interesting thing about Excel is that there are usually many different ways to get one result. So let me first start by adding this to a spreadsheet. I’ll try to get a valid from answer using 3 different functions.

If And Sum Function

One way would be to translate each pass or fail into a 0 or a 1. For example, I could add a column next to it where I could see if the student passed or failed that subject. It would look something like this:

Then, I would use the sum to determine if the student passed all subjects as follows:

=IF(SUM(C2:C7)=6,”Pass”,”Fail”)

2-Using “Countif”

This method will be much easier to do and does not require adding an extra column. How so? Here is the formula I’ll use:

=COUNTIF(B2:B7,”Fail”)

Then, I’ll use an if condition to verify the number of fails:

=IF(COUNTIF(B2:B7,”Fail”)>0,”Fail”,”Pass”)

Frankly, this seems like a much better way to manage the problem.

One of the things that I often need to do when working my spreadsheets is to save information at various times during the day. It’s important for me to keep logs of all of these files. So what can I do? I used to save files in a format such as:

TD20131015.csv

Where 2013-10-15 is the date. The problem came up when I started to need saving several files in the same day (but keeping each one). I looked at potential options.

-Saving the file manually (which is against the whole idea of using macros)
-Having a location in the file where a number is written (it could be changed from 1 to 2, to 3, etc) – the biggest problem is that it would create bugs. For example what happens if you close (without saving) and reopen the file?

In the end, I decided to simply save the file using the exact date AND time. It would look more like:

TD_20120628_094100.csv

How? It’s fairly easy. First, I created a cell in my file where I get today’s date:

Then, I created a name and finally added this simple macro:

It’s fairly simple but it works

I saw this today on Microsoft’s blog and thought it was a great thing to do. I’ve discussed the fact that some of the Microsoft apps fall way short, especially Google finance. Others though can turn out to be quite useful and I’ve started experimenting with the video tutorials. They are not always useful but in some cases they can truly help out. It’s a quick install so you might as well give it a try if you’re currently running Excel 2013.

To install the app:

1. View the app in the Office Store and click Add. (You’ll need your Microsoft account for this part.)

2. Open Excel 2013.

3. Click Insert Apps for Office

4. In the Apps for Office list, select Excel video tutorials and click Insert.

You’ll now see the app in your worksheet:

You can read more about it on Microsoft’s blog

One issue that I’ve seen quite a few times is seeing macros that end up being very slow to run. Those usually happen when a file evolves over several years and becomes more complex, bigger, etc than was anticipated at the start.

What I Do To Detect The Problem

The biggest challenge of course is trying to find what step is taking too much time so that I can then work on that specific part. How do I do it? I generally build macros, splitting them in different modules with one macro that groups them together. It makes it much easier to get a big picture of what the macros does, etc. Then, I get to something like this:

Sub ImportData()

Call importyest
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importyest”: r = r + 1

End Sub

It’s fairly simple and my bigger macros are obviously 10-20 times bigger but this gives you an idea. Every time I call a new part of the macro, I write down the time into a tab that is called “log”. Then, I can easily look at the number of time between each step and find out where the delay is happening. I could then repeat that same step in that part in order to find out how I could speed it up.