Another Solution for 1024 Character per Cell Display Problem

Remember back in January when Mike requested a solution for Excel's (pre-Excel 12) 1,024 character per cell display problem? I worked something up for him, but as part of the solution, I inserted new rows and merged cells. Another person let me know that this caused a problem if the cell was part of a table that one wanted to sort.

So...I went to work on a better solution, and I think what I've produced is far superior, though could still be taken further.

Essentially, what I've written checks each cell in a selection to see if it has more than 1024 characters. If it does, it then automatically places line feed characters -- chr(10) in VB -- at the end of each line based upon an approximate number of characters that should fit on each line (only in Arial 10 regular font). It was a bit tricky figuring out how to determine the end of each line -- it could be looking for a space near the approximate end based upon the width of the cell, or a line feed character somewhere before that point.

In the end, it works well. Some possibilities for enhancements? Well, it could be improved to work with any size, name, and style of font. This one converts affected cells to Arial 10 Regular font, which is typically the default font for Excel anyway.

I think those of you who've signed up for my Excel_Geek Insiders service will enjoy it. Just let me know if you need help "strapping" the code on to an existing file you've got. If you haven't already subscribed, you might think about doing so. It's only $39.95 per year, and you get copies of all the file I produce for $50 Project requestors, as well as a few other projects, like this one, that I simply feel like doing.

Interested? Sign up today.

My first Excel/VBA Lesson - Sort by Cell Background Color

The other day I was asked by a colleague to teach her the basics of programming in Excel with VBA. Here's the first task I gave her: write a script that a user can call to sort any selected range of cells by the color of their backgrounds. The reason I selected this task is that it seems fairly often written on on the web, so she would be sure to find plenty of examples.

In the end, with some help from me, she produced a fairly tight little script, which I'll be sending it out to those of you who've subscribed to the Excel_Geek Insiders service.

The basics are 1) insert a temporary column next to the range to be sorted, 2) set the value of each cell in that new column equal to the ColorIndex of the cells in the range to be sorted, 3) select the new column and range together, 4) sort by the new column, 5) delete the temporary column, and 6) reselect the original range to be sorted to give users a seemless feel.

Interesting, straightforward approach, I think.

Database Project - Web Statistics

Just today I (we, actually) wrapped up a project that WASN'T in Excel. How about that?

A request came in from S _ _ _ _ _ _ , who manages around 1,000 domains. Each month he receives a .DAT file with various statistics for each domain. These files can regularly exceed 5,000 to 10,000 rows of data, so until Excel 12 is in more widespread use, MS Access seemed like the best place for this to live.

I pushed this project out to my growing list of Contributors, and had an immediate taker -- a guy with an extensive database background already. My Contributor turned around the project very fast, complete with functionality to browse for and automatically import the .DAT files, as well as several pre-built simple and crosstab queries. Even better, this Contributor put together a quick instructional sheet illustrated with various screen shots, etc. Nice.

I guess I know where to send off all new incoming database projects, eh?

Seriously, though, thank you S _ _ _ _ _ _ for the project, and thank you Contributor for your quick, comprehensive approach.

Excel_Geek

Manage Google Adwords from Excel

Recently Kelly requested that I build, using Excel, VBA, and the Google Adwords API, a simple means for him/her to manage his/her Adwords account from a spreadsheet. I love the concept, especially for people/businesses that create a long list of ads to manage over time. Basically, the idea is to be able to create and activate new ads, edit existing ads, delete or deactivate ads, etc. from within Excel. Very doable, methinks.

Now, I've not done any projects using the Adwords API, and I'm a bit overloaded on some other projects right now, so I pushed this project out to my vast team of...um...two international Excel_Geek Contributors. Both very capable, but neither has chosen to tackle this particular project.

So...anyone else in the world know how to do this/want to try to do it? Please, become an Excel_Geek Contributor today!

Kelly, if no one out there is willing to step up and help out with this, I'll get to it, but it won't be tomorrow...or next week...but I do want to do this type of work. I might even use it myself.

Excel_Geek

BTW, thank you everyone for your overwhelming response to my new Meebo Me chat feature I installed yesterday. I'll stay signed in as often as possible to help you all with those quick little "How do I (blank) in Excel?" questions that always pop up.

Meebo the Excel_Geek

New feature!

Sorry I haven't been posting lately. I 've been super busy, and most of the projects I'm working on lately are of the Private Project variety, which doesn't give me much latitude to tell you all about them, but just to give you all some idea of what the heck I'm doing, here are some ultra-brief descriptions:

• a fixed income securities optimizer


• a shipping container optimizer


• an ecommerce site revenue model for scenario analysis


• several custom business forms and a staff scheduler


• code to export a series of Excel charts to image files to use in a mail merge

Now, the real reason for this post: Do you all see it? I've installed a MeeboMe widget on the upper right-hand side of the blog. Super cool. Now, please understand, by their own admission, this thing is a bit buggy right now (sometimes I'm having trouble getting it to show I'm online even when I am), but they'll get those issues worked out, and this will be sweet. When I'm online, you all can shoot me instant messages. Got a quick simple question? Meebo me!

Aren't familiar with Meebo? I suggest reading their blog from start to finish (or skim through it as you've got time). This little company recently landed big venture capital funds to try to figure out a revenue model to support their mutli-instant messaging service aggregating capabilities. I've been following them semi-regularly since near the beginning. I think they may be onto something with this Meebo Me service.

Later,

Excel_Geek

Return Online Yellow Page Listings to Excel

For a recently requested $50 Project I created a spreadsheet that allows the user to type in the name of a company and an optional state to return a list of online yellow page listings, including company name, address, city, state, zip, and telephone number.

I also created a spreadsheet for this person enabling him to automatically sort by company name, address, city, state, zip, and phone number, as well as automatically apply filters by each of those values, and filter by ranges of zip codes and phone numbers.

How do I get the listing information from the internet to Excel? Well, I wrote a script (using the objHTTP object and regular expressions) that went out to one of the many web-based yellow page services and returned the first page of matches for each company name, along with each associated address and telephone number. After running the code through a list of around 1,800 companies, it retrieved potential matches on roughly half. Not too bad. Beats the heck out of looking up each one manually and copy-pasting the contact info by hand.

All of this took me about 4 hours (programming time and run time included). See the value some of my $50 Project requesters receive!

The script I wrote to get these addresses and phone numbers could be modified to also retrieved websites for those companies listing them, which seemed like around 15-20% of them or so. Also, I could probably also tweak the code to retrieve all company matches for searches done, not by company name, but by business category. That could be valuable, I'd say,

Once a list of company matches were identified, I set up a second spreadsheet to allow this user to automatically sort and apply filters by each of the columns in the list. This could be a simple and effective means of analyzing groups and subgroups of clients, assigning sales or service territories, etc.

Thanks for the project, Ian. I hope you like it.

Excel_Geek

Reader Asks: Where does your traffic come from?

Recently an Excel_Geek reader (we'll call her Sue) emailed me. Sue is interested in trying to manage a blog similar to Excel_Geek, offering services related to Microsoft Office Suite products. She wondered how I got going, where the traffic comes from, etc. So, for this reader (and anyone else who might be interested), here goes:

First, let it be known that this takes some time and effort. The days of turning on the website and being flooded with traffic are just gone (for most of us). I started doing this just over a year ago, and I used to get 2, 3, maybe 5 visits a day...TOTAL. So what'd I do? Simple. I kept posting. I tried to talk about relevant stuff that I really knew about. I focused fairly narrowly on my core of VBA proramming specifically for Excel, and hoped that others might find it useful and/or interesting. Once in awhile I'd post about something a bit off center, and sometimes I would be surprised by the results. Currently, my post about the Yahtzee scorecards I created in Excel for my wife still consistently lands in my top ten content posts of all time. Seriously.

I also introduced myself to others out there with apparent interests and expertise in Excel and VBA programming (see the list of links to the left). Did I view these other experts as potential competitors? At first, but then I realized that we're all playing for the same team: the team that wants more people to understand what they can really do with standard software and a tiny bit of knowledge (and perhaps some of our help from time to time).

Did it work? Slowly at first, but I began to get $50 Project requests. Some were interesting and good fodder to post about, and some were less so. Heck, I did some girl's Excel homework for her for $50. Now the second main aspect of my "marketing" plan was put to the test. Word of mouth. I needed to do a great job (and still need to) on all of these projects, since these people could need help later, and would likely tell their friends, family, and colleagues how they "...got this custom programming done for 50, and it works great.." So far, I'm doing well in this respect.

The $50 Projects kept coming in (and continue to) at a faster rate. And I started to see my clientele moving from novices to intermediate Excel users to true "power users." For the most part today, I do work for those who already know how to do most of what they need done, but they just have one or two issues they can't figure out yet. My style of commenting the heck out of my code, providing explanations as to what I've done and why seems to be a huge "selling point" for me.

With an "audience" starting to build I thought it was time to add some other services for these people. I added Excel_Geek Blog_Mail as a great way for regular readers to get my new posts emailed to them automatically for free without havng to figure our RSS feeds, etc., and I added the Insiders program (probably the greatest value I offer), allowing subscribers to get copies of all my $50 Project files completed for the year for less than $40. These services have helped build my audience.

A recent phenomenon is that I'm seeing more and more Private Project requests. Word has apparently gotten out that companies trying to better integrate Excel (and other apps) into their workflows can get access to quality programming services for $50 per hour. While that's not India or Thailand cheap, that's cheap for those not yet ready to offshore this sort of work. And speaking of other countries and "offshoring", my recent move to recruit programming contributors has yielded fruit in the form of my first contributor (from Singapore) signing up and completing his first project -- ahead of the promised delivery date and in higher quality than I could have hoped for. It seems as though I could end up learning more about VBA from my contributors than they'll learn from me!

That's it. That's what I've done and what I'm doing.

To give you some idea about exactly from where my traffic comes, here are some charts I generated from my Google Analytics account:

Here's where my visitors live (this is a sample of 500 visitors, as that's the largest number that Analytics will display for you):



Here's how my traffic breaks down in terms of referrals:



You can see that nearly half of my traffic is referred by search engines and/or directories, a third is direct (they type in my address or have it bookmarked), and 1 in 5 visitors are referred to my by what I'd consider "relevant content providers" -- that is, by other websites or blogs that talk about Excel, VBA, etc. These referrals, to me, are perhaps the most valuable, and my conversion statistics play that out.

Here's how the search engines breakdown:



Adding Blogger into the Google slice, and you've got a more "true" representation of company by company results.

And lastly, here's how my "relevent content provider" referrals break down:



I think this is a good place to pass along my sincerest thanks to...

AndyPope.info
Puremis.net (home of Colo's Excel Junk Room)
Bettersolutions.com
AutomateExcel.com
Excelblog.net

...and many others who've helped me, for helping Excel_Geek become what it's becoming. Thank each one of you.


And so, Sue, here's my one line advice to you: If you want it, do it, but do it hard and with passion if you want any chance to succeed.