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