Back to XL VBA : A Way to create Utility functions to increase reusability

Hi there!

I normally create a Util Module every time I work on an Office Automation Project. I thought, people may be benefitted with it. Moreover, I will not have to create it again. I will copy it from here only ;)

Public Function SheetExists(wkb As Workbook, shtName As String) As Boolean
    Dim sht As Worksheet
    
    Set sht = Nothing
    On Error GoTo errHandler
    Set sht = wkb.Worksheets(shtName)
    SheetExists = True
    Exit Function

errHandler:
    SheetExists = False
End Function

Public Function GetSheet(wkb As Workbook, shtName As String) As Worksheet
    Dim sht As Worksheet
    
    Set sht = Nothing
    If (SheetExists(wkb, shtName)) Then
        Set sht = wkb.Worksheets(shtName)
    End If
    
    Set GetSheet = sht
End Function


Public Function RangeExists(wkSht As Worksheet, rngName As String) As Boolean
    Dim rng As Range
    
    Set rng = Nothing
    On Error GoTo errHandler
    Set rng = wkSht.Range(rngName)
    RangeExists = True
    Exit Function

errHandler:
    RangeExists = False
End Function

Public Function GetRange(wkSht As Worksheet, rngName As String) As Range
    Dim rng As Range
    
    Set rng = Nothing

    If (RangeExists(wkSht, rngName)) Then
        Set rng = wkSht.Range(rngName)
    End If
    
    Set GetRange = rng
End Function


Public Function GetRows(wkSht As Worksheet, Optional columnName As String = "A") As Long
    Dim rowCounter As Long
    Dim runLoop As Boolean
    Dim startRange As Range
    
    rowCounter = 0
    runLoop = True
    
    Set startRange = wkSht.Range(columnName & "1")
    
    While runLoop
        If (Len(startRange.Offset(rowCounter, 0).Value) > 0) Then
            rowCounter = rowCounter + 1
        Else
            runLoop = False
        End If
    Wend
    
    GetRows = rowCounter
    
End Function

Public Function GetColumns(wkSht As Worksheet, Optional rowNum As String = "1") As Long
    Dim colCounter As Long
    Dim runLoop As Boolean
    Dim startRange As Range
    
    colCounter = 0
    runLoop = True
    
    Set startRange = wkSht.Range("A" & rowNum)
    
    While runLoop
        If (Len(startRange.Offset(0, colCounter).Value) > 0) Then
            colCounter = colCounter + 1
        Else
            runLoop = False
        End If
    Wend
    
    GetColumns = colCounter
    
End Function

Thanks,
Vikas

Designing a Button in WPF - "Simplest yet cool Button"

Hi All,

I have seen a lot of button templates which offer a cool looking styles by overriding the complete button template. Just to give a small style to a button can be complicated as far as overriding the template is concerned. I tried to build a button something like this :



An option could be, to include a new style for my button and override the template. But do we really need to do that???? I don't think so. The button which you are seeing in the Image is actually not a button, it is actually a border object filled with Alice Blue color, and with the corner radius set to 5. There is a textblock object inside the border so that it can contain some text. I didn't use label for that because textblock is lighter than a label.




Now, this control is not yet completed because it doesn't give any notification whenever it is clicked. So I had to include some animation to this button.




Here, I have opted to use code behind to run my animation. Like shown in the figure below :




After including the above code lines, you can see that the border looks like a button and animates as well once you click the control.

HTH,
Vikas

WPF : Changing Listview Selected Item color

Hi All,

I am really loving WPF these days, all of the things which looked really tough to me earlier, are not simplified using WPF XAML.

Here is the code for changing the background color of the selected item in a listview :
Code :

< Style TargetType="ListViewItem">
< Style.Resources>
< SolidColorBrush x:Key="{x:Static SystemColors.HighlightBrushKey}" Color="WhiteSmoke"/>
< SolidColorBrush x:Key="{x:Static SystemColors.ControlBrushKey}" Color="Transparent"/>

Thanks,
Vikas

Binding Checkbox with the Listview Item Selection

Hi All,

I know, lot of people juggle around for this. I also did ;)

Have you ever wondered to have a checkbox in a listview item which should be checked/unchecked as per the listviewitem selection and vice versa?

Suppose if you have a checkbox as a listview item, and you want to check/uncheck the checkbox as soon as user selects the listview item. And vice versa, the listview item should be selected as soon as user selects the checkbox. The following line solves this purpose :



HTH,
Vikas Bhandari