How to select a Named Range in a Workbook using VBA

The following snippet will select the specified named range

Function Goto_A_Name(ByVal sName As String) As Boolean

'

On Error GoTo Err_Going

Application.GoTo Reference:=sName

Goto_A_Name = True

Exit Function

Err_Going:

End Function

Restrict Multiple Instance of Visual Basic Application

How to Prevent Visual Basic Exe being executed multiple times

At times we set/reset registry settings / environment variables as part of the program logic. If another instance of the application uses this concurrently it would become a mess. The best is to prevent the application from being loaded for the second time. The easy way to do is to use App.PrevInstance method as shown below.

If App.PrevInstance = True Then

MsgBox "An instance of this tool is running in this machine! Requested instance will terminated", vbExclamation

Exit Sub

End If

Only comments may appear after End Sub, End Function, or End Property

Only comments may appear after End Sub, End Function, or End Property

One possibility of this error might be because of the Declare statement, which might NOT be at the beginning of the module / class

Place the declare statement at the beginning of the module

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules

Declaring the following in a Userform is one possible cause for the error:

Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _

(ByVal lpclassname As Any, ByVal lpCaption As Any) As Long

DLL procedures declared in standard modules are public by default and can
be called from anywhere in your application. DLL procedures declared in any
other type of module are private to that module, and you must identify them
as such by preceding the declaration with the Private keyword. Hence

Private Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _

(ByVal lpclassname As Any, ByVal lpCaption As Any) As Long

Should solve the problem

How to Windows Explorer using VBA

How to Open a Folder in Windows Explorer using VBA

ShellExecute() Windows API function can be called from a VBA macro to start another program under Microsoft Windows. Use ShellExecute() instead of Shell (a Visual Basic statement) or WinExec() (a Windows API function) to work around the following limitation of the latter commands.

With Shell and WinExec(), you cannot start an application by specifying a file name only. For example, the following Shell statement will fail:

Shell (“c:\temp”)

Declare the API function

Declare Function ShellExecute Lib "shell32.dll" Alias _

"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _

As String, ByVal lpFile As String, ByVal lpParameters _

As String, ByVal lpDirectory As String, ByVal nShowCmd _

As Long) As Long

The following code will open the specified folder in Windows Explorer

Sub Open_ExplorerWindow()

ShellExecute 0, "open", "c:\temp", 0, 0, 1

End Sub

Suppress "Opening this will run the following SQL command" message using Registry – Word Mail Merge

How to suppress "Opening this will run the following SQL command" message using Registry – Word Mail Merge

Here is a method to do the same

Word 2007

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options

3. On the Edit menu, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Back to the top

Word 2003

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options

3. Click Edit, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Back to the top

Word 2002 Service Pack 3

To do this, follow these steps:

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Word\Options

3. Click Edit, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Word Message

Type RegEdit in Run Window Select the appropriate key for your Word version

Create a new DWORD value

How to Show Field Codes in Word Document – using VBA

Word VBA – Show Field Codes

Here is a simple way to show the field codes in Word

Sub Show_Field_Codes()

Application.ActiveWindow.View.ShowFieldCodes = True

End Sub

How to Hide Field codes in a Word Document using VBA

Word VBA – Hide Field Codes

The following snippet will hide the field codes present in Word document.

Sub Hide_Field_Codes()

Application.ActiveWindow.View.ShowFieldCodes = False

End Sub

How to Insert Document Properties in Word Document

Update Document Properties in a Word document using Field codes

Field codes are the best tool for any Word users. They are rich source of predefined information that can be inserted in the document.

Word document contains many important properties, which can be accessed from the Properties dialog box.

To view the Properties dialog box, click the Microsoft Office Button , point to Prepare, and click Properties, click Document Properties, and then click Advanced Properties.

The following windows shows the properties

Word Document Properties

Word Advanced Properties

To insert a field select the QuickParts option from Insert tab and then DocProperty in the option
To select a property, click the property name in the Property box in the Field dialog box.

Word Quick Parts Dialog

To view the Field dialog box, on the Insert tab, in the Text group, click Quick Parts, and then click Field.


Insert DocProperty in Word Document

How to insert the DocumentProperties field using Word VBA

The same can be done through VBA as follows

Sub Insert_PropertyData_InDocument()

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"DOCPROPERTY CreateTime ", PreserveFormatting:=True

End Sub

Word DocProperty Field

How to delete Folder using VBA

VBA RmDir Method

Here is a simple method to delete the entire folder using VBA

Sub Delete_Folders_FS()

Dim sFolder As String

On Error GoTo Err_Msg

sFolder = "c:\temp\2Bdeleted\"

RmDir (sFolder)

Err_Msg:

If Err.Number <> 0 Then

MsgBox Err.Description

Err.Clear

End If

End Sub

How to Convert Decimal to Hexadecimal using VBA

How to Convert Decimal to Octal using VBA

Sub Get_Hex_n_OCt_Values()

For i = 0 To 255

Debug.Print i & vbTab & Hex(i) & vbTab & Oct(i)

Next i

End Sub

VBA Decimal to Hexadecimal Conversion

Enable AutoRecovery using Word VBA / Set Auto Recovery Duration using VBA

The following code will help to set the AutoRecovery On using VBA. The frequency of Autorecovery Save can also be set / reset

Sub EnableAutoRecovery()

Options.SaveInterval = 5

End Sub

Word Enable Auto Recovery

How to Disable AutoRecovery using Word VBA

Word Auto Recovery will be a nemesis if you are working with large documents. It can be disabled by using the following code

Sub DisableAutoRecovery()

Options.SaveInterval = 0

End Sub

Word Disable Auto Recovery

Expand Ranges in Word using VBA

Word VBA Expand Range function

Many times ranges like 1-4 needs to be expanded. Here is a simple function to do the same.

Function ExpandRanges(ByVal sNoRange As String) As String

Dim arTemp

Dim iLowVal As Integer

Dim iUpVal As Integer

Dim i1 As Integer

Dim sRet As String

If InStr(1, sNoRange, "-") = 0 Then MsgBox "Given No not a Range ": Exit Function

arTemp = Split(sNoRange, "-")

iLowVal = Val(arTemp(0))

iUpVal = Val(arTemp(1))

For i1 = iLowVal To iUpVal

sRet = sRet & "," & i1

Next i1

sRet = Right(sRet, Len(sRet) - 1)

ExpandRanges = sRet

End Function

Here is a sample output

Expand Ranges in Word

How to Undo the last action using Excel VBA

How to Validate Keypress in Excel VBA

A combination of the Undo method and the Worksheet events can be used to validate Text input.

It can be used with Worksheet events effectively. For example, the following code will allow the user to enter only numerals in A2. If any non-numeric character is entered, the code reverts the contents of the range to the last stored value.

Sub Undo_Invalid_Entry()

If Target.Address = "$A$2" Then

If IsNumeric(Target) = False Then

Application.EnableEvents = False

Application.Undo

End If

End If

Application.EnableEvents = True

End Sub

Try using this event from Worksheet_Change event. The procedure will resist any entry other than numerals in "A2 and will restore the original text if found otherwise

Note: Application.Undo method undoes only the last action taken by the user before running the macro. This line must be the first line in the macro.

It cannot be used to undo Visual Basic commands.