Tags

, , , , , , , , , , , ,

There is a little cheat you can use to achieve a very similar effect like the Static keyword in VBA.

You can get away with things like Class1.CreateInstance(param1, param2) where Class1 has not been initialized.

Start notepad and copy paste the below

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Class1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public Sub StaticMethod(param1, param2)
Debug.Print "from StaticMethod:", param1, param2
End Sub

save the file as Class1.cls (just make sure it’s a Class1.cls and not a Class1.cls.txt)

Start a new workbook and open VBE. Right click the VBA Project and import file -> then select the Class1.cls

Now, insert a regular code module and copy-paste the below code

Sub Main()
    Class1.StaticMethod "hello", "world"
End Sub

Execute with F5 and enjoy :)

 

If you like this post you can give an upvote here :)

Difference between a public variable and a property in a class module

Tags

, , , , , , ,

Inspiration come from “VBA Difference between public variable and property” question on Stack Overflow.

As much as VBA is object-oriented it’s still limited in many ways, but as far as this example goes it should be sufficient to just understand the basics of the OOP in VBA.

Sample code

Private pVariable As Integer

Public Property Let Variable(ByVal lVariable As Integer)
    pVariable = lVariable
End Property

Public Property Get Variable()
    Variable = pVariable
End Property

is wrong a bit unnecessary.

NOTE: You may do that in cases where you want to handle errors / validate data coming in but generally if it’s as simple as setting and getting the value you wouldn’t do that.

Why would you ever need a private backing field if you are exposing both the Let/Set and Get properties? All you need for this is the public variable itself and no need for properties.

The story changes 360 degrees when you have to only expose one of the properties and not the other (ie. either setter or getter only ). Maybe it’s easier to understand by working through an example…

Example

Let’s start by working through an easy “banking” example (obviously you wouldn’t do that in VBA in real-life but it’s a good concept to evaluate as a base)

Imagine you have to build a class to simulate a bank account. You need a way to deposit and withdraw money from the account as well display balance.

Normally you wouldn’t have a setter for the balance field because no-one should be allowed to explicitly set the balance. (if you know a bank that allows this please let me know ;)) . The actual balance should be a private variable. There should be a property which exposes it and that’s all you should consider here.

Consider a VBA class (an interface)

IAccountServices.cls

Sub Deposit(amount As Double)
End Sub

Sub WithDraw(amount As Double)
End Sub

and another class to represent the account

Account.cls

Implements IAccountServices

' balance should be private
' cause you should only have a getter for it
' you should only be able to set the balance inside this class
' based on the operations
Private accBalance As Double

' see Getter only - no setter
Public Property Get Balance() As Double
   Balance = accBalance
End Property

Public Function Deposit(amount As Double)
   accBalance = accBalance + amount
End Function

Public Function WithDraw(amount As Double)
   accBalance = accBalance - amount
End Function

Private Sub IAccountServices_Deposit(amount As Double)
   accBalance = accBalance + amount
End Sub

Private Sub IAccountServices_WithDraw(amount As Double)
   accBalance = accBalance - amount
End Sub

NOTE: This obviously is the simplest of simple examples and it does not have any error handling or checking whether the balance is sufficient to withdraw etc. This is just for demonstration purposes and not to be used in a real-life application.

With this encapsulation I see/know right away

  • accBalance is a private field not accessible anywhere outside the class.
  • I can only retrieve the balance() and not explicitly set it on an instance of the Account class.
  • I can deposit() and withdraw() money from the account (publicly accessible methods)

 

In you standard module (module1) even with intelli-sense you get the .Balance listed and that’s all your library/class user ever have to worry about.

Now having a standard coding module to test both classes (Module1)

Sub Main()

    Dim myAccount As Account
    Set myAccount = New Account

    Debug.Print "Starting Balance: " & myAccount.Balance

    myAccount.Deposit (2000)
    Debug.Print "Deposited: 2000"

    myAccount.WithDraw (250)
    Debug.Print "Withdrew: 250"

    Debug.Print "Ending Balance: " & myAccount.Balance

    ' can't set balance
    ' myAccount.Balance = 999999999999999999999999
End Sub

To get an intro to VBA OOP I could recommend:

Reverse order of For Each loop

Tags

, , , , , , , , , , , , , , , , , , , , ,

Reverse order of For Each loop

Have you ever wondered if it was possible to reverse the order in a for each loop in VBA? Certainly, it’s not a built language function of VBA nor it is in even newer languages like C# or VB.NET. In .NET there is a yield keyword and IEnumerable, IEnumerator interfaces which allow you to provide your own implementation which is used in for each loop. In VBA there is no such mechanism because VBA doesn’t allow you to implement IEnumVARIANT interface nor implement/override .Clone(), .Next(), .Skip() and .Reset() methods.

Inspiration of this post comes from this question on Stack Overflow.

As @chris neilsen  has said

“For built in collections (eg a Range) the short answer is: you can’t. For user defined collections the answer linked by @VBlades might be useful, although the cost might outweigh the benifit.”

Well, I thought why not give it a go and see if the assumption about the cost really might outweigh the benefit. I have not seen yet anyone trying to implement this so I thought giving it a try wasn’t a bad idea at all!

The cool thing about using custom collections instead of the build in Collection class is the advantage of providing your own implementation and extending the capabilities. For example: you can add an AddRange() method which allows you to pass another Collection or Array etc. With a little trick with NewEnum()  you can use your own Custom Collection with a for each loop so with a bit of magic (well, not really haha) you can achieve the Reverse Order in a For Each loop in VBA.

After some thinking I have came up with a solution which I would like to share with you :)

NOTE: under the hood it’s not done properly like you would normally do in .NET or C++ but it’s a little sneaky trick which allows you achieve the same effect.

We are talking about the same behaviour as with an iterator stepping backward but using for each loop instead of an iterator and the Step -n.

This

for each item in collection step -1

couldn’t work. Well, it could if you went a step further and implemented a class that takes the code in your code module as a String and interprets it and THEN applies the technique I am going to demonstrate to the analysed block of code…. I honestly think that would have been an overkill though so don’t try it at home. The reversed order in for each is somehow achievable with much less risk of things going wrong and if you ever do need that functionality you can always consider this as an add-in because it’s an OOP concept and easily reusable in your own projects.

What I am going to do instead is this

for each item in collection

but having the return order reversed.

Side note: remember that collections in VBA are index = 1 based.

So, if collection(1) was Item 1 and collection(2) was Item 2 using the reversed order for each loop the print out would be:

for each item in customCollection

    debug.print item

next

Immediate Window:

Item 2

Item 1

Cool? Let’s see how it’s done.

REPRO

Open Notepad++ or your favorite text editor and copy paste the below code. Save it to your desktop as ReversedForEach.cls – NOTE the *.cls file extension. Make sure it’s not saved as  *.txt.cls

VERSION 1.0 CLASS
 BEGIN
 MultiUse = -1
 END
 Attribute VB_Name = "ReversedForEach"
 Attribute VB_GlobalNameSpace = False
 Attribute VB_Creatable = False
 Attribute VB_PredeclaredId = False
 Attribute VB_Exposed = False
 Option Explicit

 Private myCustomCollection As Collection
 Dim reversed As Boolean

 Private Sub Class_Initialize()
     Set myCustomCollection = New Collection
 End Sub

 Private Sub Class_Terminate()
     Set myCustomCollection = Nothing
 End Sub

 Public Sub Add(ByVal ItemToAdd As Object, Optional AddBefore as Long)
     If addbefore <= 0 then
         myCustomCollection.add ItemToAdd
     Else
         myCustomCollection.Add Item:=ItemToAdd, Before:=AddBefore
     End if
 End Sub

 Public Sub Remove(index As Long)
     myCustomCollection.Remove (index)
 End Sub

 Public Property Get Item(index As Long) As Object
     Attribute Item.VB_UserMemId = 0
     Set Item = myCustomCollection.Item(index)
 End Property

 Public Property Get Count() As Long
     Count = myCustomCollection.Count
 End Property

 Public Function getCollection() as Collection
     Set getCollection = myCustomCollection
 End Function

 Public Property Get NewEnum() As IUnknown
 Attribute NewEnum.VB_UserMemId = -4

     If Not reversed Then
         Dim reversedC As New ReversedForEach

         Dim obj
         For Each obj In myCustomCollection
             If reversedC.Count > 0 Then
                 reversedC.Add ItemToAdd:=obj, AddBefore:=1
             Else
                 reversedC.Add obj
             End If
         Next

         Set myCustomCollection = reversedC.getCollection
         Set NewEnum = myCustomCollection.[_NewEnum]

         reversed = True

     Else
         Set NewEnum = myCustomCollection.[_NewEnum]
     End If

 End Property

There are a few things which need to be discussed here:

  • Public myCustomCollection as Collection – a collection is needed to be able to enumerate the ReversedForEach class. It’s made public now instead of using a default class property for simplicity.
  • Dim reversed as Boolean this is to remember whether the collection has already been reversed. If it has then if you do decide to use the for each again then it will not reverse the collection again
  • Private Sub Class_Initialize() and _Terminate() are class events called automatically when you create an instance of that class using the new keyword. You can take advantage of those to instantiate and/or terminate your myCustomCollection collection.
  • Public Property Get NewEnum() As IUnknown – this is a method which returns an IEnumVARIANT type. Without going into a great detail this is needed to be implemented in a custom class with collections to be able to use for each loop along with the attribute Attribute NewEnum.VB_UserMemId = -4. VBE doesn’t allow you explicitly set attributes on custom classes so this is why you need to put this code in a text editor and save it as .cls file. Generally, this method takes care of the reversal of your myCustomCollection. In C# or VB.NET it’s much easier to reverse the order using the yield keywords but VBA does not support that… VBA’s IEnumVARIANT interface is restricted, so you can explicitly implement it and provide your own implementation for the .Next or .Reset – that completely sucks and trying to copy memory and/or play with vtables would just be an overkill IMHO.

Now,

Start a new workbook

Open VBE with ALT+F11

Right click the VBE Project Explorer Window and Import file. Navigate to where you have saved the ReversedForEach.cls and select it

Right click again and insert a regular coding module. Copy – paste the below code to test your ReversedForEach class

Option Explicit

 Sub Main()

     PopulateSheet ' populate sheet with some data

     Dim c As New ReversedForEach

     Dim i As Long
     For i = 1 To 10
         c.Add Range("A" & i)
     Next

     Dim Item

     ' using for each but printing already reversed collection (Items descening)
     For Each Item In c
         Debug.Print "loop 1:" & vbTab & Item
     Next

     Debug.Print vbCrLf

     ' add two items (one as last and one as first)
     c.Add Range("B1")
     c.Add ItemToAdd:=Range("B2"), AddBefore:=1

     ' print again to make sure it's not reversed back to original (Items ascending)
     For Each Item In c
         Debug.Print "loop 2:" & vbTab & Item
     Next

 End Sub

 Private Sub PopulateSheet()
     Cells.ClearContents
     Dim i As Long
     For i = 1 To 10
         Range("A" & i) = "Item" & i
     Next i
     Range("B1") = "Item B1"
     Range("B2") = "Item B2"
 End Sub

Before you run this open up your Immediate Window with CTRL+G and hit F5 to run the code.

You may want to check the active sheet for what the data inserted into your custom collection looks like. It’s very simple

enter image description here

Your Immediate Window should show you what your data looks like using the for each but in reversed order.

enter image description here

Let’s talk about what is happening here in more depth.

NOTE: I am skipping the introduction of how to implement a custom collection class in VBA as I am kind of assuming that anyone reading this blog would have already had some experience with that topic. If you need to catch up see How to use Implements in Excel VBA, How to use comparison methods between class object modules in VBA in a similar manner as VB.NET and Chip Pearson great article on Interfaces

The ReversedForEach class takes care of the reversal of the original collection. let’s look at the Public Property Get NewEnum() As IUnknown

Public Property Get NewEnum() As IUnknown
 Attribute NewEnum.VB_UserMemId = -4

     If Not reversed Then
         Dim reversedC As New ReversedForEach

         Dim obj
         For Each obj In myCustomCollection
             If reversedC.Count > 0 Then
                 reversedC.Add ItemToAdd:=obj, AddBefore:=1
             Else
                 reversedC.Add obj
             End If
         Next

         Set myCustomCollection = reversedC.getCollection

         Set NewEnum = myCustomCollection.[_NewEnum]

         reversed = True

         Set reversedC = Nothing ' free memory
     Else
         Set NewEnum = myCustomCollection.[_NewEnum]
     End If

 End Property

ha, in the end it’s not too complicated :) All it’s happening here is you’re creating a temp Collection named reversedC and copy all the elements of your original myCustomCollection to it.

The if reversedC.Count > 0 then  statement checks whether the reversedC already contains any elements. It shouldn’t. But the check is performed to actually insert the first item without using the AddBefore:= parameter. If there collection was empty and you tried to insert something before the first element you would get a runtime error so this check is added to omit the runtime ‘5’ error.  Just a quick illustration

This fails:

Dim c As New Collection
c.Add Item:="Item X", Before:=1

but this works

Dim c As New Collection
c.Add "Item 1"
c.Add Item:="Item X", Before:=1

The Set myCustomCollection = reversedC.getCollection assigns an already reversed collection in place of the original collection by returning it from a public function getCollection(). This is why the myCustomCollection is now made private as you do not want anyone from an outside world to be able to directly access it. The reversed = true now knows that the original myCustomCollection has already been reversed so if you are going to use the for each twice (or more) it will not keep reversing every time you call the for each on that original class instance. If the collection has been reversed already you just go to the Else block of the statement and return the current [._NewEnum].

Set NewEnum = myCustomCollection.[_NewEnum] returns the next linked element since the last time this function was called (by for each). There is much more to how this actually works and what goes under the hood but I couldn’t wrap my head around this entire concept to demonstrate it as an example or a few sentences. If you really want to know then get familiar with how Enumeration works, see IEnumerable and IEnumerator even though the code is completely different the mechanism is rather similar.

Well, Ok. I don’t think there is much more to discuss here but please feel free to leave me any questions/ feedback / comments :) Thanks for taking the time to read all this :)

 

Optional Parameters and ParamArray in VBA

Tags

, , , , , , , , , , , , , , , , ,

Optional Parameters and ParamArray is not something used very often by most Excel developers I guess after seeing thousands of lines of VBA code…. I am not saying they are hard to understand or use.. you probably have used it before without even recognizing it if you have ever worked with any functions…. It’s good to know what they do and how to use them so you can take a full advantage while designing your own solutions. Personally, I find them both very useful and use them wherever its possible.

Function with no optional parameters

Most of the time functions have a fixed number of arguments they ask for. Simplest example Excel’s worksheet function =COUNTBLANK(range). It takes 1 parameter (range) and returns a number (Long) of empty cells in that specific range. Not much science about it ;)

Optional Parameters 

There are a few rules that you should remember

- Optional parameters are always the last parameters in the list of parameters in a function’s definition

 – Optional keyword is required to indicate that a parameter is optional

- Optional parameters can be of Variant datatype and other (Integer, Long, String, etc ) datatypes but Variants are handled differently from the primary data types ( explained below )

- Optional Parameters do not work with ParamArray ( explained below – with ParamArray )

- You cannot use UDT (User defined types) as Optional Parameters

Right, so a few rules laid out should give you an idea how to implement Optional Parameters in your own functions/subs.

Let’s create an example and analyse it

Optional Parameter of Variant Type + IsMissing() function

Stick the below code in a Module1 in your VBA project
Public Function OptionalVariant(Optional var As Variant) As String
    If IsMissing(var) Then
        OptionalVariant = "Parameter not provided"
    Else
        OptionalVariant = "Parameter provided"
    End If
End Function

Now switch to a spreadsheet view and type below functions in two separate cells

=OptionalVariant()

=OptionalVariant(foo)

The first call =OptionalVariant() returns “Parameter not provided” as expected. The function can be called with no parameter in this case because there is only one that the function takes and it’s optional.

The second call =OptionalVariant(foo) returns “Parameter provided” – also as expected because the function was called with an argument ( foo ).

VARIANT and IsMissing() explanation

As you have noticed in the above example `IsMissing()` is used on a Variant type.  This is because Variant can be Missing. All other data types like Integer, Long, String all have default values ( 0 for Integer and Long, “” for String, False for Boolean, etc ). Variant, however, doesn’t have a default return value. This means that when you are not passing it to a function (in place of the Variant argument) you can’t do nothing with that parameter inside the body of a function/sub.  If you attempt to use the Variant variable that has not been passed to a function you get a Error 448 (“Named argument not found”). 

So, IsMissing() is a special function that only works with Variant data type. When you declare you function like Public Function(Optional i as Long)  the default value for will be 0, no matter if the argument has been passed to the function or not.  If you do this (please don’t in real life – it’s just a failing demo)

Public Function VarIsMissing(Optional var As Long)
    If IsMissing(var) Then Debug.Print var
End Function

The IsMissing(var) returns a False even though logically and practically the argument has been passed ( default is 0 ). So please remember that IsMissing() only works with Variant data type.

Now, let’s create a function which requires 1 parameter and allows you to provide the second one as optional:

Public Function CalculateNet(income As Double,Optional tax As Double=20)
    CalculateNet = income - (income * tax / 100)
End Function

call it from spreadsheet like

=CalculateNet(100)

=CalculateNet(100, 40)

The first call =CalculateNet(100) returns 80. The function is called using only one parameter – the income, and the tax is 20 (Optional Parameter’s default value).

The second call =CalculateNet(100, 40) returns 60 because you are overriding the default 20% with a custom 40%. 

This is quite straight forward, I am sure you can think of many many examples to make yours or someone else’s life easier by designing your functions using Optional Parameters – specially in financial calculations.

Just to demonstrate you can build a function that takes more than 1 optional parameter… let’s see if this is going the right way to go about this problem

Let’s say you need a function to combine strings from cells spread out horizontally. Some cells in between may be empty… Maybe it’s easier to visualize this

Untitled

I think you could/would just go with =A1&””&B1&””&C1  – trivial but let’s use it as an example for our function that user Optional Parameters

Public Function CombineStrings(separator$, Optional str1$, Optional str2$, Optional str3$)
    CombineStrings = IIf(Not (str1 = vbNullString), str1 + separator, vbNullString) + _
                    IIf(Not (str2 = vbNullString), str2 + separator, vbNullString) + _
                    IIf(Not (str3 = vbNullString), str3 + separator, vbNullString)
End Function

Untitled

EASY!? Ain’t it?

The CombineStrings adds an extra functionality using IIF() to handle empty cells not adding an extra space. It’s quite easy to understand if you simply analyse one line at a time IIf(Not (str1 = vbNullString), str1 + separator, vbNullString)

NOTE:    & vs +

This is not an error. In VBA when concatenating string you can use either + or &. Note that + works only between all String data types. INT/LONG/DOUBLE + STRING produces a type-mismatch error.

Well, in this example you can only pass max of. 4 parameters to your function, what would happen if you needed like 1337 of those? What if they were mixed numbers, strings, booleans, etc? You would need to know which cell to pass in which place on the list of parameters ( if you werent using a Variant for the parameter type – let’s assume you were not ). What if you didn’t know until the run-time how many arguments you are actually going to need to pass to your function?

Aha! this is where a ParamArray comes in really handy!

A few rules abour ParamArrays:

- ParamArray must be an array of Variant type

- ParamArray must be the last parameter in the list of parameters in your function definition

- You cant use Optional Paramters along with ParamArray – ParamArray is Optional itself!

- ParamArray index starts at 0, if ParamArray is empty the upper bound is -1.

- You cant user UDFs as a parameter for a ParamArray

Let’s see an example —

On your spreadsheet, in any cell type =SUM(    and hold it for a while… look

Untitled

list of numbers… What does this mean? You can pass almost any number of parameters, there is no fixed length.. is it a ParamArray? I don’t know 100% ask Spolsky hehe

The whole point is that when you don’t know the amount of parameters you may need to use for a function at run-time the ParamArray is the way to go.

let’s modify the previous example just a bit so it allows you to concatenate any amount of words using a ParamArray.

Public Function CombineStrings(separator$, ParamArray words() As Variant) As String
   ' check if your words ParamArray is not empty
   If UBound(words) > 0 Then
       Dim combinedStr$
       Dim word As Variant
       For Each word In words
          combinedStr = combinedStr & IIf(word <> " ", word + separator, vbNullString)
       Next
       CombineStrings = combinedStr
   Else
       CombineStrings = vbNullString
   End If
   CombineStrings = combinedStr
End Function

So now you can go back to your spreadsheet and do this

Untitled

And for the final exercise copy-paste the below sub and run ;)

Sub ParamArrayTest()

   Application.ActiveWindow.Zoom = 85
   Range("A1:Z1").ColumnWidth = 3

   Call PaintCells("A1", "B2", "B3", "C4", "C5", "D6", "D7", "E5", "E4", "F3", "F2", "G1", "I1", "I2", "I3", "I4", "I5", "I6", "I7", "J1", "K1", "L2", "K3", "J4", "K4", "L4", "M5", "M6", "J7", "K7", "L7", "O7", "O6", "P5", "P4", "Q3", "Q2", "R1", "Q5", "R5", "S5", "S2", "S3", "T4", "T5", "U6", "U7")

End Sub

Private Sub PaintCells(ParamArray cellToPaint() As Variant)
   Dim cell As Variant
   For Each cell In cellToPaint
       Range(cell).Interior.Color = RGB(100, 160, 255)
   Next
End Sub

Creating an Excel file, writing VBA code to it, running a macro, and saving it as a Macro Enabled Workbook – all done using C#

This comes from my answer on Stack Overflow.

Let me show you how to

  • Create an Excel file
  • Write VBA code to VBA Project
  • Run the macro you’ve just written
  • Save your workbook as a Macro Enabled Workbook

You need to allow programmatic access to VBA Project in Excel.

  • (Excel) File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model

enter image description here

In your C# Solution add COM references to

  • Microsoft Visual Basic for Applications Extensibility 5.3
  • Microsoft Excel 14.0 Object Library

Add using directives to your C# code behind file

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Vbe.Interop;
using System.Runtime.InteropServices;

Now follow the code and comments

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

VBProject vbProj = wb.VBProject; // access to VBAProject
VBComponent vbComp = vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); // adding a standard coding module
vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines); //emptying it

// this is VBA code to add to the coding module
string code = "Public Sub HelloWorld() \n" +
" MsgBox \"hello world!\" \n" +
"End Sub";

// code should be added to the module now
vbComp.CodeModule.AddFromString(code);

// location to which you want to save the workbook - desktop in this case
string fullPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\macroWorkbook.xlsm";

// run the macro
xlApp.Run("HelloWorld");

// save as macro enabled workbook
wb.SaveAs(Filename: fullPath, FileFormat: XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
xlApp.Quit();

In the above code;

you create an host Excel application, add a workbook. Access VBA Project Object module, add a new standard coding module to the VBA Project, write a VBA macro to that module. `Application.Run(“HelloWorld”)` simply calls the macro – note you can comment out this section if you don’t want the box to pop-up. Then in the end you save the workbook as a *Macro Enabled Workbook* which to a location specified in `fullPath` variable.

*PS. Please note I haven’t added any error handling.*

Hope this helps :)

Create a shortcut in Excel for a custom operation.

Tags

, , , , , , , , , , , , , ,

This morning, I came across this question on Stack Overflow and after digging around for a bit I actually came up with a solution. I decided to post the solution on my blog because I didn’t come across one within the first page of Google search.

The question is:

I would like to overwrite an excel keyboard short-cut. For instance, ctrl-e must now center the text in a cell.

I have never really worked with shortcuts except to run macros. So I came across this but it didn’t help much

So I kind of knew there was no other way (correct me if I am wrong) to override a shortcut but to assign a macro to it. Well, you’re in a .Net framework, there are no macros in your project – you’re using C#.  What do you do now?

Since you need a macro, let’s make the macro first then we will worry how to glue it all together…

“CTRL+E must centre the text in a cell” – from VBA point of view this really easy. You can record a macro if you don’t know the right syntax for this operation but from “experience”

Sub CentreText()

    activeCell.HorizontalAlignment = xlCenter

End Sub

We have a working macro, now we need to assign a key to it…

Application.OnKey("^e", "CentreText")

It doesn’t get simpler than that…

Ok this works within Excel but how do I get this to work in a VSTO add-in?

We need to have a macro in our workbook in order to use Application.OnKey()

We are simply going to build this programmatically… watch:)

void addModule()
{
    VBProject vbProj = Globals.ThisWorkbook.VBProject as VBProject;
    VBComponent vbComp =
     vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
    vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines);

    string code = "Public Sub CentreText() \n" +
                  " activeCell.HorizontalAlignment = xlCenter \n" +
                  "End Sub";

    vbComp.CodeModule.AddFromString(code);
}

This inserts a new standard coding module into your Workbook and writes 3 lines of code to it which means now we have a macro available in our workbook. The only thing that is left to do is to tell our Application that OnKey event we want it to run the macro (macro centring text horizontally)

 

addModule();
Worksheet ws = Globals.ThisWorkbook.ActiveSheet as Worksheet;
Workbook wb = ws.Parent as Workbook;
Microsoft.Office.Interop.Excel.Application app =
    wb.Parent as Microsoft.Office.Interop.Excel.Application;
app.OnKey("^e", "CentreText");

 

And there you go :)

Debug /Run your C# solution. Write some text to a cell and hit CTRL+E to centre it horizontally.

 

Add and remove COM references programmatically

Tags

, , , , , , , , , , , , , , , , , , , , , ,

Firstly, let’s see how to obtain currently active COM references.

By default, you should have 4 references attached to your VBA project.

Untitled

Count, Name, Description, GUID, Major, Minor, Fullpath

1. VBA Visual Basic For Applications {000204EF-0000-0000-C000-000000000046} 4 1 C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
2. Excel Microsoft Excel 14.0 Object Library {00020813-0000-0000-C000-000000000046} 1 7 C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
3. stdole OLE Automation {00020430-0000-0000-C000-000000000046} 2 0 C:\Windows\system32\stdole2.tlb
4. Office Microsoft Office 14.0 Object Library {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} 2 5 C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL

And the code to obtain currently attached references

Sub Grab_References()

Dim i As Long
Dim arr() As String
Dim cnt As Long

cnt = ActiveWorkbook.VBProject.References.Count
ReDim arr(1 To cnt, 1 To 6)

On Error Resume Next
For i = 1 To ActiveWorkbook.VBProject.References.Count
arr(i, 1) = ActiveWorkbook.VBProject.References.Item(i).Name
arr(i, 2) = ActiveWorkbook.VBProject.References.Item(i).Description
arr(i, 3) = ActiveWorkbook.VBProject.References.Item(i).GUID
arr(i, 4) = ActiveWorkbook.VBProject.References.Item(i).Major
arr(i, 5) = ActiveWorkbook.VBProject.References.Item(i).Minor
arr(i, 6) = ActiveWorkbook.VBProject.References.Item(i).fullpath
Next i

For i = LBound(arr) To UBound(arr)
Debug.Print (i & ". " & arr(i, 1) & vbTab & arr(i, 2) & vbTab & arr(i, 3) & vbTab &         arr(i, 4) & vbTab & arr(i, 5) & vbTab & arr(i, 6))
Next i
End Sub

After running check out your Immediate Window (ctrl+G)

Untitled

This shouldn’t give you any trouble understanding how to retrieve active references. I have created a 2D array to store all the information of one reference in one block (line) or array. Simply, for easy access. So the first loop (the one after On Error Resume Next) is collecting the references and copying it to array. The second loop is simply printing it to the Immediate Window ( see Immediate Window print out).

—————————————————–

Adding new References ( from GUID and from files )

GUID – Globally unique identifier.

Where do you get GUID of a COM object. There are a few ways to easily get GUID. You can

  • Find GUID using CMD
  • Add references manually at least once and then run Grab_References() 
  • Scan registry ( if the type libraries are registered on your machine, if you are referencing other VBA projects than you are most likely going to want to use the AddFromFile method)
  • Use a freeware to identify the guid ( along with the path most of the time )

Ok, so you know GUID and you know full path to the *.tbl ( type library ) and .dll ( dynamic link library )

How do you use that information in VBA to add references programmatically? It’s super easy:

Just start typing…

ThisWorkbook.VBProject.References.

after the dot VBE intelli-sense should pop-up and give you a list of possible choices ie.

Untitled

So you can AddFromFile or AddFromGuid (click on each to see MSDN developers reference)

You will quickly find out that to work with the VBA project object model you need to grand access to it

Untitled

So a sample for adding references from GUID could be

Sub AddReference()

 Dim i As Long
 Dim guidArr() As String
 ReDim guidArr(1 To 9, 1 To 3)

 ' reference details
 ' Name: VBA
 ' Description: Visual Basic For Applications
 ' GUID: {000204EF-0000-0000-C000-000000000046}
 ' Major: 4
 ' Minor: 1
 ' FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
 guidArr(1, 1) = "{000204EF-0000-0000-C000-000000000046}"
 guidArr(1, 2) = "4"
 guidArr(1, 3) = "1"

 ' reference details
 ' Name: Excel
 ' Description: Microsoft Excel 14.0 Object Library
 ' GUID: {00020813-0000-0000-C000-000000000046}
 ' Major: 1
 ' Minor: 7
 ' FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
 guidArr(2, 1) = "{00020813-0000-0000-C000-000000000046}"
 guidArr(2, 2) = "1"
 guidArr(2, 3) = "7"

 ' reference details
 ' Name: stdole
 ' Description: OLE Automation
 ' GUID: {00020430-0000-0000-C000-000000000046}
 ' Major: 2
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\stdole2.tlb
 guidArr(3, 1) = "{00020430-0000-0000-C000-000000000046}"
 guidArr(3, 2) = "2"
 guidArr(3, 3) = "0"

 ' reference details
 ' Name: Office
 ' Description: Microsoft Office 14.0 Object Library
 ' GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
 ' Major: 2
 ' Minor: 5
 ' FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
 guidArr(4, 1) = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
 guidArr(4, 2) = "2"
 guidArr(4, 3) = "5"

 ' reference details
 ' Name: MSForms
 ' Description: Microsoft Forms 2.0 Object Library
 ' GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
 ' Major: 2
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\FM20.DLL
 guidArr(5, 1) = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
 guidArr(5, 2) = "2"
 guidArr(5, 3) = "0"

 ' reference details
 ' Name: ADODB
 ' Description: Microsoft ActiveX Data Objects 6.0 Library
 ' GUID: {B691E011-1797-432E-907A-4D8C69339129}
 ' Major: 6
 ' Minor: 0
 ' FullPath: C:\Program Files (x86)\Common Files\System\ado\msado15.dll
 guidArr(6, 1) = "{B691E011-1797-432E-907A-4D8C69339129}"
 guidArr(6, 2) = "6"
 guidArr(6, 3) = "0"

 ' reference details
 ' Name: Scripting
 ' Description: Microsoft Scripting Runtime
 ' GUID: {420B2830-E718-11CF-893D-00A0C9054228}
 ' Major: 1
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\scrrun.dll
 guidArr(7, 1) = "{420B2830-E718-11CF-893D-00A0C9054228}"
 guidArr(7, 2) = "1"
 guidArr(7, 3) = "0"

 ' reference details
 ' Name: Scriptlet
 ' Description: Microsoft Scriptlet Library
 ' GUID: {06290C00-48AA-11D2-8432-006008C3FBFC}
 ' Major: 1
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\scrobj.dll
 guidArr(8, 1) = "{06290C00-48AA-11D2-8432-006008C3FBFC}"
 guidArr(8, 2) = "1"
 guidArr(8, 3) = "0"

 ' reference details
 ' Name: VBIDE
 ' Description: Microsoft Visual Basic for Applications Extensibility 5.3
 ' GUID: {0002E157-0000-0000-C000-000000000046}
 ' Major: 5
 ' Minor: 3
 ' FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
 guidArr(9, 1) = "{0002E157-0000-0000-C000-000000000046}"
 guidArr(9, 2) = "5"
 guidArr(9, 3) = "3"

 On Error Resume Next

 For i = 1 To 9
     ThisWorkbook.VBProject.References.AddFromGuid GUID:=guidArr(i, 1), Major:=guidArr(i, 2), Minor:=guidArr(i, 3)
 Next i

End Sub

Creating an array of the references you want to add and then simply iterating over that array and adding it using AddFromGuid method.

To add references from file it’s as simple as

ThisWorkbook.VbProj.References.AddFromFile "C:\WINDOWS\system32\..."

—————————————————–

Removing references

Sub RemoveReferences()
Dim chkRef As Reference
Set chkRef = ThisWorkbook.VBProject.References.Item(5)
ThisWorkbook.VBProject.References.Remove chkRef
End Sub

Using a .NET ADODB.Connection object in VBA via COM library to be able to use .NET attributes within VBA

Tags

, , , , , , , , , , , , , , , , , , , , ,

Ahh I see VBA connection string doesn’t support the Application Name attribute. It simply isn’t being recognized when used within VBA. The only way I can think of solving this at the moment it’s to return anADODB.Connection object from a COM C# library.

Your own COM library would return an ADODB.Connection object with a predefined connection string which seem to work in .NET. You will be connecting to the database using a VBA ADODB.Connection object but with a substituted object reference. Instead of

Set cn = new ADODB.Connection you will use a GetConection() method exposed by your own library.

Dim cn as ADODB.Connection
Set cn = yourCOMlibrary.GetConnection

or something like that

This may not be too very useful to you in this case but I think maybe some future visitors will find it useful.

here are the steps to reproduce

Download and install Visual Studio Express for Windows (FREE)

Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to MyConnection

enter image description here

In the Solution Explorer, rename Class1.cs to ServerConnection.cs

enter image description here

Right click your MyConnection project in the Solution Explorer and select Add Reference

Type activeX in the search box and tick the Microsoft ActiveX Data Objects 6.1 Library

enter image description here

Copy and paste the below code into the ServerConnection.cs completely replacing whatever is in the file.

using System;
using System.Runtime.InteropServices;
using System.IO;
using ADODB;

namespace MyConnection
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("32A5A235-DA9F-47F0-B02C-9243315F55FD")]
    public interface INetConnection
    {
        Connection GetConnection();
        void Dispose();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("4E7C6DA2-2606-4100-97BB-AB11D85E54A3")]
    public class ServerConnection : INetConnection, IDisposable
    {
        private Connection cn;

        private string cnStr = "Provider=SQLOLEDB; Data Source=SERVER\\DB; Initial Catalog=default_catalog; User ID=username; Password=password;Application Name=MyNetConnection";

        public Connection GetConnection()
        {
            cn = new Connection();
            cn.ConnectionString = cnStr;
            return cn;
        }

        public void Dispose()
        {
            cn = null;
            GC.Collect();
        }
    }
}

Locate the cnStr variable in the code and UPDATE your connection string details.

Note: if you are unsure about the connection string you should use see ALL CONNECTION STRINGS

Click on TOOLs in Visual Studio and CREATE GUID

Replace the GUIDs with your own and remove the curly braces so they are in the same format as the ones you see now from the copied code

enter image description here

Right click MyConnection in the Solution Explorer and select Properties.

Click the Application tab on the left side, then Assembly Info and tick Make Assembly COM-Visible

enter image description here

Click the *Build* from the menu on the left and tick Register For COM Interop

enter image description here

Note: If you are developing for 64-bit Office then make sure you change the Platform Target on theBuild menu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.


Right click MyConnection in the Solution Explorer and select Build from the menu.

If everything went OK then your MyConnection.dll and MyConnection.tlb should be successfully generated. Go to this path now

enter image description here

C:\Users\username\desktop\

or wherever you saved them

and you should see your files.

enter image description here


Now open Excel and go to VBE. Click Tools and select References.

Click the Browse button and navigate to the MyConnection.tlb.

Also, add references to Microsoft ActiveX Object 6.1 Library – this is so you can use ADODB library.

enter image description here

Now right click anywhere in the Project Explorer window and Insert a new Module

copy and paste the below code to it

Option Explicit

Sub Main()

    Dim myNetConnection As ServerConnection
    Set myNetConnection = New ServerConnection

    Dim cn As ADODB.Connection
    Set cn = myNetConnection.GetConnection

    cn.Open

    Application.Wait (Now + TimeValue("0:00:10"))

    cn.Close
    Set cn = Nothing

    myNetConnection.Dispose

End Sub

Open SQL Server Management Studio, right click the server and select Activity Monitor

enter image description here

dont close this window


Go back to Excel and hit F5 or hit the green play button on the ribbon.

enter image description here

now switch back to SSMS ( SQL Server Management Studio )

and wait for your custom connection name to appear! :)

Here we go! That was easy, wasn’t it? :)

enter image description here


This is what is happening.

You are returning an ADODB Connection object from you C# COM library by usingmyNetConnection.GetConnection function

Dim myNetConnection As ServerConnection
Set myNetConnection = New ServerConnectionDim cn As ADODB.Connection
Set cn = myNetConnection.GetConnection

It’s almost like saying Set cn = new ADODB.Connection but with predefined connection string which you did in your C# code.

You can use the cn object like a normal ADODB.Connection object within VBA now.

Remember to always .Close() the ADODB.Connection. A good programmers practice is to always close anything you open – streams, connections, etc.

You can rely on the Garbage Collector to clear the free references to objects but I also wrote aDispose() method for you so you can half-manually free the Connection so it does not hang in the SSMS as opened.

Remember to use myNetConnection.Dispose along with the cn.Close and you’ll be fine.

Note:

This is how I would do it if any one thinks this is wrong or needs to be updates (as being unstable orunsafe) please leave a comment.

Extending the functionality of VBA forms by using C# COM libraries. Specific: OpenFileDialog box pointing to My Computer by default.

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

AFAIK there is no VBA solution for this that’s why I decided to write a tutorial about how to achieve the effect of having the OpenFileDialog point to My Computer by default.

You can print all the environmental variables using the Environ$() function. This will not show any variable directly pointing to MyComputer therefore you can’t pass it to the .InitialFileNameproperty.

MyComputer is not a physical location that you can access through cmd. I think of it as an abstract Interface and it’s quite difficult to explain how VBA and .InitialFileName uses a string to access a location.

Well, the only workaround the problem I can think of it’s to use an external library written in for example C# that can access the MyComputer.

It’s easier than it sounds!

Follow the below steps to create your Custom OpenFileDialog.

You need a Visual Studio Express For Desktop – it’s free to download and use.

After installation – run as Administrator! (it’s necessary for the libraries to get registered)

Select File and New Project. Rename it to CustomOFD and and hit the OK.

enter image description here

Right-click the CustomOFD Project in the Solution Explorer and Select Add References

Add references to the System.Windows.Forms as shown in the below img

enter image description here

Right-click Class1.cs in the Solution Explorer and rename it to CustomOFD.cs.

Double click your CustomOFD and replace the code with the one from below

using System;
using System.Runtime.InteropServices;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CustomOpenFileDialog
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("541EDD34-4CDC-4991-82E9-6FC23F904B5B")]
    public interface ICustomOFD
    {
        DialogResult ShowDialog();
        string FileName();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("E33102F0-B3C0-441C-8E7A-B9D4155A0D91")]
    public class CustomOFD : ICustomOFD
    {
        private OpenFileDialog box = new OpenFileDialog();

        public CustomOFD()
        {
            box.Multiselect = false;
            box.Title = "Select file";
            box.InitialDirectory = "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}";
        }

        public DialogResult ShowDialog()
        {
            return box.ShowDialog();
        }

        public string FileName()
        {
            return box.FileName;
        }
    }
}

Note: you can generate a new GUID for your own class using the Tools => Create GUID and replace it with your own, if you wanted to…

Right-click the CustomFileOpenDialog in the Solution Explorer and select Properties

enter image description here

In the Properties window go to Application tab and click Assembly Info and tick the Make COM-Visible box

enter image description here

Then go to the Build tab and tick Register for COM interop

enter image description here

Right-click the project and select Build from the menu

Now look in the Output tab as it shows you where the library was compiled to

usually its

c:\users\administrator\documents\visual studio 2012\Projects\CustomOpenFileDialog\CustomOpenFileDialog\bin\Debug\CustomOpenFileDialog.dll

Ok. Now save and close VS.

Open Excel and go into VBE ALT+F11 and insert a standard module

Click Tools on the menu bar and select References

Click the Browse button and navigate to the CustomOpenFileDialog.tlb file and click OK add to the list of references

Copy paste the code for module

Option Explicit

Sub Main()

    Dim ofd As New CustomOFD
    Set ofd = New CustomOFD

    ofd.ShowDialog

    Debug.Print ofd.Filename
End Sub

enter image description here


finally, run the sub and enjoy the computer as the default location for the customized OpenFileDialog box!

enter image description here

VBA classes, interfaces, implements and adequate comparison methods

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

VBA doesn’t support class polymorphism so I recommend to change the way you’re thinking about the Employee and Manager classes.

You can’t have an Employee class as a base class and then a separate Manager class that derivesfrom Employee. They could be 2 separate classes implementing a common interface.

I will talk about it in detail in a bit. Let’s now go through a few examples…


↓ Easy approach ↓


base class (Person) and child classes which derive from the base class. (applies to C#, VB.NET, etc)

but in VBA you have to think of it like this:

A base class which exposes an enum property describing the position.

Something like

enter image description here

enter image description here

This is the easiest way to have a class exposing some properties. It allows you to add your Personobjects into a collection and iterate over using the easy for each loop with Intellisense!

enter image description here

Properties comparison system would be very very easy

enter image description here

note: same applies to enum as its implicitly converted to a number


↓ More complex approach ↓


Two separate classes which both expose public properties. For instance you have an Employee andManager classes which both implement a Person Interface and an additional Comparer class exposing a Compare() method

enter image description here

In your VBA Project you need 4 class modules and a standard module

enter image description here

Person (this is your Interface)

Public Property Get Name() As String
End Property

Public Property Get Age() As Long
End Property

this class is the interface which both the Employee and Manager both need to implement to share some common functions (getters for Names and Ages). Having the interface allows you to do the for each loop using the interface type variable as the enumerator. You will see in a minute.

Employee and Manager are identical. Obviously you may modify them to suit your real life solution.

Implements Person

Private name_ As String
Private age_ As Long

Public Property Get Name() As String
    Name = name_
End Property

Public Property Let Name(ByVal Value As String)
    name_ = Value
End Property

Public Property Get Age() As Long
    Age = age_
End Property

Public Property Let Age(ByVal Value As Long)
    age_ = Value
End Property

Private Property Get Person_Name() As String
    Person_Name = Name
End Property

Private Property Get Person_Age() As Long
    Person_Age = Age
End Property

ComparerCls you will use an instance of this class to compare two objects properties or references. You do not necessarily need to have a class for this but I prefer it that way.

Public Enum ComparisonMethod
    Names = 0 ' default
    Ages = 1
    References = 2
End Enum

' makes names the default comparison method
Public Function Compare(ByRef obj1 As Person, _
                        ByRef obj2 As Person, _
                        Optional method As ComparisonMethod = 0) _
                        As Boolean

    Select Case method
        Case Ages
            Compare = IIf(obj1.Age = obj2.Age, True, False)
        Case References
            Compare = IIf(obj1 Is obj2, True, False)
        Case Else
            Compare = IIf(obj1.Name = obj2.Name, True, False)
    End Select

End Function

And your Module1 code

Option Explicit

Sub Main()

    Dim emp As New Employee
    emp.Name = "person"
    emp.Age = 25

    Dim man As New Manager
    man.Name = "manager"
    man.Age = 25

    Dim People As New Collection
    People.Add emp
    People.Add man

    Dim individual As Person
    For Each individual In People
        Debug.Print TypeName(individual), individual.Name, individual.Age
    Next

End Sub

run the Main() sub and check out the results in the Immediate Window

enter image description here

The best part of the above code is the fact that you creating a reference variable of the Personinterface. It allows you to loop through all items in collection which implement the interface. Also, you can use the Intellisense which is great if you have had many more properties and functions.


Comparison


Take a look again at the code in the ComparerCls class

enter image description here

I hope you see now why I have separated this to be a class. Its purpose is just to take care of the way the objects are being compared. You can specify the Enum order and modify the Compare() method itself to compare differently. Note the Optional parameter which allows you to call the Compare method without the method of comparison.

enter image description here

Now you can play around passing different parameters to the compare function. See what the results are like

try combinations

emp.Name = "name"
man.Name = "name"

Comparer.Compare(emp, name, Names)
Comparer.Compare(emp, name, References)Comparer.Compare(emp, emp, References)

If something is still unclear refer to this answer about the Implements keyword in VBA

Follow

Get every new post delivered to your Inbox.