Author: Dick Kusleika

A Rose by Any Other

Posted on by Dick Kusleika

Tuesday will be the one year anniversary of Daily Dose of Excel. Thanks to everyone for reading. It’s been a lot of fun, and educational, writing these posts. Thanks also to the other contributors for the excellent work they’ve done over the last several months. Finally, special thanks to JWalk for encouraging me to do this and helping me out in the early days.

I came up with the name “Daily Dose of Excel” in about 90 seconds, and I’ve hated it ever since. Well, ‘hated’ might be too strong. I’ve always wished I would have spent more time and had a cooler name. I’m considering changing the name and thought you might have a few ideas for me.

Since it’s been a year, it might be fun to have a contest. That will give me a chance to spend some of my hard earned Google revenue. The worst thing about a contest is writing the rules such that I don’t have to declare bankruptcy, so be sure to read the last one carefully.

  • Send me an email with your suggestion for a new name for this site. The current name will be considered in the voting, so don’t bother telling me you like the current name – it’s already in the running.
  • You must send the name via the link above or in such a way that I can’t tell that you didn’t.
  • You must send the email from the same email account with which you registered on this site. If you’re not registered, you can’t win. If you used a fake email to register, just include that fake email address in your message so I can confirm you’re registered.
  • The best three or four names will be listed with the current name and a vote will be taken. The name that gets the most votes wins.
  • I may not change the name, even if a different name wins. In other words, it’s totally up to me what I’m going to do. But there will still be a winner even if the name stays the same.
  • One submission per registered reader.
  • If two people send the same name, the first one received will get that name. If two names are similar, the one I use, if any, will be the one to get that name.
  • The winner will get to choose between a copy of Professional Excel Development or Excel VBA Programming for Dummies.
  • To be eligible, the entrant must live in a place that Amazon.com will ship to and where the shipping will be less than the cost of the book.
  • Any other rules that make this contest less of a burden on me or that I may choose to enact retroactively or otherwise

Okay, if I missed any rules or you need clarification, post a comment. Otherwise, start the hamsters and send an email. If I don’t get three suggestions, I’ll probably just buy myself a present. If I do get three, expect a poll early next week. I suppose I’ll need to find a way to vote that’s more secure than the polls I have been using.

TextColumn Property

Posted on by Dick Kusleika

I never noticed this Listbox property before, but it seems like it could be useful. According to help, the TextColumn property sets the column whose value the Text property will return. The BoundColumn property determines what the Value property returns, so this seems to bet the step sister of that.

You can now have easy access to two columns in a multicolumn Listbox. I don’t really see a downside since everything in a Listbox is text anyway, so the difference between Value and Text should be nothing. If you don’t set the TextColumn property, the Text property returns the same as Value.

This example shows two ways to access a different column than the BoundColumn: One using the List property and one using the Text/TextColumn properties.

userform and message box showing results

Adding Comments II

Posted on by Dick Kusleika

In Adding Comments, I read the data from a text file and created comments with one of the fields because it was large. I mentioned in that post that the way I wouldn’t do it is to import the text file normally, then a run macro. I don’t think it’s necessarily a bad way, it’s just not my preference.

I start by importing the text file using File > Open. That brings up the Import Text wizard and results in a sheet like this:

Text file after importing into Excel

In a separate file, I have a macro. The separate file could be an add-in, for instance, but it doesn’t have to be. The code just can’t be in a text file.

Sub MoveColumnToComment()
   
    Dim ws As Worksheet
    Dim rCell As Range
    Dim rRng As Range
   
    Set ws = ActiveSheet
    Set rRng = Intersect(ws.Columns(1), ws.UsedRange)
   
    For Each rCell In rRng.Cells
        rCell.AddComment rCell.Offset(0, 1).Value
    Next rCell
   
    ws.Columns(2).Delete
   
End Sub

Once the code is run, I’m left with:

Excel range after macro showing comments added

Adding Noncontiguous Cells

Posted on by Dick Kusleika

I’ve found myself in the situation where no matter where I moved my cursor, the selection was extended. This is usually because I accidentally hit the F8 key to enter Extended Selection mode. I don’t think I’ve ever used it on purpose.

However, in my never-ending quest to rid myself of the mouse, I used a feature I’ve never used before: Shift-F8 to enter Add mode. I had a list of five numbers and I wanted to see the sum of the first, third, and fifth numbers. Normally, I would use the Control key and the mouse to select the three cells, then read the SUM in the status bar.

To do this with just the keyboard, I found the Add mode, but I still don’t think I’m using it right. To get the below SUM, I did the following:

  1. Start in A2
  2. Press Shift-F8
  3. Arrow down to A4
  4. Press Shift-F8 to exit Add mode
  5. Press Shift-F8 again to reenter Add mode
  6. Arrow down to A6
  7. Read the sum

Three noncontiguous cells selected

That double Shift-F8 key sequence can’t be the best way, but I’ll be damned if I could come up with a better one.

OpenOffice Blog

Posted on by Dick Kusleika

About a month ago I downloaded OpenOffice. It’s still sitting in my Downloads folder waiting for someone to double click it. Gee, I wonder when that’s going to happen. I think it would be fun to explore the Calc program and maybe even write a review of it. I’m just too busy to have fun lately.

Until I’m properly motivated, I’ve subscribed to Calc Tips which I heard about from j-walkblog.

Adding Comments

Posted on by Dick Kusleika

Paul is importing a text file into Excel. One of the fields of the text file is particularly long and he’d prefer if that field was made a comment instead of put in a cell. You certainly need VBA to accomplish this, but there are probably a couple of ways to tackle it.

You could import the text file normally, then run a macro to put some of the data into comments. Or you could access the data in the text file directly through VBA. I prefer the latter. Take this comma delimited text file, for example.

Notepad file showing comma delimited values

I can run this code to put the data in the cells or comments I want:

Sub TxtToComms()
   
    Dim lFnum As Long
    Dim sFname As String
    Dim i As Long
    Dim sInput As String
   
    sFname = “C:TesterImportComment.txt”
    lFnum = FreeFile
   
    Open sFname For Input As lFnum
   
    Do While Not EOF(lFnum)
        i = i + 1
        Input #lFnum, sInput
        Sheet1.Cells(i, 1).Value = sInput
       
        Input #lFnum, sInput
        Sheet1.Cells(i, 1).AddComment sInput
    Loop
   
End Sub

This simply puts the first field in the cell and uses the AddComment method to create a comment that holds the second field.

Excel range showing some text put into comments

Programming Language of Choice

Posted on by Dick Kusleika

Steve asked a question that I couldn’t answer:

…if I wanted to build decent applications for the future and will spend the time to learn a language, would you recommend VB6, VB.NET or another language?

You may have heard about the infamous ClassicVB Petition about which Stephen Bullen posted. For opponents of the petition, this seems to be an easy choice: .Net is the future, so pick VB or C# and be on your way. For many of the proponents, it’s not so easy. Obviously if the petition does its job, VB6 will be the choice. As much as I am behind the effort (although not in complete agreement with all aspects) I wouldn’t bet the mortgage that it will change anything. I didn’t expect Microsoft to come around as soon as the petition hit the blogwaves, but it looks to me like they’re defending their position quite vehemently. Having said that, all it takes is for the right person to get the right information (e.g. big customer talks to Ballmer) and things could start happening, so I haven’t lost all hope. But enough about that petition.

I can’t tell Steve to write in VB6 today. You’re one Windows Update away from code that fails. You can write in .Net, but there’s risk there. If Microsoft can throw away VB6 code assets so easily, what are you going to do in 10 years when .Net is old news? Are you going to rewrite your tens of thousands of lines of code in MS’s flavor of the day?

Here’s your choices as I see them.

  • Write in .Net and understand the risks. Resign yourself to rewriting your code every 10 years, and if you don’t have to, then it’s a bonus.
  • Write in C++. Microsoft writes its stuff in C++ and you’ll notice that C++ hasn’t gone anywhere. They know how to protect their code assets. So long as your language is the same as theirs, you’ll be fine.
  • Write in Visual FoxPro. I don’t know the whole story behind this, but from what I gather MS was going to dump VFP. Someone(s) made the right stink to the right people and it came back. There seems to be some freakish cult of VFP programmers and that’s one program that doesn’t seem to be going away.
  • Dump MS. I’ve heard of VB6 programmers moving to Delphi, Sun’s Java and REALBasic.

If you’ve read this far, you know that I don’t know what I’m talking about. What I do know is that I’m fearful of writing anything major in VBA or VB6, but I haven’t committed to .Net yet. Are you still writing in these languages? If not, what languages are you using?