Tuesday, November 25, 2008

Call a Method in VSTO Addin from Visual Basic Applications

How to call a VSTO Addin Function from VBA

VSTO Addins we write might contain good number of reusable components. The following article shows how to use them from external applications. This consists of the following steps:

1. Creating a VSTO Addin with a public method

2. Expose the VSTO Method for external use

3. Consume the VSTO Method from VBA

Creation of VSTO Excel Addin

Create a new project from Visual Studio - - > Office Projects - - > Excel 2007 Addin and call the project CallVSTOExample

This will automatically create necessary references and add required directives


VSTO Addin



using System;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

Create a new C# Class module and add the following code to it

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

namespace CallVSTOExample

{

[ComVisible(true)]

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]

public interface IAddinVSTO

{

void SayHello();

}

[ComVisible(true)]

[ClassInterface(ClassInterfaceType.None)]

public class VSTOExample : IAddinVSTO

{

#region IAddinVSTO Members

public void SayHello()

{

Excel.Worksheet wks = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

Excel.Range myRange = wks.get_Range("A1", System.Type.Missing);

myRange.Value2 = "Hello";

}

#endregion

}

}

Here we have created one interface and have also created a class that implements the interface. The class has a simple method SayHello, which inserts “Hello” in cell A1 of active worksheet

The class must be public and should expose the IDispatch interface for it to be visible to COM

InterfaceIsIDispatch indicates an interface is exposed to COM as a dispinterface, which enables late binding only.

Exposing the method for external use

Add the following code to ThisAddin class

private VSTOExample utilities;

protected override object RequestComAddInAutomationService()

{

if (utilities == null)

utilities = new VSTOExample();

return utilities;

}

RequestComAddInAutomationService method returns an object in your add-in that can be used by other Microsoft Office solutions.

Override this method to expose an object in your add-in to other Microsoft Office solutions. This includes other add-ins and document-level customizations running in the same application process, VBA code, and external automation code.

The object that you return must be public, it must be visible to COM, and it must expose the IDispatch interface. If the object you return does not meet these requirements, the Visual Studio Tools for Office runtime will throw an InvalidCastException after it calls your implementation.

Compile the code and open the application (Excel) with the addin installed.

Excel VBA Code to call VSTO Method

The VSTOAddin that was created will be loaded as a COMAddin in Excel. The following code will retrieve the addin information

Sub Execute_VSTO_Addin_Macro()

Dim oAddin As COMAddIn

Dim oCOMFuncs As Object

Set oAddin = Application.COMAddIns("CallVSTOExample")

Set oCOMFuncs = oAddin.Object

oCOMFuncs.SayHello

End Sub

The Object property returns the object represented by the specified COMAddIn object. The VSTO function SayHello is called using the COMAddin’s object as shown above

See also:

Insert Array to Excel Range using VSTO

Create Custom Task Panes for Word using VSTO

Office host applications and their support for add-ins

Copy Array Values to Excel Range using VSTO

Insert Array to Excel Range using VSTO

Many times we need to insert text to a contiguous Excel range using VSTO (like column headings). The following code does exactly that

private void InsertHeading()

{

string[] Header = { "Sno", "Prouct Code", "Product Name", "Quanity", "Price", "Total" };

Excel.Worksheet sht = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

Excel.Range myHeader = sht.get_Range("A1", "F1");

myHeader.Value2 = Header;

myHeader.Columns.AutoFit();

}

VSTO Code Output - Autofit columns

See also:

How to use .Net Array.Sort Function in VBA

Convert Dates to Arrays using Array Function

Array Dimensioning in Visual Basic

Filtering Array Elements

Transferring array to Excel range

How to Return Multiple Values from a VBA Function

Return Multiple Values from a Visual Basic Function


A normal VBA function has a return statement that returns a value to the calling function/subroutine

If you want multiple values to be returned, use reference parameters. The reference parameter represents the same storage location as the argument variable and hence changes made in the function is reflected in the calling function too.

Sub ReturnMultipleValue()

Dim L As Double

Dim B As Double

L = 10

B = 6

Debug.Print L & " " & B

ChangeLengthAndBreadth L, B

Debug.Print L & " " & B

End Sub

The above sub passes the arguments to ChangeLengthAndBreadth function by reference. That is, the storage location is passed to function and the changes made inside the ChangeLengthAndBreadth function is reflected in the main method.

Function ChangeLengthAndBreadth(ByRef Length As Double, ByRef Width As Double)

Length = 1.2 * Length

Width = 1.2 * Width

End Function

Multiple Return Parameters in VBA, Multiple Return Parameters in Visual Basic, Return Multiple Values – VBA Function, VBA Function to return more than one value



Return Multiple Values

See also:

Output Parameters in C# (.NET)

How to get the return value from C# program (console application)

How to insert Symbols in Word Document using VBA

Here is a simple way to insert the commonly used symbols (Copyright, Trademark etc) using Word VBA

Sub Insert_Symbols_Example()

Selection.TypeText Text:="Copyright "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=169, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Trade mark "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8482, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Registered Symbol "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=174, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:=" EURO"

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8364, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Pound "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=163, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Yen "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=165, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Degrees "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=176, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Plus Minus "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=177, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Multiplication Sign "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=215, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Division Symbol "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=247, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Square Root "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8730, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Ohm Sign "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8486, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Dagger "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8224, _

Unicode:=True

End Sub



VBA Insert CopyRight Symbol, VBA Insert Trade mark, VBA Insert Registered Symbol, VBA Insert EURO Sign, VBA Insert Pound Sign, VBA Insert Division Symbol, VBA Insert Square Root Symbol,

See also:

Add Words to AutoCorrect Entries


Saturday, November 22, 2008

How to use collections in Excel VBA

Collection of objects using Excel VBA

Collection provides a convenient way to refer to a group of objects and collections as a single object. The objects and collections in the collection don't have to be of the same data type.

In the following example we will use collections for a class (that is a collection of objects of type Vendor)

First define a class ClsVendor (Insert a Class Module to the project) and add the following item


Public VendorID As String
Public VendorName As String
Public VendorAddress As String


Public Function AddVendor(ByVal sVendorID As String, ByVal sVendorName As String, Optional ByVal sVendorAddress)

VendorID = sVendorID
VendorName = sVendorName
VendorAddress = sVendorAddress

End Function

The class has three public variables that can be initialized by AddVendor method.

Let us define a collection by

Private oColl As New Collection ' Collection to store Vendor

Now let us define a sub to create a collection of Vendors

Sub Add_Vendor_Info()

Dim oVendor As ClsVendor



Set oVendor = New ClsVendor

oVendor.AddVendor "EXV0023", "MCMASTER", "P.O. Box 94930, Cleveland, OH 44101-4930"
oColl.Add oVendor, "MCMASTER"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0024", "SHANTI GEARS"
oColl.Add oVendor, "SHANTI GEARS"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0025", "SKF"
oColl.Add oVendor, "SKF"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0026", "SUNDARAM FASTNERS"
oColl.Add oVendor, "SUNDARAM FASTNERS"

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & " " & oReqVen.VendorName & " " & oReqVen.VendorAddress

End Sub

Nornally, you would be reading the above information from a database or a flat file. You can also use the above example to get the information from an userform and add it to the collection

Here the Vendor objects are added to the collection using Add method. The syntax is as follows

object.Add item, key, before, after

The Add method syntax has the following object qualifier and named arguments:
object - Required. An object expression that evaluates to an object in the Applies To list.

item - Required. An expression of any type that specifies the member to add to the collection.

key - Optional. A unique string expression that specifies a key string that can be used,
instead of a positional index, to access a member of the collection.

before - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection before the member identified by the before argument. If a numeric expression, before must be a number from 1 to the value of the collection's Count property. If a string expression, before must correspond to the key specified when the member being referred to was added to the collection. You can specify a before position or an after position, but not both.

after - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection after the member identified by the after argument. If numeric, after must be a number from 1 to the value of the collection's Count property. If a string, after must correspond to the key specified when the member referred to was added to the collection. You can specify a before position or an after position, but not both.

Here we have added the item and the key

oColl.Add oVendor, "SUNDARAM FASTNERS"

The above adds "SUNDARAM FASTNERS" as a key to the corresponding Vendor. This Key will be useful to retrieve the information from the collection (as shown later)

Once a collection is created, its members can be retrieved using the Item method. The entire collection can be iterated using the For Each...Next statement

The Item method can be used with numeric or string arguments. If you are specifying a string argument, it should match the ‘Key’ of the member

Retrieving a specific member of a Collection object by position

Set oReqVen = oColl(1)
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress


Collection data retrieved using position
Retrieving a specific member of a Collection object by key

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress


Collection data retrieved using Key (EXcel VBA)

To delete a member from a collection using VBA use the remove method

oColl.Remove ("SKF")

or

oColl.Remove (3)

Collection after removing a member

Convert Decimal to Percentage using VBA Format Function

Convert Decimal to Percentage using VBA FormatPercent Function

Here is a simple example to convert a decimal to percentage using VBA 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

Saturday, November 08, 2008

How to Read and Write Configuration Files using VBA

How to Read and Write INI files using VBA

Ini files were extensively used for storing configuration path. Of late this has been replaced with XML. Here is a small snippet for using

Here is a sample INI File

[MOSS_Documents]
AdminDocPath=d:\MOSS\AdminDocs\
ArchiveDocPath="d:\MOSS\DocsArchive\"


Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" _
Alias "WritePrivateProfileStringA" _
(ByVal sSectionName As String, _
ByVal sKeyName As String, _
ByVal sString As String, _
ByVal sFileName As String) As Long

Private Function GetSectionEntry(ByVal strSectionName As String, ByVal strEntry As String, ByVal strIniPath As String) As String

'===================================================================================
'Purpose : Retrieve data from Sample.ini
'Assumptions:
'Effects :
'Inputs : Section Name and Entry Name in the KitBOM ini
'Returns : Value of the Entry in the section
'===================================================================================

Dim X As Long
Dim sSection As String, sEntry As String, sDefault As String
Dim sRetBuf As String, iLenBuf As Integer, sFileName As String
Dim sValue As String

On Error GoTo ErrGetSectionentry
sSection = strSectionName
sEntry = strEntry
sDefault = ""
sRetBuf = Strings.String$(256, 0) '256 null characters
iLenBuf = Len(sRetBuf$)
sFileName = strIniPath
X = GetPrivateProfileString(sSection, sEntry, _
"", sRetBuf, iLenBuf, sFileName)
sValue = Strings.Trim(Strings.Left$(sRetBuf, X))

If sValue <> "" Then
GetSectionEntry = sValue
Else
GetSectionEntry = vbNullChar
End If

ErrGetSectionentry:
If Err <> 0 Then
Err.Clear
Resume Next
End If

End Function

Here is the sub to drive the above function

Sub Store_And_Retrieve_Values()

Dim sIniPath
Dim sBuffer
Dim ret

sIniPath = "d:\temp\VBA_MOSS.ini"
sBuffer = GetSectionEntry("MOSS_Documents", "AdminDocPath", sIniPath)

' Write Data to Ini File
ret = WritePrivateProfileString("MOSS_Documents", "AdminDocPath", "d:\MOSSNew\AdminDocs\", sIniPath)
End Sub

The following will be the output INI file after running the macro

[MOSS_Documents]
AdminDocPath=d:\MOSSNew\AdminDocs\
ArchiveDocPath="d:\MOSS\DocsArchive\"

Keywords: Write INI files using VBA, Read files using VBA Read Configuration Files using VBA, Write Configuration Files using VBA, Excel VBA Ini files, VBA GetPrivateProfileString Function, VBA WritePrivateProfileString function, Read and Write Ini files using Excel VBA, Update an Section in Ini file using VBA, Overwrite a string in INI file using VBA, Replace a string in INI file using VBA
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