How to disable user from using short-cut keys using VBA

You can disable the keybinding using the following code

Sub VBA_DisableKey()

'This code will disables Ctrl + O FileOpen command.
' Disable Ctrl + O

FindKey(BuildKeyCode(wdKeyControl, wdKeyO)).Disable

' Rebind Ctrl + O to FileOpen

FindKey(BuildKeyCode(wdKeyControl, wdKeyO)).Rebind wdKeyCategoryCommand, "FileOpen"

End Sub

Use the rebind method to restore the bind.

How to get the free space available using VBA

Sub FreeDiskSpaceCurrent_Drive()

Dim sFreeSpace As String

sFreeSpace = System.FreeDiskSpace

sFreeSpace = Format(sFreeSpace, "0,000")

MsgBox "Free Space Available is : " & sFreeSpace

End Sub

Convert Decimal to Percentage using VBA Format Function

Here is a simple example to convert a decimal to percentage using VBA function

Sub Con_Decimal2Percentage()


Dim dblSuccess As Double
Dim sSuccess As String

dblSuccess = 0.54

sSuccess = FormatPercent(dblSuccess, 2)

sSuccess = Format(Expression:=dblSuccess, Format:="Percent")


End Sub

How to Show File Print Setup Dialog Box using Word VBA

The following code displays the Print Setup Dialog

Sub Show_PntSetup()

With Dialogs(wdDialogFilePrintSetup)
.Show
End With

End Sub.

Convert Decimal to Percentage using VBA FormatPercent Function

Sub Convert_Decimal2Percentage()

Dim dblSuccess As Double
Dim sSuccess As String

dblSuccess = 0.456345

sSuccess = FormatPercent(dblSuccess, 2)

sSuccess = Format(Expression:=dblSuccess, Format:="Percent")

End Sub

VBA check program modes.

How to check if the program is in debug mode using VBA. Below code is used to check :-

Sub Check_VBA_VBE()

Dim oVBe As VBProject

Set oVBe = ThisWorkbook.VBProject

If oVBe.Mode = vbext_vm_Run Then

MsgBox "Executing.."

MsgBox oVBe.VBE.MainWindow.Visible

End If

End Sub

The code uses VBProject. Ensure that the Microsoft Visual Basic for Applications reference is loaded.

How to Add Macro to Workbook using Excel VBA

Here is a simple procedure to add a macro to the current workbook using VBComponents

Sub Add_Macro_To_ThisWorkbook()

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent

Set VBP = ThisWorkbook.VBProject

Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule

VBModule.AddFromString ("Sub Sample_Macro" & vbCrLf & "ret = msgbox (""Hello VBADUD"") " & vbCrLf & "End Sub")

End Sub

ADO Connection

If you are try to connect to ADO. Here is the connection string for Excel

sXL = "c:\CtArtExample.xls"

Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & sXL & ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40

Cn.Open

The rest is the usual ADO recordset retrieving technique.

Dir Function in VBA (Visual Basic)

Dir or Dir$ Function can be used in Microsoft Visual Basic (Applications) to get the list of directories or files

sDir = Dir$(sPath & "*.Doc", vbNormal)
Do Until LenB(sDir) = 0
Set oWB = Documents.Open(sPath & sDir)
‘ Do some stuff
oWB.close
sDir = Dir$
Loop

The above will open all Word Documents under a particular directory.
Cheers,
Chithu

Shared Name For the Drive

QueryDosDeviceW API Function can be used to get the device name.

To get the Shared Name of the drive, use the following function:-

Public Function ConvertDrive2ServerName(ByVal sFullPath As String) As String

' --- Replaces the DriveName with ShareName in a given string

Dim FSO As FileSystemObject
Dim sDrive As String
Dim drvName As Drive
Dim sShare As String
On Error GoTo Err_TrapSet
FSO = New FileSystemObject
sDrive = FSO.GetDriveName(sFullPath)
Set drvName = FSO.GetDrive(sDrive)
sShare = drvName.ShareName
If LenB(sShare) <> 0 Then
ConvertDrive2ServerName = Replace(sFullPath, sDrive, sShare, 1, 1, vbTextCompare)
Else
ConvertDrive2ServerName = sFullPath
End If
If Not FSO Is Nothing Then Set FSO = Nothing
' ---------------------------------------
' Error Handling'
'----------------------------------------
Err_Trap:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Function

The function returns the DriveName with ShareName in a given string. It is advisable for programmers to store all the file locations using Sharename instead of DriveName.

Cheers,
Chithu