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