Insert/Update

Somewhere between heaven and hells

2010-04-26T12:03:00.000-07:00

No more declarations. No more "to be continued...". It obviously does not work for me. Instead let me tell you about a tool which I've found recently which gets you closer to heaven. Or maybe further from hell - IE hell.

Programmers and web developers are well familiar with many different kinds of hell - DLL hell, JAR hell, etc. What I mean by IE hell is the situation when in order to test my newly created web page for compatibility with different versions of IE browser I have to run around in circles, begging my collegues to let me use their computers for a while. How come? It's simple. Most of us updated IE to version 8 and only several people were clever enough to resist the temptation. Now, all of us "I've got the newest IE8" wise guys depend on those who have "It doesn't work" IE7 or "Wow, I will have to rewrite this CSS especially for you" IE6 browsers.

This makes me a saaaaad panda (Southpark fans know what I mean).

Being determined to make something about it, recently I've started to look for a way to make all versions of IE work on a single computer. Moreover, this computer had to be my laptop not a server hosting a number of virtual machines - one for each IE version. And yesterday I found it.

Remedy to all my current IE problems is called IETester. I've tested it myself and it has already turned out to be helpful for some of my collegues. It simply does it's job! All IE versions in one box and working! Brilliant! It may sound like a commercial or sponsored article, but who cares! I'm truely enthousiastic about my finding! IETester rocks!

Laziness is a true curse!

2010-02-26T12:56:00.000-08:00

It's been a month since I wrote my last post... and I'm not proud of this. Let's leave it and realize at least some of the promises I made weeks ago.

Subroutine Update_Row_Colors() works in the following way:

for each row between 1 and 9999 it checks wheather text in column C matches word "red" or not. In the first case, cells in columns A to F of the current row are coloured red. In the second case, the cells get no colouring.

This idea is translated to VBA as follows:

The variables of appropriate types are declared. Declaration of variable type may be ommited, however for some reasons it is a good practice to provide it.

Dim LRow As Integer Dim LCell As String Dim LColorCells As String

LRow variable keeps number of the current row. LCell keeps name of the cell which may contain word "red". LColorCells keeps string describing range of cells that must be coloured if the match was found.
Each row is processed separately in while loop which is executed as long as value of LRow variable (incremented by one in each step of the loop) is less than 10000.

'Start at row 1 LRow = 1 'Update row colors for the first 10000 rows While LRow < 10000 ... LRow = LRow + 1 Wend
For each row values of variables LCell and LColorCells are appropriately set. '&' is the VBA concatenation operator, glueing values together to make a single string.

LCell = "C" & LRow 'Color will changed in columns A to F LColorCells = "A" & LRow & ":" & "F" & LRow

Then text in cell in column C is checked and if its red appropriate actions are taken. ColorIndex property of the cell range interior is set to 3 (index of red colour in the Excel colour palette) and background pattern is set to solid if the mach is found. Otherwise cell range gets no styling.

Select Case Range(LCell).Value 'Set row color to light yellow Case "red" Range(LColorCells).Interior.ColorIndex = 3 Range(LColorCells).Interior.Pattern = xlSolid 'Default all other rows to no color Case Else Rows(LRow & ":" & LRow).Select Range(LColorCells).Interior.ColorIndex = xlNone End Select

In my next post I would like to show how to use Excel colour palette to pick the colour we want to use and how to interactively provide text we want to look for in cells.

Laziness is a true virtue!

2010-01-29T13:12:00.000-08:00

Being lazy is a virtue. Especially if what you have on your mind is not the coach potato kind of laziness but something more like "I will spend this hour working to save fifteen minutes of some dull everyday work" laziness. Generally, this blog will be about me being lazy and happy so better get used to it.

Example. Your boss gives you an Excel spreadsheet, 10 thousand rows of data, and tells you that all those rows which have text 'red' in column C should have green background. You can check each row and spend whole day updating the document. You can reduce time spent on this fascinating task to two hours being clever and using the 'Search' option.

But you can do it in a single mouseclick! However, first you have to write a macro, a small piece of code that will do the work for you. Macros for Excel are written in Visual Basic for Applications (VBA) language. And here is the solution to our problem, row-colouring function.

Sub Update_Row_Colors()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 1
    LRow = 1

    'Update row colors for the first 10000 rows
    While LRow < 10000

       LCell = "C" & LRow
       'Color will changed in columns A to F
       LColorCells = "A" & LRow & ":" & "F" & LRow
       Select Case Range(LCell).Value
           'Set row color to light yellow
           Case "red"
               Range(LColorCells).Interior.ColorIndex = 3
               Range(LColorCells).Interior.Pattern = xlSolid
           'Default all other rows to no color
           Case Else
               Rows(LRow & ":" & LRow).Select
               Range(LColorCells).Interior.ColorIndex = xlNone
       End Select

       LRow = LRow + 1
    Wend Range("A1").Select

End Sub

I had a very similar problem to solve recently. For a couple of days I was doing such colourings manually. Finally I was fed up with this enough to find different solution at this page. The above code is based on the one I found at Tech on the Net.

In the future I would like to extend this example to make it more flexible. If you want to know what interesting can be done about it, follow me on this blog. Believe me - I am about to prepare even more lazy version of it.

Next time I will start from going line by line through the code to describe in detail what is really happening there and then I will move on to showing some lazy extensions. Stay tuned!

The First Post

2010-01-27T13:05:00.000-08:00

Hello Blogger! Hello World!