Programmatically add macro to Excel Workbook using VBA

Insert Code Module to Workbook using Excel VBA

There are numerous instances where code/macro needs to be added to the workbook on the fly. The following code snippet adds the code from the module to the specified workbook

Function Insert_Button(ByRef oWB As Workbook)

Dim oVBP As VBProject ' VB Project Object

Dim oVBC As VBComponent ' VB Component Object

On Error GoTo Err_VBP

Set oVBP = oWB.VBProject

Set oVBC = oVBP.VBComponents.Add(vbext_ct_StdModule)

oVBC.CodeModule.AddFromFile "c:\VBADUD\Templates\MSample.bas"

oWB.Application.Run "'" & oWB.name & "'!SayHello"

oWB.Save

' -------------------

' Destroy Objects

' -------------------

Finally:

If Not oVBP Is Nothing Then Set oVBP = Nothing

' -------------------

' Error Clearer

' -------------------

Err_VBP:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Function

After adding the macro to the Excel workbook programmatically, the code fires the Macro – “SayHello” from the added bas module

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

Excel VBA CodeModule, Excel VBA AddFromFile, Excel VBA add Code Modules, Excel VBA VBComponents

How to add macro to workbook using VBA, Programmatically add macros to workbook, Add code from BAS module to workbook using VBA, Excel VBA add code to workbook,

Microsoft Visual Basic for Applications Extensibility reference Dialog

See also:

Add Macro to Workbook programatically using Excel VBA

Extract Procedure Names from all Modules - VBA

Insert Procedure to a Module Using VBComponents

Delete Module on the Fly using VBA

Insert User Form on the Fly

Insert Class Module on the Fly

VBA check program modes

How to check if the program is in debug mode using VBA

Here is a simple way to check if the program is in debug mode using VBA

Sub Check_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 Extensibility reference is loaded

Microsoft Visual Basic for Applications Extensibility reference

Excel VBA TimeStamp – Milliseconds using Excel VBA

How to Get Time in Milliseconds using Excel VBA

The following function uses Timer function to get the milliseconds and append it to the current time

Public Function TimeInMS() As String

TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)

End Function

Timer function returns a Single representing the number of seconds elapsed since midnight.

Another method is to use API Functions as shown below

Private Type SYSTEMTIME

wYear As Integer

wMonth As Integer

wDayOfWeek As Integer

wDay As Integer

wHour As Integer

wMinute As Integer

wSecond As Integer

wMilliseconds As Integer

End Type

Private Declare Sub GetSystemTime Lib "kernel32" _

(lpSystemTime As SYSTEMTIME)

Public Function TimeToMillisecond() As String

Dim tSystem As SYSTEMTIME

Dim sRet

On Error Resume Next

GetSystemTime tSystem

sRet = Hour(Now) & ":" & Minute(Now) & ":" & Second(Now) & _

":" & tSystem.wMilliseconds

TimeToMillisecond = sRet

End Function

Millisecond timer using VBA, How to get milliseconds in VBA Now() function, VBA Now() function, VBA Timer function , Excel VBA Timer, VBA Milliseconds

See also:

Sleep Function in Excel VBA

How to get number of Weekdays/Weekends in a given month using (C#/VB.NET)

Calculating Time taken for an operation using VB.NET (in Milliseconds)

Calculate Working days (Excluding Holdiays) using Excel Function / VBA

Automatically Event Repeat in Excel VBA (OnTime Method)

Compare Files by Date

Display Time Dynamically in Form using Time/Now Function

Excel VBA uninstall Excel Addins

Programmatically uninstall Excel Addins using VBA

Sub UnInstall_Addins_From_EXcel_AddinsList()

Dim oXLAddin As AddIn

For Each oXLAddin In Application.AddIns

Debug.Print oXLAddin.FullName

If oXLAddin.Installed = True Then

oXLAddin.Installed = False

End If

Next oXLAddin

End Sub

See also:

Call a Method in VSTO Addin from Visual Basic Applications

Programmatically Delete Word Addins from the Addins List using VBA

UnInstall Word Addins using VBA

Programmatically Add Modules/Addins to Word using VBA

How to use .Net Array.Sort Function in VBA

Word VBA add command buttons through code

Add CommandButton to Word Document using VBA (through AddOLEControl)

Here is one of the ways to add a command button on a Word document using Word VBA

Sub Macro_Add_Button()

Dim oCtl

Dim oCmd

Set oCtl = ActiveDocument.InlineShapes.AddOLEControl(ClassType:="Forms.CommandButton.1")

Set oCmd = oCtl.OLEFormat.Object

oCmd.Caption = "Click Me..."

End Sub

Manipulate ActiveX TextBoxes in a Word Document using VBA

Change Content of Embedded Textboxes in Word using VBA

Word Document might contain text boxes embedded in it as Inlineshapes. In that case, it can be manipulated using VBA as follows:

Sub Document_TextBoxes()

Dim oCtl As InlineShape

Dim oTB

For Each oCtl In ActiveDocument.InlineShapes

If oCtl.OLEFormat.ProgID = "Forms.TextBox.1" Then

Set oTB = oCtl.OLEFormat.Object

oTB.Text = "Sample Text"

End If

Next

End Sub

Embedded Text Box using Word VBA

Automatically Tag Underlined Words using Word VBA

Tag Formatted Text in Word using VBA

Here is a Word VBA snippet for searching text in a particular format, for example, underlined text and Tag them

Sub Tag_Under_Line()

Selection.ClearFormatting

Selection.HomeKey wdStory, wdMove

Selection.Find.Font.Underline = wdUnderlineSingle

Selection.Find.Execute ""

Do Until Selection.Find.Found = False

Selection.Font.Underline = wdUnderlineNone

Selection.InsertBefore "<>"

Selection.InsertAfter "< /UL >"

Selection.MoveRight

Selection.Find.Execute ""

Loop

End Sub

Word Document with Formatting (Underline)

(Tagged Word Document after Macro Execution)

Programmatically Delete Word Addins from the Addins List using VBA

Delete Word Addins using Word VBA

At times there will be quite a no of Addins that you would have tested in Word and they will line-up in the Addins List or you would have added no of versions of the same addin and it still is showing on the list

The following code will delete all uninstalled word addins from the Addins List

Private Sub Delete_UnInstalled_WordAddins()

Dim oAddin As AddIn

On Error GoTo Err_Addin

For Each oAddin In AddIns

If oAddin.Installed = False Then

oAddin.Delete

End If

Next oAddin

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Uninstalled Word Addin

Word Addins Removed using VBA

UnInstall Word Addins using VBA

Here is a simple method to uninstall a Word Addin (.dot file) using Word VBA

Private Sub UnInstalled_AllWordAddins()

Dim oAddin As AddIn

On Error GoTo Err_Addin

For Each oAddin In AddIns

If oAddin.Installed Then

msg = oAddin.Name

oAddin.Installed = False

End If

Next oAddin

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Installed Word Addin

Word Addin List after Macro Execution. Addin is uninstalled (not removed)

Programmatically Add Modules/Addins to Word using VBA

Install Word Addins using VBA

Here is a simple method to add a Word Addin (.dot file) using Word VBA

Private Sub Add_Word_Addin()

Dim oAddin As AddIn

On Error GoTo Err_Addin

' Add the Word Addin and Install It

Set oAddin = Application.AddIns.Add("c:\ShasurData\vbadud_Business_Template1.0.dot", True)

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Word Addins Dialog Before Macro Execution

Word Addins Dialog After Macro Execution

Add Macro to Workbook programatically using Excel VBA

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

Add Controls Popup Menu using Powerpoint VBA

Powerpoint Application - Create Popup menu using VBA

The following code is a simple one to create popup menu in Powerpoint using VBA

Sub Add_Ctrl_To_Popup(ByVal sControlName As String, ByVal sMacroName As String)

On Error GoTo DisplayErr

' ---------------------------------------------------------------'
Written By Shasur for http://vbadud.blogspot.com
'---------------------------------------------------------------
Dim ctlCB As CommandBarDim ctlCommand As CommandBarControl

Set ctlCB = Application.CommandBars("Shapes")

If ctlCB Is Nothing Then Exit Sub

Set ctlCommand = ctlCB.Controls.Add

ctlCommand.Caption = sControlName

ctlCommand.OnAction = sMacroName

DisplayErr:

If Err <> 0 Then

MsgBox Err.Description

Err.Clear
Resume Next

End If
End Sub

Following sub will call the function

Sub Drive_Add_Ctrl_To_Poppup()
Add_Ctrl_To_Poppup "NewMenu", "Test"
End Sub

The above function will add an item to popup menu using VBA and assign a macro to it

See also:

Creating Menu’s and CommandBars in Powerpoint using VBA

Disable Cut & Copy from Popup menu (Excel VBA/Word VBA)

enable popup menu

Disable Right Click using VBA

Add Control To PopupMenu

Opening Comma Separate File (CSV) through ADO

Sort CSV File through Excel VBA ADO/ Open CSV File using ADO

CSV files are easy way of storing data apart from XML files. There are many times we need the data from CSV file to be extracted in a specific format / specific data alone to be extracted. In such cases we can use ADO to extract restricted data using SQL Query

The main changes in connection string from using Access to using CSV would be the following :

Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)""

Source : Folder where the file exists

Extended Properties: Text (denoting Text File), HDR =Yes (If Header is present in the Text File),

FMT=Delimited(,) (denoting comma delimiter)

The following would be the SQL Query

sQuery = "Select * From VBA.csv ORDER BY ID"

We have only specified the folder name in the connection string and here we specify the file name (VBA.csv)

The following code needs reference to Microsoft ActiveX Data Objects library

Sub Open_Sort_CSV()

Dim cN As ADODB.Connection '* Connection String

Dim RS As ADODB.Recordset '* Record Set

Dim sQuery As String '* Query String

On Error GoTo ADO_ERROR

' -----------------------------------------

' Coded by Shasur for vbadud.blogspot.com

' -----------------------------------------

cN = New ADODB.Connection

cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

cN.ConnectionTimeout = 40

cN.Open()

RS = New ADODB.Recordset

sQuery = "Select * From VBA.csv ORDER BY ID"

RS.ActiveConnection = cN

RS.Source = sQuery

RS.Open()

If RS.EOF <> True Then

While RS.EOF = False

Open "c:\temp\vba_sorted.csv" For Append As 1

Print #1, RS.Fields(0) & "," & RS.Fields(1)

RS.MoveNext()

Close #1

End While

End If

If RS.State <> adStateClosed Then

RS.Close()

End If

If Not RS Is Nothing Then RS = Nothing

If Not cN Is Nothing Then cN = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert(Err = 0)

MsgBox(Err.Description)

Resume Next

End If

End Sub

Sorting Text files (comma separated) is not that easy. One option is to open the file in Excel, sort the columns and save it back (Refer ). However, this cannot be done if the rows are more than that Excel can accomodate in that case you can use ADO to sort the file and write the file back to CSV

Common Errors

You will get the following error if the specified file is not available “The Microsoft Jet database engine could not find the object 'VBA.csv'. Make sure the object exists and that you spell its name and the path name correctly.”

ADO Error :- Query must have at least one destination field

The above error occurs when there are no records in the Text file

ADO Error :- Operation is not allowed when the object is closed.

The above error occurs when some operations are done on closed recordset etc











ADO Connection string for Text File, ADO Connection string for CSV File, Excel VBA ADO for Text Files, ADO Text File Connection String, Connection String CSV File ADO, Open Text File using ADO, Sort Text File using ADO, Open and Sort Text Files using ADO,

See also:

C# CSV to XML Conversion, CSV to XML Conversion using LINQ

Insert CSV File to Array using C#

Convert Excel Comments to Text using VBA

VBA Copy comments to a Range in Excel

If a worksheet is dotted with lot of comments and you want to respond to each one of these, it is better to extract the comments and place it in a column. This would help in responding to the comments. The following code will extract all comments and place it in column ‘F’ along with the value of the original cell

Sub Convert_Comment_To_Text()

Dim oCom As Comment

Dim sVal As String

Dim i As Integer

For i = 1 To ActiveSheet.Comments.Count

sVal = ""

Set oCom = ActiveSheet.Comments(i)

sVal = ActiveSheet.Cells(oCom.Parent.Row, oCom.Parent.Column).Value

ActiveSheet.Range("F" & CStr(oCom.Parent.Row)).Value = "'" & sVal & " -- " & oCom.Text

Next

End Sub

Before Running the Macro
Comments placed in Column F

See also:

Delete Comments from Excel Workbook using VBA

Visual Basic Script to Lock Computer

Lock Machine (Computer) using VB Script

My good friend Venugopala Reddy Ragi had a good VBScript snippet to lock computer. Probably useful for someone to enhance security

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "rundll32 user32.dll,LockWorkStation"

Play Audio File using Excel VBA

Play Audio File using Excel VBA / Excel VBA Play Wav File

Here is a small snippet used by Pradeep Meesala to play a wav file. He has used this for an elegant splash screen, which was a nice combination of music and display

The code uses PlaySound API Function

Private Declare Function PlaySound Lib "winmm.dll" _

Alias "PlaySoundA" (ByVal lpszName As String, _

ByVal hModule As Long, ByVal dwFlags As Long) As Long

Sub Play_Wav_File()

Dim sWAVFile As String

On Error GoTo Err_Play

sWAVFile = "C:\Temp\Windows XP Logon Sound.wav"

PlaySound(sWAVFile, &O0, 0)

Err_Play:

If Err <> 0 Then

Err.Clear()

End If

End Sub

Permanently hide worksheet from user using VBA

Hide / Unhide Worksheets using Excel VBA (Excel 2007)

Here is the simple code to Hide and unhide Worksheet:

Sub Hide_Unhide_Worksheets()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = Worksheets(1)

' hide the sheet - user cannot unhide

oWS.Visible = xlSheetVeryHidden

' To make the sheet visible

oWS.Visible = xlSheetVisible

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub

Here are the values for XLSheetVisibility. Hides the Worksheet so that the only way for you to make it visible again is by setting this property to True (the user cannot make the Worksheet visible).

Name		Description
xlSheetHidden		Hides the worksheet which the user can unhide via menu.
xlSheetVeryHidden		Hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible).
xlSheetVisible		Displays the sheet.