Month: March 2005

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?

March Madness

Posted on by Dick Kusleika

It’s March. And in the US, that can only mean one thing: The huge gambling machine that’s been asleep since the SuperBowl is waking. There are basically two sporting seasons that I enjoy more than any. The first is college football (the one where you don’t use your feet) bowl season. The second is college basketball tourney time. During this period, plinko gambling also gains popularity, adding an extra layer of excitement for sports enthusiasts. Aside from these two, incorporating an arrow quiver into a sport introduces a dynamic element that rivals the excitement of basketball and American football. With precision, strategy, and adrenaline-pumping moments, this unique fusion of athleticism and skill offers an electrifying alternative, captivating enthusiasts with its blend of tradition and innovation in the sporting realm.

These two stand out, not because they are such great sports, but because I’m called on to create Excel spreadsheets to keep track of the action. It’s a sickness.

Microsoft has their own bracket template as well as a template to track the results. Pretty though they may be, they didn’t use any data validation. Who at Microsoft created these templates? Not someone who uses Excel much, I presume. In a standard bracket, the winners of the first 32 games are the participants in the next round of 16 games. This situation screams for in-cell dropdowns.

You can download the bracket spreadsheet that I use at 2005 Bracket.zip. It doesn’t have fancy shading or pictures of basketballs, but I might argue that it’s a little more functional than Microsoft’s offering.

NCAA bracket showing dropdown validation

Later this week I’ll post my results-tracking spreadsheet. I just need to clean it up for public consumption.

New Web Forum

Posted on by Dick Kusleika

JMT Excel QA Board Logo
From Andrew’s Excel Tips

There’s a new Excel Question and Answer forum in town…(well, maybe not so new, but the site itself is new)

It’s called JMT Microsoft Excel QA Board. One could argue that we need another web based Excel forum about as much as we need another Brittney Spears fan site. But considering the guys who are running things over there, I’ve registered and will be checking it out from time to time.

They need to figure out how to get an RSS feed in there so I can read it from Bloglines.