Excel VBA




About Us



Disk is full

On Friday, this error occurred two times to me in one day. First my software gets a 3050 error. I look it up on Google and find that the MaxLocksPerFile needs to be raised. I'm unable to fix it because at my client, I'm unable to change this as I don't have admin rights. While making a copy of the database I get a disk is full error. It turns out that at my client, you can only have 1 Gigabyte of space for your projects, but I've gotten mine upped to 2 Gigabytes. Time to clean up files. Computers should be better at this, maintaining file space. I usually do a search for the files greater than 10 megabytes and then sort the list in reverse order giving me the files to delete first. After deleting the files my error goes away, but not before wasting a half hour of time. How much time is lost collectively at this company due to this problem?

Later on another machine, I get a -2147024784 error and looking this up on Google I find that the Disk is Full.


Poor Error Messages

Today I get an error message "Error 75 Path/File access error occurred in line 0". Now the message was annoying by itself, but it popped up every 5 minutes. Developer should have built a function to ignore the message


Can You Help Me

Today my son says to me "Dad, I need your help. My Wii isn't working." I asked him if he's tried everything and he answers, "Yes." So I ask him, "If you did everything, what could I possible do to fix it?" So I look at the Wii. When you point the controller at the screen nothing would happen. So my focus was on weak batteries, asking him if the batteries are OK. He was right the controller wasn't working. I looked next at the device that picks up the controllers signal and noticed that it wasn't plugged into the Wii. My son plugged it in an the Wii started to work again, even though my son tried everything.

The most important lesson to take from this incident is Dads are smarter than kids. Well, I'm just kidding. The lesson is things that seem impossible (fixing a broken Wii to my son) are almost embarrassingly simple once you know the solution (the Wii was unplugged). This especially applies to programming for fixing bugs always seems to be so obvious when you find the solution. The rule to keep in mind is when something seems impossible there is usually a very simple and obvious solution (in hindsight). Giving up won't solve the problem.


Beware Excel 2002 Users

Today, I learned of a dangerous bug in Excel 2002. As a rule I try to keep multiple versions of spreadsheets I work on in case of corruption. I learned today that this is no cure for corruption. Saving a corrupted Excel sheet to a new version will copy of the corrupted code and still allow you to edit your program. You must close the file and reopen it to make sure the file isn't corrupted. Unfortunately the client I work for uses Excel 2002. If you can try to upgrade as this version is pretty bad. The corruptions appear to happen when sheets get large, and I was working with 33 Mb sheets.


A ........... Moment

Every so often you find something, whether a book, video, article, website, song or person that changes the way you look at, understand a subject. I use the ......... because I don't have a word to describe it. But while looking up the Sharpe Ratio, I found an article that lead me to another article on called The Tao of Alpha. It describes the concept of Alpha and Beta in terms of investment performance. All I can say is wow, what a great article.


Nail Puzzle

I love the nail puzzle on a couple of levels. First it is ingenious to take two nails worth about 5 cents and make it into a $3.00 puzzle. Second, it is a great puzzle that forces you to think out of the box. Once you solve the puzzle it seems so obvious on how to solve it. I wonder if it could be a good way to test developers on a job interview. Solving this puzzle and others relates to software development by showing:
  • Thinking out of the box, creativity
  • Determination
  • Problem Solving
  • Not Quitting


More Poor Customer Service

I get my bill for my 800 phone number today and it looks wrong. So I try calling the number for Customer Care. Of course I have to select through menus and they need my account number. Finally I get connected but the message tells me that the office is closed. It's 6:30 pm on a Thursday night. I thought businesses were supposed to stay open late on this night. So I'm frustrated from the bill and frustrated from the call.

A Good Customer Experience

I signed up today for online banking with Chase, and it was a very pleasant experience. There was one minor glitch, but from their website they guided me to set up my account and I was able to actually pay a bill in about 5 minutes. They used an authorization process where the computer calls you at your home phone number and speaks an 8 digit authorization code. After entering your information you can actually pay a bill. Nice job, Chase.

I also use a web host, called addr.com, which has given me pretty good customer service. They have an online, chat to reach them. They've always answered any questions I have.

November's Goals

November's Accomplishments

  • Got Alpha version of Trader Analyzer installed
  • Spoke with first new client
  • Research remote support product

December's Goals


Reboot it

My cell phone was acting strangely. I tried to make a call but nothing happened. When someone called me I couldn't pick up the call. At first I thought maybe a setting was changed, but then I decided to do what solves 80% of all computer problems, reboot. So I remove the battery, reinstall it, and phone is back to normal.


Where did it go?

Finding bugs is a lot like finding lost items. Recently, I went ice skating and after putting on my skates I couldn't find my blade protector. I looked around but it appeared to vanish into thin air. I looked behind the bench were I sat but it was dark and there was lots of junk there but no blade guard. After the session was over and I removed my skates, I wondered what happened to the blade guard. I went back to the bench where I put the skates on and looked more carefully. After a minute there it was. It had fallen behind the bench and I didn't see it. When looking to solve bugs you may sometimes overlook the cause, so be careful when checking your assumptions.


Listen to your Intuition

This Thanksgiving while at my brother's house, I was watching the football game when my wife asks me to help her move a glass table out of the way so the kids don't break it. Feeling tired I ask my nephew to help her. My sister says that it isn't necessary. I don't know if my wife is psychic, but sure enough my son breaks the table by just leaning on it. Luckily, he was unhurt, but it could have turned out much worse. It is another example on how important you intuition is. Whether it's hiring someone, or a safety issue, dealing with a customer. Always listen to what your gut is saying.


The Lost CD

I recently took an audio book out of the library,The World is Flat by Thomas L. Friedman. It's a very interesting and highly recommended book. But the problem started when I was looking for a CD #6 out of the 22 and couldn't find it. I asked my wife if she had seen it and she said she may have mailed it accidentally to Netflix. So she continually tried to get the CD back. They even have it listed in their FAQ section what to do if you do this. My late fines were accumulating and finally Netflix credited my wife about $16.00 for the lost CD. But something bothered me. The CD's were clearly marked with the name of the book. How could she miss this? I started to wonder if she really returned the CD to Netflix. So I decided to look through my boxes and sure enough after about 10 minutes I found the CD. I bring this story up because it's similar to finding bugs in software and what assumptions you make. Assume your program works and you don't find the bug. Assume you made a mistake and problem will be solved much sooner.


My First Hire

My son worked for me for one hour. He created a return stream which I used to test Trader Analyzer to see if it could detect fraudulent data and it worked.

I also had him enter a book summary. To grow my business I must hire someone.


May I

One example of a bad user interface is forcing people to do things they don't want to do without asking them or not giving them a way to pause or cancel the process. I'm shutting down my computer as I'm late for a meeting and all of a sudden my computer decides to install updates to my computer. I'm stuck for 5 minutes waiting for the software to be installed and wind up being late for my meeting.


Bad Customer Service

I've been getting "Disk is Full" messages every day, so I have to stop what I'm doing and clean up files I don't need. I decide to call the help desk to see if I can get this increased and while telling the customer service rep about my problem she says "it's like a paper bag, you can only put so much in it." I could come up with 20 arguments why it's not like a paper bag, but the point is, the customer is always right. Again, here may be a case of thinking your company is saving costs but in reality may be wasting productivity by having to have people manage their disk space on a daily basis.

Where's the coffee?

I like a good cup of tea, or dose of caffeine, to keep me alert during the day. I think most Americans are addicted to this stuff. You can tell just by the lines outside of Starbucks. Anyhow, the company I'm at doesn't supply a coffee machine, or even hot water on my floor. For me to get a cup of coffee (sounds better than tea) I have to take the elevator from 14 to 1 then up to 24, back to 1 and then up to 14. OK, now I'm sure they've saved real money by cutting this out, but what about the employee's time and frustration just to get a cup of coffee?


Automated Voice Response Machines

I'm all for automation, but sometimes the cost of lost productivity and frustration should be included in the savings of replacing real people. I get very frustrated with these automated response systems. I always seem to get a choice of menu items but none of them are what I want. If you're company has one of these contraptions, then make sure the first thing it says is "If you want to speak to an operator, press zero." To me, time is money and by using this my time is wasted. Today I timed various parts of the call I made, five minutes to report that I was locked out of the system.

The system always starts with asking me for my employee id and then the month and date of my birthday. Sometimes itís your social security number. Why is it that when a person finally answers they always ask you for this information again. Why did I have to enter the information in the first place? Since it's an internal system and they have caller id, why can't they pull my record up by my phone number?

One thing that keeps happening is that I get randomly selected to complete a survey after the call. This message took 19 seconds of my time and I didn't plan on answering anyway. Here's the stats for the call:

HH:MM Note
00:00 Dialed the Help Desk
00:19 Phone picked up by robot
01:00 Got to the main menu.
01:10 Selected Password Reset from menu
01:31 You have been selected for a survey....
02:07 Message tells me that I have less than a two minute wait time. Now at least they've given me the option of hanging up. I like to be told how long it will take.
??:?? Told problem to operator
05:07 Call completed and got my reference #4949632
60:00 Not reset. Not sure if anyone even knows.
  Over three hours and I'm still locked out.@##@@$@!

Murphy's Law

Whatever can go wrong will go wrong at the worst possible moment. How true these words can be. I was preparing for a demo today and I ran into two problems:

  1. Network is extremely slow
  2. Excel Macros aren't working
All the while I'm trying to create a PowerPoint presentation which is needed in three hours. I keep working and it finally starts speeding up again.

I use to give lots of demos while working at Banker's Trust now Deutsche Bank. I learned a few valuable lessons from this experience:

  1. Script your demo and don't stray from the script no matter how temping
  2. Have a way to automate the reinitialization of the demo so you can easily give it again
  3. Have a backup on paper in case your system has problems
  4. Get to the site where the demo is ahead of time and walk through the process
While giving the demo, the machine wasn't connecting to the network. This was caused by a problem with the cable. The clip on the cable was broken off so the cable wasn't sitting properly in the female connector. Fortunately I had spent the time preparing the PowerPoint slides which were just screen prints.

Also, get someone to assist you with your setup I had a co-worker help me and he was able to save time and help get ready at the final moments. Thanks, Igor.

The power of PowerPoint

What a great tool. Any time you give a demo make sure you have screen print embedded in your PowerPoint presentation, in case something goes wrong. It also makes your demo much easier as all you have to do is go through your slides.

Test without your Network cable

One test case I will do from now on is to test applications with the network cable removed. It's a great way to test cases that you won't find otherwise.

Test your application on another machine

I can't say how many times a program I'm working on works on my machine but the second you try it on someone else's it fails. As user can have different drive mappings, be missing files stored on your machine, missing ODBC drivers or any other host of possible issues. The only way to check is to try it.

Make sure applications has no Links

One issue you can get when working with multiple files is links to other files that only are on your machine and not on your user's machine. Go to the Edit - Links menu or search the Workbook for the "[" character.

Poor Service

Today I entered a password incorrectly and got locked out of the timesheet software I use at a client. So I call the help desk to unlock me. First of all the system shouldn't lock you out because of this. It should not allow a sign on for a minute. Do this three times and then lock the user out. To put the request in on their Voice automated system took over five minutes. Then to make matters worse they never unlocked my account. I called a second time and it didn't help. It's unacceptable to me that the help desk person couldn't unlock my account or even allow me to send myself an email with a new password like most systems do today.


It's due Friday

It seems to me that if you want to get productivity out of your workers, set a deadline as work tends to fit the time allocated.


My computer is slow

Be happy if your user calls you for help. Some don't and get angry at you and you don't know why.. My user calls and says software is running slower than normal. So I go up to her floor to view the problem first hand. I see that the system is running slow so first I check the Access database and it's pretty large, 670 mb, so I compress it down to 87 mb and retry. The same result. Next I move the files from the network drive to user's local "C" drive and wallah, the system runs at normal speed. I log a call to her help desk to try and resolve the cause of the slowness. Many things can cause a slow network drive.


How did I do that?

One problem I had today was that I figured out how to do something, (coloring a bar chart red for negative and green for positive) and I forget the technique I used. I remembered that I did a search on Google for InvertIfNegative and found the answer again in Peltiertech.com. The return below is Merrill Lynch's yearly return graph.

Deleting Modules from your code

I've been working on a program called DetermineWorkSheetSizes.xls. The purpose of this tool is to help identify what's taking up the room in your spreadsheet. I started with an 11.5 Mb sheet and through this Excel program I've gotten it down to 1.5 Mb and should be able to make it smaller. I got stuck on trying to delete modules from via code but I stuck with it. Finally through trial and error I identified why the code wasn't deleting modules. It was cause I had EnableEvents = true and once I turned these off the code worked. It does happen, where you will spend many hours trying to solve a problem. This one took much longer than I would have liked, but I'm happy it's solved.


I was having an issue in some code I wrote that when one Excel program opened another, the second Excel's macros started running, which is the behavior I didn't want. To solve the problem I use the Application.EnableEnvets = True. This stopped the problem from happening.


When in doubt reboot

I notice that an Excel program I run to detect overdue library books is hanging. Also, entering C: in the Start -Run window and nothing happens. I open the Excel program and step though the code. It hangs on a call to a DLL. So I try to see if I can at least call the DLL with the Version property and that hangs too. So, rebooting and at least 30 minutes down the drain. As the system is shutting down all the software that's hanging starts to work again. So I try the Library program again and of course it works.

Dynamic buttons

I have a VB6 software program that I'm using right now to enter this blog called WWWGen. I use it to make developing and managing multiple websites easier. So I would add new buttons for common things I do. I realized that I should be able to do this in the code so I've set up a button called "Set cmd" which takes whatís in the clipboard and assigns it to the "Do cmd" button.


While at a client and developing Excel code I get the dreaded File Corruption dialog, which never saves my work. This is one of the most frustrating things about being a software developer. I posted some code I use to save multiple versions of my code here.


I'm at my desk and I hear some in an office say "No one's using it! It's too F@#ken slow." Wow, it made me think how important something like this could be. You could build the best system but something like this can cause your users to reject it. Once they get a bad opinion of your product it's almost impossible to change.


Why do Cops Carry their Hats?

In New York city, I've noticed that cops seem to carry their hats around, never wearing them. I guess they don't want to get Hat Hair. It's probably due to some rule that the cops must have their hats but don't have to wear them.

What's the price

Another practice that I find annoying is websites that don't post the price of a service they offer. Dice.com and GotoAssist.com both didn't list their prices.


Escalator Problem

When I take the Long Island railroad to client in the city and it's time to take the escalator up, I notice that many people will walk up the escalator, but there's always some lazy person who stands still and rides the escalator to the top. Once this happens, people are no longer able to walk up the escalator. Common escalator courtesy says to stay to right if you're not walking up.

I Get a call

I get a call today from a old co-worker who I was thinking about calling. He's at a new company and is interested in my Trader Analyzer software. He will be my first beta customer.


Had the idea today of training Excel developers using Excel. I could write code that helps you learn the concepts and you would do it right from Excel.


Today I decided that I wanted code to search my hard drive for Excel projects, I've worked on in the past to help me with a book I started yesterday called Kravatz Inc.'s Guide to Excel-VBA Macros. At first I tried to use a recursive method to walk the directory structure, but the Excel wasn't able to properly keep track with the Dir command. So I changed the code a little and used a looping structure. I then added some additional code to retrieve the size of each file. I now have a very simple piece of code that can walk the directories and subdirectories of a disk and place the output on a spreadsheet. While working on my website I ran into a bug which is probably one of the most common bugs there is. I entered data with a single quote (') and it screwed up my SQL I forgot to include my QF routine,

Function QF(s As String) 'Quote Fix QF = Replace(s, "'", "''") End Function

A problem computers have is that they store files but don't have little context of what the files are and what the relationships between files are, the purpose, etc. If software was designed better peoples hard drives wouldn't be filled up with so many files that you have no idea what they're from or for or if you need them or can delete them.


Spent some time cleaning up code to find short domain names. There were no three character .com domains available and no 4 character domains available starting with the letter "G". If you're interested in a short domain see what we have or contact us to find out how we can help you.


While sitting on train on the way to a client today, I noticed a picture of Stanley O'Neil, the CEO of Merrill Lynch, in the NY Times. The following article was in the New York Times. It was a scathing article about Merrill Lynch's Risk Practices. Shocking article as I'm currently working in the Credit Risk group. Hopefully, I can help them out of some of the issues they're having.

I continue to update my website. It's a lot of work.

Decided to change the book idea I have from "Beat the Market with Excel-VBA" to "Kravatz Inc.'s Guide to Excel-VBA"

Spoke with a former colleague and he is very excited about an accounting idea for Hedge Funds. He also liked the book idea. I spent a little time today working on the table of contents.