Monday, October 28, 2013

Dynamic Filtering

Dim filterValues() As String

'add all required values to the filter
'actvate the sheet that has filter
'you can use a dynamic range and filter column as well
'Field corresponds to column used to filter

ActiveSheet.Range("$A$1:$Z$6000").AutoFilter Field:=6,_
 Criteria1:=filterValues, Operator:=xlFilterValues

Friday, October 4, 2013

Handy Functions for Tables (ListObject)

Functions to get row number or column number for an active cell selected in the table

Function GetActiveCellTableRowNum() As Long
    Dim currTable As ListObject
    Set currTable = ActiveCell.ListObject
    If currTable Is Nothing Then
        GetActiveCellTableRowNum = -1
        GetActiveCellTableRowNum = _ 
            ActiveCell.Row - currTable.HeaderRowRange.Row
    End If
End Function

Function GetActiveCellTableColNum() As Long
    Dim currTable As ListObject
    Set currTable = ActiveCell.ListObject
    If currTable Is Nothing Then
        GetActiveCellTableColNum = -1
        GetActiveCellTableColNum = _
           currTable.ListColumns(ActiveCell.Column - _
           currTable.HeaderRowRange.Cells(1).Column + 1).Index
    End If
End Function

Monday, September 30, 2013

Validation in VBA using Regular Expressions (RegEx)

Will keep appending the validations I use (and have to think/ research before using) in this post. 

'check if it is alpha numeric
 Dim theRegex As Object
 Set theRegex = CreateObject("VBScript.RegExp")
 With theRegex
      .MultiLine = False
      .Global = True
      .IgnoreCase = False
 End With
 theRegex.Pattern = "([^A-Z^a-z^0-9^\s])"

 Set MyMatches = theRegex.Execute(nameStrRefToCompare)

 If MyMatches.Count <> 0 Then
     MsgBox ("Can have spaces, text, and numbers only")
     'do something else
 End If

Tuesday, September 24, 2013

Taking User Inputs

Post MBA I am working in Financial Services. Programming skills are, nevertheless, handy with excel and VBA. Again, just collating things that I looked for while solving problems. Not a VBA expert, so some ways may not be most efficient, but they work. For many things you can simply record a macro and play with it. For other tasks you have to do a bit of research.

To get string input:

Dim strInput As String
strInput = InputBox(Prompt:="Please input a value:", _
                Title:="Box Title")

To get range input:

Dim inputRange As Range
On Error Resume Next 'needed in case user does not select
Set inputRange = Application.InputBox _
        (Prompt:="Please select a range:", _
         Title:="Select Range", _


If inputRange Is Nothing Then

      MsgBox ("No value selected")
      'do something
End If