Saturday, October 11, 2008

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:

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
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group