Firebug - web development tool

It's a very useful web development tool that can help us to learn the HTML, CSS, or even javascript when we browse the net. Not only that we can read the codes, but also edit, debug and monitor them live. Just click on the item on the webpage, then the codes will be revealed. There, you can also edit the codes and see the changes.



The tool also provides network monitoring feature. It helps us to break down the files, so that we can speed up the web page loading.

By the way, it's an add-on to Firefox. You can just install it, and you can open the Firebug as a bar at the bottom of your browser, or even in a separate window.

Click here to download the free firebug.

How to check the target’s range name?

I am working on the Excel file, for show and hide certain ranges based on the selection. Say if user selects “Yes”, then I show certain range. Otherwise, I’ll hide that range.

I do the coding in the Worksheet_Change event. I check if the target is the relevant cell/range, then only I perform the function accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Option 1 Selected

    If Target.Address = "$I$21" Then SelectOption1

End Sub

But, this is hardcoded, whenever the cell address has been changed, say insert a new row or remove a row, then I21 is no longer the cell I’m referring.

I just found a way to solve this “hardcode” problem – Define range name. But how can I check if the range name is my defined one? As I’ve tried Target.Address and Target.Name, both return me the cell address.

Finally, I’ve found this:

If ThisWorkbook.Worksheets("SheetName").Range(Target.Address).Name.Name = "DefinedRangeName" Then SelectOption1

Range(Target.Address).Name.Name will return the defined range name. By using this, I do not need to hardcode the cell address anymore, and it saves lots of maintenance work in future. ^_^

Unhide hidden sheets in Excel

I am doing an Excel task using Macro. I hide most of the worksheets at the start of the Excel file, until user did some selections to show/hide certain worksheets.

Now, I am asked to allow the users to unhide the hidden worksheets in the Excel file. I have searched around in the Excel file, but failed to get so. Then I realized, I have to change how I hide the worksheet in Macro codes.

This is how I used to hide worksheet:

ThisWorkbook.Worksheets(sheetIndex).Visible = xlSheetVeryHidden

Just change xlSheetVeryHidden to xlSheetHidden will do.

Using xlSheetVeryHidden can only unhide certain worksheets in Macro.

To unhide hidden worksheets:

1. Go to Format menu.

2. Click on Sheet, then Unhide.

3. You’ll get a list of hidden worksheets, just select the worksheet name and click on OK button to unhide it.

Convert Column Number to Letter and Vice Versa in Excel

Sometimes when I built the application for Excel, I’ll need to convert the Column number to Letter (say, 3 is C) or vice versa.

I’ve been searching around, and finally found the following methods which are quite useful and simple. They cater for column letter like “AB” too.

Function: Convert column number to letter ( 2 –> “B”)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. 2 --> "B") 

   3: '   Parameters:     The column number (Long) 

   4: '   Return:         The column letter (String) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnNumberToLetter(ByVal ColumnNumber As Long) As String 

   7:  

   8:     Dim s0 As String, s1 As String, S2 As String, s3 As String 

   9:     If ColumnNumber > 18278 Then s0 = Chr((Int((ColumnNumber - 18279) / 17576) Mod 26) + 65) 

  10:     If ColumnNumber > 702 Then s1 = Chr((Int((ColumnNumber - 703) / 676) Mod 26) + 65) 

  11:     If ColumnNumber > 26 Then S2 = Chr(m_ConvertColumnNumberToLetter & (Int((ColumnNumber - 27) / 26)) Mod 26 + 65) 

  12:     

  13:     s3 = Chr(((ColumnNumber - 1) Mod 26) + 65) 

  14:     m_ConvertColumnNumberToLetter = s0 & s1 & S2 & s3 

  15:  

  16: End Function 

Function: Convert column letter to number (“B” –> 2)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. "B" --> 2) 

   3: '   Parameters:     The column letter (String) 

   4: '   Return:         The column number (Long) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnLetterToNumber(ByVal ColumnLetter As String) As Long 

   7:  

   8:     Dim intCount                As Integer 

   9:     Dim intColumnLetterLength   As Integer 

  10:     m_ConvertColumnLetterToNumber = 0: intColumnLetterLength = Len(ColumnLetter) 

  11:     For intCount = 1 To intColumnLetterLength 

  12:         m_ConvertColumnLetterToNumber = m_ConvertColumnLetterToNumber * 26 + (Asc(UCase(Mid(ColumnLetter, intCount, 1))) - 64) 

  13:     Next intCount 

  14:  

  15: End Function