Office Apps Tips & Techniques

On various occasions whilst working with Office applications, I came across certain time consuming repetitive tasks that could be tedious if done manually, which inspired me to get the job automated, therefore I wrote few (VBA) Macros that proved to be very useful often time-savers. These macros might be useful to those who may find themselves in similar situation, hence I would like to share these.

ProperCase with 1st character bold:

While writing tip-sheets (user guides) for one of the web-applications that we developed, the team came across a repetitive time consuming task. Our tip-sheets had definitions of many acronyms and abbreviations (many listed in appendices) and it transpired that all the definitions of acronyms need to be highlighted in certain way (with 1st character capitalise and bold, e.g. HTTP = Hyper Text Transfer Protocol.)

It would have been a tedious job if we were to do this manually, hence I wrote a quick macro to assist us, which is as below:

To highlight all 1st letters of each word in a sentence, simply select the sentence then run the macro (assign a keyboard shortcut for quick run). Here is the macro code:

SHORT VERSION:
Sub HighlightFirstChars()
Dim cnt As Integer
cnt = Selection.Words.Count

'moving (without wdExtend) cursor to the left/beginning of the selection:
Selection.MoveLeft Unit:=wdCharacter, Count:=1

'running a loop to select the 1st character then highlight it:
For i = 1 To cnt
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Font.Bold = wdToggle
Selection.Range.Case = wdUpperCase
Selection.MoveRight Unit:=wdWord, Count:=1
Next i
End Sub

'Note: Above short-version does not distinguish between a significant and a non-significant parts of sentence (e.g. 'and', 'of', '&') and therefore highlights (1st character of) all selected words regardless.

LONG-VERSION:

'Ver4: Excludes all non-alphabets & certain words.
Sub HighlightFirstChars()
Dim cnt As Integer: cnt = Selection.Words.Count
Dim regEx As New RegExp
regEx.Pattern = "[a-zA-Z]" 'requires: VBE>Tools>Reference: "Microsoft VBScript Regular Expressions 5.5".

Dim excl() As String
excl() = Split("and, of, for, to, with, in, on, a, an, the", ", ") 'One-liner array assignment.

'move (without wdExtend) cursor to the left/beginning of the selection:
Selection.MoveLeft Unit:=wdCharacter, Count:=1

'running a loop to select the 1st character then highlight it:
'For Each w In Selection.Words 'this won't work here as we need to alter the Words in selection.
For i = 1 To cnt
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend 'select single word
'MsgBox (Selection) 'check which word
'If (Selection = "of " Or Selection = "for " Or Selection = (Chr(34) & ","))
'postfixing-whitespace is essential i.e. "of " instead of "of" otherwise offline/often etc. will also be omitted.
For Each x In excl
If (InStr(Selection, x & " ") > 0) Then
Selection.MoveRight Unit:=wdWord, Count:=1
GoTo NextIteration
End If
Next x
'above array-iteration replaces need of following Loooooong if statement:
'If (InStr(Selection, "of ") > 0 Or InStr(Selection, "for ") > 0 Or InStr(Selection, "and ") > 0 Or InStr(Selection, "to ") > 0) Then

Selection.MoveLeft Unit:=wdWord, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
'check if selected character is an alphabet:
If (regEx.Test(Selection)) Then
Selection.Font.Bold = wdToggle
Selection.Range.Case = wdUpperCase
End If
Selection.MoveRight Unit:=wdWord, Count:=1
NextIteration:
Next i
End Sub

------------ x -------------

Finding a date in a range of dates:

On another occasion, we had an Excel spreadsheet (called Team-Rota) containing thousands weekly dates in a column and team members often complaint that it was time consuming to look for current week. So I ended up writing another quick Macro and added a button on the spreadsheet for users' ease. Here is the code (in case someone finds him/herself in similar situation):

Following VBA code takes the cursor or cell-pointer (as Goto command) to the current week Assumption: Worksheet contains a range/column listing weekly-dates (e.g. A1:A300 change as required).

Note: One of the easiest way to compare dates in VBA is to convert them into long values using CLng() function, see used in the example below.

Sub curr_week()

Dim cell As Range, _

wcf As Range

Dim today As Long 'must compare dates in Long otherwise problematic.

today = CLng(Date) 'converting current date into Long.

For Each cell In Range("A1:A300")

'Set wcf = Range("d1").Find(cell.Value)

If IsDate(cell.Value) And Not IsEmpty(cell.Value) Then

If today >= CLng(cell.Value) And today < CLng(cell.Offset(1, 0).Value) Then

Set wcf = cell

End If

Next cell

If Not wcf Is Nothing Then

Debug.Print wcf.Value 'Ctrl+G to open Immediate window where Debug-stmts print.

Application.Goto wcf, True 'Application.Goto requires a range.

Else

MsgBox "Date range not found, Eddie to check Macro."

End If

End Sub

VBA Notes & Tips

ADO (ActiveX Data Objects):

ActiveX Data Objects (ADO) consists of the following components:

1) ADO: ADO enable your client applications to access and manipulate data from a variety of (db) sources through an OLE-DB provider. Its primary benefits are: ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

2) ADO MD: ADO Multi-Dimensional (ADO MD) provides easy access to multidimensional data, it extends the ADO to include interaction with multidimensional data, such as the CubeDef and Cellset objects. With ADO MD you can browse multidimensional schema, query a cube, using VB & Visual C++.

3) RDS: Remote Data Service (RDS) is a feature of ADO, with which you can move data from a server to a client application or Web page, manipulate the data on the client, and return updates to the server in a single round trip. However from Windows 8 & Windows Server 2012, RDS server components are no longer included, hence Applications that use RDS should migrate to WCF Data Service.

4) ADOX: Microsoft ADO Extensions provides features as Data Definition Language (DDL) and Security (ADOX). ADOX includes objects for schema creation and modification, as well as security.

Note: Microsoft WCF Data Services: enables the creation and consumption of OData services for the web (formerly known as ADO.NET Data Services).

Vb Project Supporting Technologies:

• SimplyVBUnit : Unit test for VB.

• NSIS : Customisable Installer exe/msi creator.

• AutoIt : Windows script to automate GUI interface.

Enabling Developer tab/Ribbon:

File -> Options -> Customize Ribbon -> Main Tabs (list) -> Developer (checkbox) -> OK.

Now on Developer ribbon, click Visual Basic or press Alt + F11 to open VBE (VB Editor).

Creating Shortcuts of Commands or Macros as Icon & Keyboard-Shortcut:

MS-Office 2k/2003 (Pre-2007):

To add an icon on toolbar:

- Tools > Customize > Commands > (From "Categories") Macros > (Drag & Drop the macro).

To add a Keyboard-Shortcut:

- Tools > Customize > Toolbars > Keyboard.. > (From "Categories") Macros > (After selecting macro, put the cursor in "Press new shortcut key" textbox then press shortcut).

MS-Office 2007/2010/2013 (Post-2007)

To add a Macro's icon on toolbar:

- File (or "Office button" @ top-left) -> Options -> Customize Ribbon (or Customize) -> (From "Categories" select) Macros -> (create new ribbon or group) move item.

To Add or Assign Keyboard-Shortcut to Macro (Assigning Keys):

- File (or "Office button" @ top-left) -> Options -> Customize Ribbon (or Customize) -> (bottom left) Keyboard Shortcuts: "Customize" (button) -> Macros -> (scroll down "Categories" in "Customize Keyboard" dialogbox) select 'Macros', (then put the cursor in) "Press new shortcut key" textbox then press desired shortcut on keyboard -> Assign (button) -> Close Ok -> Close MS-Word, now you will be asked whather to save changes made in the 'Normal.dot', click Yes. That's it

Adding an item in menus

(OO 3.2 has no "record macro" button to add it follow the steps below:)

the menu item for record macro is not available but you can make it avalilable.

- Post-2007: View -> Macros -> Record Macro.

- Pre-2007: Tools > Customize > (tab) Menus > (listbox select) "Tools | Macros" > (button) Add > (In Categories select) Basic > (In Commands select) "Record Macro" > (Click button) Add & that's it. > Close > (Click) OK.

Adding a button

There are 2 types of buttons:

18. Form control button and

19. ActiveX control command button (has some extra functionality/appearance)

GUI: Adding button & assign macro to it in worksheet

Developer tab -> Controls group -> Insert -> under Form Controls -> 1) Button OR 2) ActiveX Controls, click Command.

-> Click the worksheet (where you want button to appear.) Assign Macro dialog box appears (simply do this).

Note: For Button-Properties: Right-click the button -> Format Control.

VBA-Code: Adding button & assign task:

Sub button_test()

ActiveSheet.Buttons.Add(10, 10, 100, 50).Select 'parameters: (X-postion, Y-postion, Width, Height)

Selection.Characters.Text = "MyButton"

With Selection.Font

.Bold = True

.Size = 12

.ColorIndex = 5

End With

Selection.OnAction = "ThisWorkbook.test"

End Sub

Sub test()

Sheets.Add before:=Sheet(1)

ActiveSheet.Name = "Added by MyButton"

End Sub

ScreenUpdating

Sytax:

Application.ScreenUpdating = False/True

Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.

Remember to set the ScreenUpdating property back to True when your macro ends.

VBE (VB Editor)

Multi-line Statement (Line-Breaks in VBA code)

Use an underscore ( _ ) to break a statement and continue writing (the same statement) on next line in VBE. E.g.

If Not MyRange.Paragraphs(1).Range.End _

= MyRange.Sections(1).Range.End Then

In other words, to break a line of code, you put a space followed by an underscore (_). You can break a line between any keywords or values, so you can use line breaks freely to make your code easier to read.

VBE: Multiline Comment Uncomment Block:

"Comment Block" is not be present by default on your toolbar in VBE. To bring these icons on toolbar:

 right click a toolbar in the VBE and

 choose "Customize"

 On the Commands tab,

 choose "Edit" and

 scroll down the Commands list & find the icon for "Comment Block" and "Uncomment Block".

 Drag to a toolbar. That's it.

VBE's Debug.Print : (similar to console.log)

In VBA, messages being output via Debug.Print will be displayed in the immediate window which can be opened by pressing Ctrl+G OR by clicking View -> Immediate Window on the VBE toolbar (Immediate Window also displays all the active / live variables.)

VBE Testing Summary:

• Use Debug.Print() to output the test data (Don’t use Debug.Write() and Console.WriteLine() etc.)

• Messages being output via Debug.Print() will be displayed in the Immediate Window which you can open by pressing Ctrl+G.

• You can also Activate the so called Immediate Window by clicking View -> Immediate Window on the VBE toolbar.

What are SET & LET keywords do in VBA?

'Set' keyword assigns an object reference. For all other assignments the (implicit, optional, and little-used), Let statement can be used to for a literal assignment:

Set object = New SomeObject

Set object = FunctionReturningAnObjectRef(SomeArgument)

Let i = 0

Let i = FunctionReturningAValue(SomeArgument)

Let is optional hence following are valid:

i = 0

i = FunctionReturningAValue(SomeArgument)

But following is INvalid:

Set i = 0

Note: SET keyword is not required and not supported in .net framework.

Return, GoTo, Break (Exit in vba) & Continue (no equivalent in vba):

There are 4 usual GoTo variations, the GoTo itsef and its accomplices Break (Exit in vba), Continue (no equivalent in vba) and Return (also Return in vba). Any of the 4 may break the flow of the program (used inside a loop may even break the logic of the loop).

Notes:

20. Exit <scope>: you must specify the <scope> in VBA, e.g. Exit Loop, Exit Sub etc.

21. The use of GoTo is usually considered non structured programming.

Using GoTo as alternative to "Continue":

For/Do/While

' code'

If Condition then GoTo EndLoop

' more code'

EndLoop:

End For/Loop

Select or Activating specific Spreadsheet:

1) Worksheets(sheetname).Activate

2) Sheets(sheetname).Select

Double-Double Quotes in string

22. The easiest way is to double up """" on the quotes to handle a quote.

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)"

23. Alternatively use CHR(34)* OR WorksheetFunction.Char(34) :

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"

*Note: CHAR() is a Worksheet Functions hence is used in Excel cell formulas, e.g. "=CHAR(34)" in a cell, but for VBA code you use the CHR() function.

On Error Resume Next

Whenever an insignificant error is expected, the following code must be used:

On Error Resume Next

Err.Clear

VBE Assumes (Default Available Objects in VBE)

1. Application is assumed

2. ActiveWorkbook is assumed

3. ActiveSheet is assumed

4. Value property is assumed if you only write a range, as it is the default property of the Range object. Therefore, following line of code:

Range ( "A1" ) = 42

Is interpreted as:

Application.ActiveWorkbook.ActiveSheet.Range( "A1" ) . Value = 42

All VBA Functions (including WorksheetFunctions):

CountA(): WorksheetFunction.CountA()

Counts the number of non-blank cells, i.e. only counts the non-empty cells in a range.

WorksheetFunction.CountA(columns(1))

InStr(): Finds Substring in a String:

Finding a substring within a String using InStr() function, often used within Loop, see also Find() function to search something without loop.

Note: The first character is at position 1.

Syntax:

InStr( [starting_position], string_being_searched, search_string, [compare] )

Example:

s = "Chilkat Software"

' The following 4 lines are case-sensitive

Print InStr(s, "Software") ' Prints 9

Print InStr(s, "Chilkat") ' Prints 1

Print InStr(s, "xyz") ' Prints 0

Print InStr(s, "software") ' Prints 0

' The following 3 lines are case-insensitive (as passing 4th parameter "vbTextCompare" for case-insensitive search):

Print InStr(1, s, "software", vbTextCompare) ' Prints 9

Print InStr(1, s, "chilkat", vbTextCompare) ' Prints 1

Print InStr(1, s, "xyz", vbTextCompare) ' Prints 0

Searching String

(See also: String Comparison in VBA & Searching string in array)

1. LookUp()

2. Match()

3. Find()

Lookup()

https://msdn.microsoft.com/EN-US/library/office/ff192952.aspx

Match():

https://msdn.microsoft.com/en-us/library/office/ff835873.aspx

e.g.:

Public Sub test()

Dim rng As Range: Set rng = Sheet1.Range("B1:B20")

Dim x As Variant: x = "2gg"

Dim rowNum As Variant: rowNum = Application.Match(x, rng, 0)

If Not IsError(rowNum) Then

MsgBox rowNum

Else

MsgBox "error"

End If

End Sub

Find():

https://msdn.microsoft.com/EN-US/library/office/ff198169.aspx

Searches value(s) in a Range. It returns a Range-object that represents the first cell where that information was found. This is often used with FindNext() or FindPrevious() functions.

Syntax:

RangeObj rangeObj.Find(<What>, [After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat])

Examples:

Set c = rng.Find(5)

OR, typical use:

Dim cell As Range

Set cell = Range("C:C").Find(What:="0", LookAt:=xlWhole)

If Not cell Is Nothing Then msgBox cell.Row

rng.Find(

What:="my string to search",

After:=ActiveCell,

LookIn:=xlValues | xlF,

LookAt:=xlPart,

SearchOrder:=xlByRows,

SearchDirection:=xlNext,

MatchCase:=False,

SearchFormat:=False

)

Finding Value In Range

Sub FindingValueInRange()

'Finding Values: use LookIn:=xlValues (not required as xlValues is default for LookIn).

Set aCell = Range("C1:C18").Find("hello")

If Not aCell Is Nothing Then

MsgBox aCell.Value

End If

End Sub

Finding Formula In Range

Sub FindingFormulaInRange()

'Finding Formulas: use LookIn:=xlFormulas (for case insensitive search use: MatchCase:=False)

Set aCell = Range("C1:C18").Find("Concatenate", LookIn:=xlFormulas)

If Not aCell Is Nothing Then

MsgBox aCell.Formula

End If

End Sub

FindingInRange() Both Values & Formula:

Sub FindingInRange()

'Finding Values: use LookIn:=xlValues (not required as xlValues is default for LookIn).

'Set aCell = Range("C1:C18").Find("hello")

'Finding Formulas: use LookIn:=xlFormulas (for case insensitive search use: MatchCase:=False)

Set aCell = Range("C1:C18").Find("Concatenate", LookIn:=xlFormulas)

If Not aCell Is Nothing Then

MsgBox """" & aCell.Formula & """" & " - Found in Cell " & aCell.Address & vbCrLf & _

"and it took " & GetTickCount - t & " milliseconds"

Else

MsgBox "not found"

End If

End Sub

Finding Multiple Times In Range():

Sub FindingMultiTimesInRange()

Dim aCell, firstMatch, rng As Range: Set rng = Range("C1:C18")

Dim foundAt, searchStr As String: searchStr = "Advice"

Set aCell = rng.Find(searchStr)

If Not aCell Is Nothing Then

Set firstMatch = aCell

foundAt = aCell.Address

Set aCell = rng.FindNext(After:=aCell)

If aCell.Address = firstMatch.Address Or aCell Is Nothing Then

Exit Do

End If

foundAt = foundAt & ", " & aCell.Address

Loop

MsgBox "The Search String has been found in these locations: " & foundAt

End If

End Sub

Format(): Formatting Date:

MsgBox ("The formatted date is " & Format(#5/31/1993#, "dddd, d MMM yyyy"))

Move() functions

Move(), MoveLeft(),MoveRight(),MoveStart():

Syntax Move():

returnValue = instance.Move(Unit, Count)

Unit

Type: System.Object%

Optional Object. The unit by which the selection is to be moved. Can be one of the following

WdUnits constants: wdCharacter, wdWord, wdLine, wdSentence, wdParagraph, wdSection, wdStory, wdCell, wdColumn, wdRow, or wdTable. The default value is wdCharacter.

Syntax MoveLeft / Right():

expression.MoveLeft(Unit, Count, Extend)

Moves the selection to the left and returns the number of units it's been moved.

Note: Unlike Move()/MoveStart() MoveLeft() / MoveRight() can move cursor as well as can select text, if wdExtend is specified.

All above methods return an Integer value indicating the number of units by which the object was actually moved, or it returns 0 (zero) if the move was unsuccessful.

Extend: wdExtend v/s wdMove:

Both move the cursor, the difference is:

wdExtend: selects text as well as moves the cursor, meaning as if you have 'Shift' key pressed when you are move cursor, on the other hand, default Extend value is usually wdMove.

wdMove: only moves the cursor without selecting, meaning (no inverted text visible),

For example:

Selection.HomeKey Unit:=wdStory, Extend:=wdExtend (is-equal-to) Ctrl+Shift+Home,

Selection.HomeKey Unit:=wdStory, Extend:=wdMove (is-equal-to) Ctrl+Home.

Type Conversion in VBA:

VB Boolean to Numeric Conversion:

Same as most languages, in VB also, 0 is false and everything else is true, therefore, when you convert a numeral into a boolean, 0 becomes False and all other values become True. When you do opposite i.e. convert Boolean into numeric types, False becomes 0 and True becomes -1. Note that it's best practice to explicitly use Boolean True or False where needed, instead of relying on the auto-conversion (auto-cast) of non-boolean values into booleans.

Type Conversion Functions in VBA:

CBool(), CByte(), CChar(), CDate(), CDbl(), CDec(), CInt(), CLng(), CObj(), CSByte(), CShort(), CSng(), CStr(), CUInt(), CULng(), CUShort().

See: https://msdn.microsoft.com/en-us/library/s2dy91zy.aspx

Compare Concatenation Operators: & and +

Both concatenate strings but + might add them instead depending on type conversions, whereas & guarantees you won't get anything except a string concatenation. E.g.

5 + 2 => 7

5 & 2 => 52

List of Common VBA Properties

ActiveCell's Row number & Column name/number:

ActiveCell.Row

ActiveCell.Column

Total Rows & Columns in entire sheet:

Rows.Count

Columns.Count

String Comparison in VBA:

There are 4 ways to do the string comparison:

1. using =, <, > Operators

2. using LIKE operator

3. using InStr() function, and

4. using StrComp() function.

1. using =, <, > Operators: binary comparison only (by default: Option Compare Binary)

o "This" = "this" 'FALSE as case-sensitive

o Option Compare Text (Note: Option Compare Binary|Text must be declared at Module or Global level NOT inside a Sub or Function)

"This" = "this" 'TURE

2. using LIKE operator: (str1 LIKE str2)

o Wildcards (* ? #):

? Indicates any single character, repeat for multiples. (Ex. “?ip” equates to “Tip”, “Hip” or “Lip”).

* Indicates zero or more characters in a sequence. (Ex – “*ike” = “ike”, “Mike”, “Hike”, “Strike”)

# Indicates a single digit from 0 to 9, repeat for multiples. (Ex. “#th” equates to “4th”, “5th” etc.)

o Patterns: For example, both the below expressions would both evaluate to TRUE:

“this” Like “*[a-z]his” OR “this” Like “*[s,t,u]his”

However following is FALSE:

“this” Like “*[x,y,x]his”

Range:

“*[A-C]one” equates to (A or B or C)+one

exclusion set using ! symbol within bracket [!]

*[!a-c] to exclude the characters a,b and c

“this” Like “*[!s-u]his” ' will evaluate to FALSE

3. InStr("str1","str2") : 'returns position of 2nd string in 1st string. (Note: by default: Option Compare Binary)

Syntax:

InStr ( [“position_to_start”,] “string_to_search”, “string_to_find” [,”comparison_type”])

here:

position_to_start = The position where you would like to function to begin the comparison (optional)

comparison_type = whether comparison is binary (case-sensitive) or text (case-insensitive) (optional)

o INSTR() function returns the position of the string_to_find within the string_to_search starting from the first position. If it is not able to locate the sub-string, it will return 0. If any of the strings is NULL, it returns a NULL. If the string_to_find is a zero length (say “”),

o Example:

INSTR(“This is a string”, “string”)

'=> returns 11 (as the given sub-string starts at position 11).

4. StrComp() function: compares ASCII values starting from 1st position (Note: by default: Option Compare Binary)

Syntax:

StrComp(“first_string_to_compare”, “second_string_to_compare”,“comparison_type”)

here:

comparison_type = whether comparison is binary (case-sensitive) or text (case-insensitive) (optional)

Note: if comparison_type is not explicitly specified, then the value specified in the Option Compare Text|Binary (at Global or Module level) will be used.

 The function returns:

-1 when str1 < str2

0 when str2 = str1

1 when str1 > str2

Null when either of the strings is NULL

o Example:

MsgBox StrComp("a", "b") '=> -1

MsgBox StrComp("ping", "bing") '=> 1

MsgBox StrComp("ping", "ping") '=> 0

Option Compare Binary|Text|Database:

The Option Compare determines how a text/String comparison is conducted, it must be declared at Module or Global level NOT inside a Sub or Function.

Binary: Case-sensitive.

Text: Case-insensitive.

Database: Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale-ID of the database where the string comparisons occur.

Arrays in VBA

Declaring Array:

Dim strFriends(0 to 2) As String

Assigning Values to Array:

• Assigning values to array in a single line:

o Method-1:

Dim arr 'Note: default dataype is VARIANT, if no datatype is defined.

arr = Array("a", "b", "c") 'Note: Array() method returns VARIANT datatype.

o Method-2:

Dim arr() As String

arr() = Split("a b c", " ") 'here whitespace is used as separator.

arr() = Split("a, b, c", ", ") 'here comma & whitespace are being used as separator.

• Assigning values to individual array elements:

o arr(0) = "a"

arr(0) = "b" …

Resizing Array:

ReDim cdRack(0 to 3) As String 'Beware: ReDim deletes all elements if not preserved.

Syntax:

ReDim [Preserve] ArrayName(size) As DataType

For arrays NOT dimensioned yet (at first declaration), the ReDim statement dynamically sets the array-size. It only works if the array has been previously dimensioned with empty parentheses (no subscripts), otherwise you will get "Array already dimensioned" error.

Preserve is an optional keyword that forces VBA to retain all existing elements' values. Without the "preserve" keyword all elements will be erased and return to their default values. (Numeric data types to 0, strings to "" (a zero-length string), fixed-length strings filled with zeros, and variants to empty.)

Size / Count Elements:

UBound(ArrayName) 'UBound(arr, [dimension]) e.g. size Ubound

WorksheetFunction.Count(MyArray)

For R = 1 To UBound(myArr)

Erase

Erase myArrName

Retrieval

Retrieving Entire Array: Without Iteration

Join & Display Elements:

MsgBox Join(arr, ", ")

'This will display array elements as a single string separated by comma (a,b,c)

Retrieving Entire Array: Iterating Through Array

For i = LBound(arr) To UBound(arr)

Debug.Print arr(i)

Next i

For Each x In arr

Debug.Print x

Next x

Retrieving Individual Array-Elements:

MsgBox arr(0)

MsgBox arr(1)

MsgBox arr(2)

Taking values from user: storing in array then printing on spreadsheet:

Private Sub CommandButton1_Click( )

Dim StudentName(1 to 5) As String

For i = 1 To 3

StudentName(i) = InputBox("Enter student Name")

Cells(i, 1) = StudentName(i) 'storing in the same column # 1 (or column A)

End Sub

Reading & Writing Array to Worksheet

Reading Range into Array

It is very easy to read a range on a worksheet and put it into an array in VBA. For example,

Dim Arr() As Variant ' declare an unallocated array.

Arr = Range("A1:C5")' Arr is now an allocated array

When you bring in data from a worksheet to a VBA array, the array is always 2 dimensional. The first dimension is the rows and the second dimension is the columns. So, in the example above, Arr is implicitly sized as Arr(1 To 5, 1 To 3) where 5 is the number of rows and 3 is the number of columns. A 2-D array is created even if the worksheet data is in a single row or a single column (e.g, Arr(1 To 10, 1 To 1)). The array into which the worksheet data is loaded always has an lower bound (LBound) equal to 1, regardless of what Option Base directive you may have in your module. You cannot change this behaviour.

You can use code like the following to loop through the array of the worksheet values:

Dim Arr() As Variant

Arr = Range("A1:B10")

'in above line, Arr is automatically (double) dimensioned by VBA as: Arr(1 to 10, 1 To 1).

Dim R,C As Long

For R = 1 To UBound(Arr, 1) ' First array dimension is rows.

For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.

Debug.Print Arr(R, C)

Next C

Next R

Writing A One Dimensional VBA Array To The Worksheet

Once you have calculated an array with the appropriate values, you can write it back to the worksheet. The array may be 1 or 2 dimensional.

To write a one dimensional array back to the worksheet, you must create a Range object, resize that range to the size of your array, and then write to the range.

Suppose we have a one dimensional array and want to write that out to the worksheet starting at cell K1. The code must first resize the destination range. For example,

' one row spanning several columns

Dim Destination As Range

Set Destination = Range("K1")

Set Destination = Destination.Resize(1, UBound(Arr))

Destination.Value = Arr

OR (to transpose)

Set Destination = Destination.Resize(UBound(Arr), 1)

Destination.Value = Application.Transpose(Arr)

OR (for 2-D array)

Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

OR (for transposed 2-D array)

Destination.Resize(UBound(Arr, 2), UBound(Arr, 1)).Value = Application.Transpose(Arr)

NOTE that the parameters to Resize are reversed and that the array Arr is transposed before being written to the worksheet.

Searching string in array

Good discussion @ stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array

Method-1: Simple For Each

Function isInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean

For Each element In arr

If element = stringToBeFound Then

isInArray = True

Exit Function

End If

Next element

End Function

Method-2: using Match() function - Enforce exact matching (i.e. no partial matching):

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))

End Function

read more about the Match() method at http://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx

Method-3: by creating a string containing all the array-items (separated by a delimiter)

' Returns true if item is in the array; false otherwise.

Function IsInArray(ar, item$) As Boolean

Dim delimiter$, list$

' Chr(7) is the ASCII 'Bell' Character, chosen as unlikely to be found in a normal string

delimiter = Chr(7)

' Create a list string containing all the items in the array separated by the delimiter.

list = delimiter & Join(ar, delimiter) & delimiter

IsInArray = InStr(list, delimiter & item & delimiter) > 0

End Function

VBA Errors & Exceptions:

5. Array Errors:

o "Subscript out of range" means "Array out of bounds".

o "Array already dimensioned" error: ReDim throws this error if an array has already been statically dimensioned (instead of empty parentheses).

6. "Object variable (or With block variable) not Set": you forgot to use the 'SET' keyword in setting a reference (LET for setting literal values).

Regular Expression in VBA (RegEx)

Ref: how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Searching Pattern & Writing in Debug-Immediate Window:

Following sub finds 'Aditya' in the entire word document:

Private Sub HighlightRegExMatch_Click()

Set re = New RegExp

re.Pattern = "\bAditya\b"

re.Global = True

re.IgnoreCase = True

Dim myWord As MatchCollection

Set myWord = re.Execute(ActiveDocument.Range)

For Each x In myWord

MsgBox x

End Sub

Searching Pattern & Writing in Debug-Immediate Window:

Private Sub ObjInDebugWin_Click()

'Print data to Debug window.

'Debug.Print (RegExpTest("\b\w*\sobject[s]?", ActiveDocument.Range.Text))

Debug.Print (RegExpTest("is.", "IS1 is2 IS3 is4"))

End Sub

'Call this function passing 2 args e.g.:

'MsgBox (RegExpTest("is.", "IS1 is2 IS3 is4"))

''''''''''''''''''''''''''''012345678901234

Function RegExpTest(patrn, strng)

Dim regEx, Match, Matches, s

' Create the regular expression.

Set regEx = New RegExp

regEx.Pattern = patrn

regEx.IgnoreCase = True

regEx.Global = True

' Do the search.

Set Matches = regEx.Execute(strng)

' Iterate through the Matches collection.

s = ""

For Each Match In Matches

's = s & "Match found from " & Match.FirstIndex & " to " & Match.FirstIndex + (Match.Length - 1)

's = s & " & Match Value is: " & Match.Value & vbCrLf & "."

s = s & Match.Value & vbCrLf

RegExpTest = s

End Function

Excel VBA Keywords / Short-Commands List:

Nothing Same as NULL, used to initialise a variable. E.g.: Set rDelete = Nothing

Calling Worksheet Functions In VBA

Nearly all worksheet functions can be called from VBA using the Application or Application.Worksheet class's objects

The difference between using or omitting the WorksheetFunctions reference is how errors are handled.

=VLOOKUP(123,A1:C100,3,FALSE)

Following code in VBA that does the same thing:

Dim Res As Variant

Res = WorksheetFunction.VLookup(123,Range("A1:C100"),3,FALSE)

See: http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx

Excel VBA EdExamples

Counting a word in a column:

Sub CountBonfire()

Dim lRow As Long 'last row (of table)

Dim str1, str2 As String 'strings to search

Dim cnt As Integer: cnt = 0 'count number of findings

lRow = ActiveSheet.Range("GP" & ActiveSheet.Rows.Count).End(xlUp).Row 'MsgBox lRow

str1 = "BONFIRE"

str2 = UCase("Controlled burning")

'str3 = UCase("")

For Each cell In Range("AM1:AM" & lRow)

'Set wcf = Range("d1").Find(cell.Value)

'Above is ok if exact value matches in range - Not for <= or >= comparisons.

If (InStr(UCase(cell.Value), str1)) <> 0 Or InStr(UCase(cell.Value), str2) <> 0 Then

'MsgBox cell.Row '_ Exit For

'MsgBox ActiveSheet.Range("GN" & cell.Row).Value

'If (InStr(UCase(ActiveSheet.Range("GN" & cell.Row).Value), UCase("overborder"))) = 0 Then

'Exit For

cnt = cnt + 1

'End If

End If

Next cell

MsgBox cnt

End Sub

Changing cell's background colour-shade to highlighting certain data (used in Data Analysis):

Note: this can also be done using: Conditional Formatting -> Data Bars / Icon Sets OR Colour Scales.

Colouring a range - using Interior.Color property:

Range("A1").Interior.Color = RGB(255, 0, 0) 'using RGB function

Range("A1").Interior.ColorIndex = 37 'using ColorIndex

Setting background color to 'No Fill':

Range("A1").Interior.ColorIndex = 0

Displaying ColorIndex

MsgBox Selection.Interior.ColorIndex

Full code:

Sub HighlihgtCellsAsPerValue()

Dim maxval As Integer, rng As Range, cell As Range

Set rng = Range("e4:g16") 'SET is mandatory as we are assigning a reference (not literal value/LET).

For Each cell In rng

cell.Value = WorksheetFunction.RandBetween(100, 999) 'filling cells with random values for test.

maxval = WorksheetFunction.Max(rng)

'Debug.Print ((9 / 255) * maxval)

For Each cell In rng

'cell.Interior.Color = RGB(255, 255 - (cell.Value * 255 / maxval), 255 - (cell.Value * 255 / maxval))

'above makes colours too dark, even though logically more correct.

cell.Interior.Color = RGB(255, 255 - (cell.Value * 80 / maxval), 255 - (cell.Value * 80 / maxval))

End Sub

Searching & highlighting matching values from two ranges (using Array from a Range):

(Requires: two ranges in column B, file: ArrayFromRange_RowHide.xlsm)

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))

End Function

Sub FindMatchingValsFromOtherRange()

Dim rng, cell As Range: Set rng = Range("b1:b39")

Dim arr() As Variant 'must be of Variant type as Range() & Split() return array of Variants.

'arr() = Split("Essex, Berkshire, Dorset, Durham", ", ") 'manual array - without using range.

arr = Range("B44:B56") 'Note: Range() always returns 2-D array (even if data is in single row/col).

'MsgBox arr(0, 0) 'Error: Subscript out of range as index in VBA starts with 1 (not 0 Ggrrr).

'Application.SendKeys "^g ^g ^a {DEL} {F7}" 'Clearing Immediate/Debug-output window.

Debug.Print Now

'Debug.Print Join(arr, ", ")

For Each cell In rng

If (IsInArray(cell.Value, arr)) Then

cell.Interior.Color = vbRed 'Highlight cell.

'cell.EntireRow.Hidden = True 'Hide EntireRow.

End If

Next cell

End Sub

Word-VBA (MS Word specific VBA)

Universal VBA Code Containers / Templates:

Word documents in the .docx format and Word templates in the .dotx format cannot contain VBA code. If you want to put VBA code in a document or template, you must use either the Word Macro-Enabled Document (.docm) format or the Word 97-2003 Document (.doc) format for a document or the Word Macro-Enabled Template (.dotm) format or the Word 97-2003 Template (.dot) format for a template. The Normal template is a Word Macro-Enabled Template (Normal. dotm).

Adding Macros in Word Normal.dot / default-template (to make them available in all office files):

Post-2007-2013:-

 View (ribbon) -> Macros -> Show/View Macros -> (select one macro from list then click) Edit / Create. (Alternatively go to "Developers" (Tab) -> Visual Basic) : this will open the "VB for Applications" window.

 (on the LeftNavBar) Normal -> Modules > right click "Insert > Module" (or select existing module e.g. Module1 -> NewMacros) -> Write or Copy-Paste "Subs" -> Save file and that's it.

Note: Macros can also be customized in: "Developers Tab" -> "Macros".

The Selection object:

Selection object represents the current selection, it can be: an insertion point (with no content), characters, words, paragraph, or another object (for example, a picture).

Functions and Properties of 'Selection' object:

- Selection.TypeParagraph : adds a blank paragraph.

- "HomeKey"/"EndKey" : jumps to the home/end position of (units): "wdStory", "wdParagraph" or "wdLine" etc. (spec # of "counts").

- "TypeText" : Types the passed string.

- "MoveUp"/"MoveDown"/"MoveLeft"/"MoveRight"/ : Moves the cursor towards specified position for specified number of "counts" (of: "wdStory", "wdParagraph" or "wdLine" etc.)

- "StartOf"/"EndOf" : Puts the cursor at specified place, e.g.: Selection.StartOf(wdParagraph).

- ActiveDocument.Paragraphs.count : Returns total number of paragraphs in the active document.

Commonly used Word VBA Snippets:

* Retrieval:

To pull/set data from/to your document, you must use the following part of the Word object:

- ActiveDocument.Paragraphs(#).Range.Sentences(#)

'here # is number.

'ActiveDocument is the parent object, through which nearly all objects can be accessed.

e.g. ActiveDocument.Paragraphs(3).Range.Sentences(3)

* Selection:

• Entire Document:

ActiveDocument.Range

• Paragraph:

ActiveDocument.Paragraphs(1).Range.Select : This will select 1st paragraph in Active document.

• Current Selected Text

Selection.Range

 'Range' is important to manipulate current selected text.

• Selection.Range v/s Selection.Text:

 Selection.Range lets you edit the current selection, whereas Selection.Text is a read only property.

* Variable Assignment :

Dim oRng As Range

Set oRng = Selection.Range

* Copy Text (to clipboard):

- Using: Selection.Copy() command along with: SelStart, SelLength & SetFocus.

* Paste Text:

- Selection.PasteSpecial DataType:=wdPasteText : This will paste unformatted text from clipboard at current cursor position, if something is selected/highlighted it will replace/delete it.

Copy - Paste Example using DataObject:

e.g.: Sub CopyToTheClipboard()

Dim MyDataObj As New DataObject

MyDataObj.SetText "This Is A Text String" 'setting string

'MyDataObj.SetText 123.456 'setting numbers

MyDataObj.PutInClipboard

End Sub

Sub PastingFromTheClipboard()

Dim MyDataObj As New DataObject

MyDataObj.GetFromClipboard

Dim MyVar As Variant

'MyVar = (MyDataObj.GetFromClipboard).GetText '1 liner.

MyVar = MyDataObj.GetText

MsgBox MyVar

End Sub

* Addition / Inserting Text:

- ActiveDocument.Paragraphs(3).Range.InsertBefore "This sentence was inserted. "

* Deletion:

- ActiveDocument.Paragraphs(3).Range.Sentences(1).Delete

* Modification:

- Let's loop through all the paragraphs, replacing the first character of each sentence with a lower case version of itself:

For x = 1 To ActiveDocument.Paragraphs.Count

For y = 1 To ActiveDocument.Paragraphs(x).Range.Sentences.Count

firstchar = ActiveDocument.Paragraphs(x).Range.Sentences(y).Characters.First

firstchar = StrConv(firstchar, vbLowerCase) 'convert to lower case

ActiveDocument.Paragraphs(x).Range.Sentences(y).Characters.First = firstchar

Next y

Next x

* Change Directory:

ChangeFileOpenDirectory "C:\Users\Eddie\"

* Create new blank word document:

Documents.Add DocumentType:=wdNewBlankDocument

Documents.Add

* Open file:

Documents.Open command

* Close file:

ActiveWindow.Close

* Copy text:

Selection.Copy

* Font Styles Alteration:

Dim oRng As Range

Set oRng = Selection.Range

oRng.Font.Bold = True

oRng.Font.Color = wdColorRed

or use the macro to apply your style

Dim oRng As Range

Set oRng = Selection.Range

oRng.Style = "Character Style Name"

Word VBA EdExamples

Extra VBA Example:

Removing hyperlinks in Word with VBA

VBA Code 1: Remove all hyperlinks of current document

Sub RemoveHyperlinks()

With ThisDocument

'Loop while there are hyperlinks on document

While .Hyperlinks.Count > 0

.Hyperlinks(1).Delete

Wend

End With

'Turn off auto-hyperlinking:

Application.Options.AutoFormatAsYouTypeReplaceHyperlinks = False

End Sub

VAB code 2: Remove all hyperlinks in all open documents

Sub RemoveHyperlinksInAllOpenDocuments()

' Removes all hyperlinks from any open documents

Dim doc As Document

Dim szOpenDocName As String

'Loop through all open documents:

For Each doc In Application.Documents

'Store the document name

szOpenDocName = doc.Name

'Remove the hyperlinks from that document

With Documents(szOpenDocName)

'Loop while there are hyperlinks afoot!

While .Hyperlinks.Count > 0

.Hyperlinks(1).Delete

Wend

End With

'Turn off auto-hyperlinking:

Application.Options.AutoFormatAsYouTypeReplaceHyperlinks = False

Next doc

End Sub

File Handling (Word-VBA)

Private Sub WriteToFile_Click()

'Open file in file-system: (creates new file if doesn't exist)

Open "C:\Temp\HoldPastes.txt" For Append As #1

'Read Clipboard content:

Dim myData As DataObject

Dim strClip As String

Set myData = New DataObject

myData.GetFromClipboard

strClip = myData.GetText

'Paste Clipboard content in the same file:

Selection.EndKey Unit:=wdStory, Extend:=wdMove

Selection.PasteSpecial DataType:=wdPasteText

'Write to file:

Write #1, "------------------------" & vbCrLf & vbCrLf & vbCrLf

Write #1, strClip

'Close file:

Close #1

End Sub

Private Sub ReadWriteFile_Click()

Dim sFirst, sLast, sAddress, sCity, sState, sZip As String

' CREATE DATA FILE

' Open file in current directory:

Open "Datafile.txt" For Output As #1

' Write data to file.

Write #1, "John", "Doe", "An Address", "A City", "A State", "A Zip"

' Close File.

Close #1

' RETRIEVE DATA FROM FILE

' Open file for input.

Open "Datafile.txt" For Input As #1

' Loop until the end of file is reached.

Do While Not EOF(1)

' Read data into variables.

Input #1, sFirst, sLast, sAddress, sCity, sState, sZip

' Print data to Debug window.

Debug.Print sFirst, sLast, sAddress, sCity, sState, sZip

Loop

' Close file.

Close #1

End Sub

Replacing Dodgy Characters

Sub ConvertIntoHtmlParas()

'This macro first deletes all blank lines then encloses all the paragraphs in a document with (except paragraphs enclosed within <li></li>),

'Secondly this also replaces all special (dodgy) characters with plain characters.

Dim pCount As Integer

Dim paraCount As Integer

pCount = ActiveDocument.Paragraphs.Count

Selection.HomeKey Unit:=wdStory

For x = 1 To (pCount + 1)

Selection.MoveEnd Unit:=wdParagraph, Count:=1

If Len(Trim(Replace(Selection.Text, vbCr, ""))) < 1 Then ' Note: in MS-Word a new-line character is vbCr (NOT vbCrLf).

Selection.Delete

End If

Selection.EndOf (wdParagraph)

Selection.EndKey Unit:=wdStory

Selection.TypeText (vbCrLf)

Selection.HomeKey Unit:=wdStory

paraCount = ActiveDocument.Paragraphs.Count

For x = 2 To paraCount 'Starting from 2 (instead of 1) to skip last blank paragraph that was manually entered for consistency.

Selection.StartOf (wdParagraph)

Selection.MoveEnd Unit:=wdParagraph, Count:=1

If (InStr(1, Trim(Selection.Text), "<li>", vbTextCompare) = 0) Then

Selection.StartOf (wdParagraph)

Selection.TypeText ("")

Selection.EndOf (wdParagraph)

Selection.MoveLeft Unit:=wdCharacter, Count:=1

Selection.TypeText ("")

Selection.MoveDown Unit:=wdLine, Count:=1

Else

Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdMove

End If

'Following section replaces dodgy characters: (¦`’‘“”– " ")

Selection.Find.Replacement.ClearFormatting

With Selection.Find

.Text = "¦"

.Replacement.Text = "|"

.Wrap = wdFindContinue