Buttons and spreadsheet duplicaton with Excel VBA

If you want to add Button on an excel spreadsheet you have two choice

1) Active-x buttons
2) Form buttons


I you plan to duplicate a spreadsheet whic contains buttons using VBA Code, than you must use
Form buttons not Active-x ones.
If you use Active-x buttions you might get a VBA run time errors, which now on top of my head I don't rememeber.

So the tip here is
Use Form buttons if you plan to duplicat using VBA code to duplicate excel spread-sheets that contains them.

Using implements behind an Excel worksheet function

Since an excel worksheet is represented by a class module in vba, you might be tempted, as I was, to use the implements keywords behind a worksheet.
This would allow you to use polymorphically an Excel worksheet and could open-up differ possibilities.
Howev this is my advice

DO NOT USE IMPLEMENTS BEHING A WORKSHEET

I have noticed that despites the code compiles, the overall worksheet becomes unstable and tend to crash!
In additon the TypeOf function applied to the worksheet object that use the implements keyword, does not always behaves as you would expect.
Ex:
if you write on top of an excel worksheet module

Implements IEngine


Some time the test TypeOf sht is IEngine will return false even if it is implementing the interface.

Excel Tip of the Day: INDIRECT

The INDIRECT function is a pretty hard function to understand at first glance. However all you need to know is this

1) It converts a string into a cell Reference
2) It does not work with named formula
3) INDIRECT is a volatile function
4) It is often used in conjuction with the function ADDRESS


 1) It converts a string into a cell Reference


 =INDIRECT("A1")

Is equivalent to a foruma =A1

=INDIRECT("TblOrders")

it gives you back a reference to the TblOrders Table. This is Equivalent to a formula =TblOrders
The advantage is that you can form the string using formulas to make dyamically build reference to table objects


2) INDIRECT does not work with named formula

if you have a named formula like   myrange  =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
and then you use =INDIRECT("myrange") this will not be equivalent to =myrange.

However if you do something like myrange = $A$3:$C$10
and then you type =INDIRECT("myrange") this will work fine and will be equal to =myrange

This means that if you are trying to use in a list validation INDIRECT(C3) where C3="mylist" and
mylist  = OFFSET($A$1,1,0,COUNTA($A:$A)-1,1),
this dynamic validation procedure will fail - INDIRECT is poiting to a named formula

You will instead need to use something like

 C3 = "mylistheader", whe mylistheader is a named cell,
mylistheader = $A$1.
mylistheadeCol = $A:$A

OFFSET(INDIRECT(C3),0,0,COUNTA(INDIRECT(A1&"Col")),1)

INDIRECT(C3) = a referece to $A$1
COUNTA = will count the name in the list

This will work just fine

3) INDIRECT is a volatile function

This mean that Excel recomputes it each time it recalculate the spread sheet. It make the spreadsheet very heavy. Use it sparingly.

4) It is often used in conjuction with the function ADDRESS
    to dynamically build range reference 

VBA Error Handling

In my previous post I showed the difference between

Break on All Errors
Break in Class module
Break on unhandled errors

After looking at each of them we came to the conclusion that we should really use Break on unhandled errors as our default option. See my post
We this option on, however we need to set up o more sofisticated approach to make our life easier.
I looked into the problem and the best approach I could find is the one described in details in the book,
Professional Excel Developers, Chapter 15, VBA Error Handling.
The Error Handling system described there are two: the functin return value and the re-throw method.
I will not go into the details of the two system in this post, I will just add few comments of mine and present you the main ideas.
To start with, The main Vba keyword to deal with Error handling are

1) The object Err
2) On Error Goto Label
3) On Error Resume Next
4) Resume / Resume Next / Resume Label
5) On Error Goto 0

The Err object is a global object whose property are filled by Vba as an error occurs.
Err.Number, Err.Source, Err.Descripton and Err.Raise are by far the most important ones.

Each time we meet a Exit Sub, Exit Fucntion, Exit Property, End Sub, End Function, End Property, Resume and On Error statement the property of the object error are reset. So some time we want to be carefull and store then into some variables.


An Error Handler is a labeled section of a procedure that will run as an error occur.

The Only way into this part of the code is an error, the only way out of this code should be a resume statement. You can see an example here.

Private Sub MySub()
    On Error Goto ErrorHandler  
   'Some code goes here
  
ExitProc:
  Exit Sub
ErrorHandler:
  'Clean up code goes here
  if CentralErrorHandler("Mymodule","MySub") Then
     Stop
     Resume
  else
     Go to ExitProc
  End if

Exit Sub

The second importan principle is the Single Exi Point. Eache time we write a procedure, we need to make sure that there is a single exit point. In this example is ExitProc.

The call to the CentralErrorHandler funcion happens only when we cannot deal with the error within the code, so an exception must be raised. In my personal implementation I actually changed the name of the function from CentraErrorHandler to Exception.LogMe, or if you want Exception.Inizialize.

The CentralErrorHandler function will be responsible for
1) Log erros to a txt log file
2) Activate or deactivate the Debug mode
3) Show a message to the User is we are at an entry point or in Debug mode
4) Re-raise the error is we are not at an entry point or we are not in Debug mode

The call looks like

Public Function CentralErrorHandler(module,proc,entryPoint,showMessage) as boolean

 module and proc tells the CentralErrorHandler what it the source of the error. In this case MyModule:MySub

entryPoint tells it if we are at an entry point.
showMessage tells it if we need a message displayed.


The CentralErroHandler function looks like

Public Function CentralErrorHandler(module,proc,entrypoint,showmessage) as boolean

  'Store the variable of the Global Error message
   Static errMsg as string


   errNum = Err.Num
   errSource = Err.Source
   errDes = Err.Description
   
   'We cannot allow errorn in the CentralErrorHandler
   On Error Resume Next

   errFullSource = module & ":" & proc 
   errLogTxt = errFullSource & " " &  Err.Num & " " & Err.Des
   
   if len(errMsg) = 0 Then errMsg = Err.Description
 
  'Log the errLogTxt Error into a text file

   if entryPoint OR DebugMode then
      if showMessage Then msgbox(errMsg)

      errMsg = vbNullString  

   else

      On Error Goto 0 
      Err.Raise errNum,  errFullSource, errMsg

   end if

End Function

The idea is
1) We store first the property of the Err Object, otherwise they will be reset by the call to Resume
2) We create the new source code and txt to be logged
3) We log the error to the file. We could add: if ToBeLog Then SaveToFile()
4) the errMsg is Static, which means that we will show the original message
5) If we are in DebugMode or at an EntyPoint show a message an reset the string
6) Otherwise re-raise the error

DebugMode is a boolean costant that tells the compiler if we are in DegubMode of not. We can define in at the module level that contains the global error handler.

In few words: when we call the CentralErrorHanlder and we are at an entry point or Debugmode is true a msgbox is shown and the program stops and the errMsg string is cleared. (see the example above on how to call it)
If the debug mode is false and we are not at entry point, a message is re-thrown, with the original Err.Num and Err.Description, but a new Source: MySub:MyModule.

What is an entryPoint?
An entry point is a point from which the user can start execution: menu button, worksheet events...

 For the System to work we need
1) Any Entry point procedure must never call another entry point procedure. If two of them need to run the same code, we can move the code out to a non-entry level procedure
If this happens, we have that the the entry point procedure called, will show a message rather than raising the error up to the caller.

A special case are the Excel User define Functions, which I still need to made my mind up how to treat them.

If we set an Excel UDF as EntryPoint = False and DebugMode = False than an error is re-thrown, so an Excel UDF must have EntryPoint = True

If we set for an Excel UDF EntryPoint = True then as there is an error a msgbox will be diplayed. This is not compliant from what you would expect for an Excel UDF, and image what happens if we had to run hundreds of them.

So we can have Excel UDF EntryPoint = True, showMessage = False.
This gets better, no message anymore.
But if we have 200,000 calls with an error we will log it 200,000 time in the txtfiles, whis is kind of inefficient.
So we could have

ExcelUDF EntryPoint = True, showMessage = False, LogTxt = False

This is getting better, but as you see we have nearly turned off all the Central handling facilities!
No message, no error re-thrown, no txt log. This begs the queston do we need a CentralErrorHandling at all for an Excel UDF?

Lastly if we define an ExcelUDF EntryPoint = true, than we cannot call it from any other part of the code, which is pretty limiting.
So what we can do it to move the code from the entry point to another internal function such that

Public Function MyUDFFunc() as Variant
   MyUDFFunc = MyUDFFuncInternal()
End Function

So that we can set MyUDFFuncInternal EntryPoint = False, in such a case the msgbox will be shown only in Debugmode.    EntryPoint = False, showMessage = True , LogTxt = False

Then MyUDFFunc instead does not need any handler at all.
If we are in DebugMode = True, all the debugging will happen in MyUDFFuncInternal, which will show a message and stop
If we are in DebugMode = False, any error will be logged by MyUDFFuncInternal, whill will no show any message because DebugMode = False, EntryPoint = False. If MyUDFFuncInternal is successfull it will pass the value up to MyUDFFunc. If MyUDFFuncInternal will re-throw an error, MyUDFFunc will just show #N/A, becasue it will exit from execution straightaway.

This could be the best solution: All the Excel UDF don't have any CentralErrorHandler at all, they just delegate the work at some internal fucntion, which wil full support the CentralErrorHandler approach.
This will allow us to reuse them in code easily.
I am still not completely convinced that this is the best approach though.

UPDATE:
After a quick chat with the author of Dailydose of excel, I came to realize that he does not use the Centra Error Handling either for Excel UDF. So my suggested soluiton is the best way I suggest to go.
For Excel UDF no central error Handling. We delegate their functon to some Internal function that implements the CentralErrorHandling Approach. We can set for this functin ErrorLog = False, to prevent to log 100,000 or more calls to failing functions

Matlab Excel Link from VBA configuration

To create macros that use Excel Link functions, you must first configure Excel to reference the functions from the Excel Link add-in. From the Visual Basic environment pull down the Insert menu and select Module.When the Module page opens, pull down the Tools menu and select References.... In the References window, check the box for EXCLLINK.XLA and click OK. You may have to use Browse to find the EXCLLINK.XLA file.

If you use MLGetMatrix in a macro subroutine, enter MatlabRequest on the line after MLGetMatrix. MatlabRequest initializes internal Excel Link variables and enables MLGetMatrix to function in a subroutine. For example,

Sub Get_RangeA()
  MLGetMatrix "A", "RangeA"
  MatlabRequest

End Sub

Do not include MatlabRequest in a macro function unless the function is called from a subroutine.

Excel how to show all value in a filtered table in vba

Hi,

If you want to show all value from a filter table, you need to check if there the table is filtered first, otherwise the Sheet1.ShowAllData will fail.
This is the solution

With  Sheet1
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With

How to call a parametric stored procedure from Microsoft Excel Query

Hi,
This is a very nice trick to call a stored procedure with parameters from excel.

If you type for example

exec model.GetPrices (?,?,?,?)

or

CALL model.GetPrices (?,?,?,?)




you will get this message

"Parameters are not allowed in queries that can't be displayed graphically"

while instead if you put the second Call within {} like that


{CALL model.GetPrices (?,?,?,?)}

it will work!!!

How to build a mail system framework in Excel using OOP: Example

I will explain how to use the framework to build a new mail object using the framework I have build

Let'us suppose we want to build a mail to trade a Total Return Swap to be send to a broker

 1) Interface Layer: We first create a ITrsMailDataProvider and define its interface

Option Explicit

Public Function RetrieveBody(irsId As String, fundId As String) As Parameters

'No Code Here
End Function

Public Function RetrieveHeader(irsId As String, fundId As String) As HeaderDTO
'No Code Here
End Function

 2) DAL:  We then create a TrsMailDataExcelProvider that implements teh ITrsMailDataProvider interface. You   can use some mock data for testing. If you are getting data from Access just go ahead and create your TRSMailDataAccessProvider that implements the same interface

2) Interface Layer: Then we need to Create a IProviderFactory. This will be the abstract factory that define the abstract methods that return our interface ITrsMailDataProvider or a  IIrsMailDataProvider. The idea is that the abstract providers will be produced by your abstract factory. The business layer will use only those interface and will know nothing about which concrete provider it is in use

   This class will look like
   Class IProviderFactory
       GetTrsMailDataProvider as ITrsMailDataProvider  //only method signature
       GetIrsMailDataProvider as  IIrsMailDataProvider  //only method signature
   End Class

3) DAL Layer: We then need a class that implement the IProviderFactory. For Example a ExcelProviderFactory will take care to create of the MailDataProvider that have Excel as source.

Option Explicit

Implements IProviderFactory

Private mTrsMailDataProvider As TrsMailDataExcelProvider
Private mIrsMailDataProvider As IrsMailDataExcelProvider

Private Function IProviderFactory_GetIrsMailDataProvider() As IIrsMailDataProvider
      If mIrsMailDataProvider Is Nothing Then
         Set mIrsMailDataProvider = New IrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      Set IProviderFactory_GetIrsMailDataProvider = mIrsMailDataProvider
      
End Function

Private Function IProviderFactory_GetTrsMailDataProvider() As ITrsMailDataProvider
     If mTrsMailDataProvider Is Nothing Then
         Set mTrsMailDataProvider = New TrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      
      Set IProviderFactory_GetTrsMailDataProvider = mTrsMailDataProvider
End Function

4) BLL: Finally we will have the client of the Abstract Factory, as for the Abstract factory Pattern. We will call this class the DataProvider (this can be a static class in c# ore moduel in VBA) and it will sit in the business Layer. This Class has the main objective to
use the IProviderFactory interface to produce our Trs and Irs MailDataProvider. The diffuclt part here is that this class should istantiate a Real istance of the abstract class IProviderFactory, but as we know we want the business Layer to be agnostic of the DAL. To get this result in C# we can use reflection. We can just write a method so that it loads a .dll specified in a config file as a string, and create an istance of a class from this dll.
This way to reference to the DAL Layer are necessary. In VB 6.0 this is can be done with CreateObject.

Option Explicit

Private mFactory As IProviderFactory

Public Function IrsMail() As IIrsMailDataProvider
  Set IrsMail = Factory.GetIrsMailDataProvider()
End Function

Public Function TrsMail() As ITrsMailDataProvider
  Set TrsMail = Factory.GetTrsMailDataProvider()
End Function

Private Function Factory() As IProviderFactory
'Insert Code here
'This cose should use a config file and reflection to choose
'which concrete factory instantiate. Createobject could be used in VB 6.0
'to make this class decouple with the DAL Layer
'We keep things ease here.

 If (mFactory Is Nothing) Then
     Set mFactory = New ExcelProviderFactory 'I really should be using CreateObject
                                             'to keep the class decoupled from the DAL
 End If
 Set Factory = mFactory
 
End Function

4) So fare we have done the following
    a) Business Layer -->  Interface Layer -->; DataBase Layer
    b) In the Interface Layer we have implemented the Model Provider Pattern and the Abstract Factory Pattern. Those class are just interface that define the methods that the business Layer can call to accesss the data
    c) In the DAL we have the real providers that just implement the provider interfaces and the real factory that just implement the factory methods.
    d) The transfer of the data between DAL and Business Layer is done using some object that are on the common layer such as MailDTO (data transfer object) or the Parameters collections (this is an utility object)
    e) The client of the abstract factory, the one we called DataProvider, is in the BLL and it stores in as a private field a reference to the IProviderFactory. This is where the magic happen: we can just switch the RealProvider withouth having to change any of the code that regards the BLL or Interface Layer.

How to create custom collection in VBA tricks

This post has been update here


You can find attached here the code that shows you how to create a strongly typed collection in VBA that has both a default Item property and that can be iterated with the For Each Loop.

As you will see from this blog post, some vba attributes are needed to be assigned to specific Collection properties, however these are not visible in the VBA IDE, but you can see them using notepad.




This solution was taken from a forum entry I found on the web
In VBA You can create both a defaul property and a default enumerator, but the process is a bit more manual.
If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property


It is important to note that the Attribute directive are just below the Property Signatures.
If they are not there, the code will not work.

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to (1) Make your Class, (2) SAVE, (3) Export the Class, (4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove") and then (5) Manually add the Attribute Lines as shown above, (6) Re-Import the edited Class.

As for one comment in this post another way is
1) Add those attributes in the VBA IDE. You will get  a syntax error. Ignore it
2) Click on the Class and Remove
3) Say Yes when you are asked to Export it
4) Import it again

Pay attention to not delete the class. Otherwise do: Export, Delete, Import.


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

Mail Merge with multiple To, CC, distribution lists and changing Subject

Link to Advance Mail Merge.doc
Link to Advanced Mail Merge DB.xls



In this two files you will find a way to extend the MS World mail merge to send email to
1)  Have multiple mails and distribution list in the To field
2)  Have multiple mails and distribution list in the CC field
3) Have a chaning subject

Also nothe the the merged field in the .doc document can be formatted
1) To format a date, toggle the field and add \@"DD MMMM, YYY"
2) To format a number add \##,##

In the attached document you will find an example.

UDFs for Excel in VSTO

VSTO does not support yet the introduction of UDFs, which is a kind of crazy!
However there are few work around.

VSTO "Excel Disigner Could Not Be Activated" error

After Installing VSTO on my PC, and trying our my first "Code Behind" project, I could not have access to the Excel designer. No controls were displayed in the Control toolbox and I could not any button or any type of control on the excel worksheet I was working on
The error message was a pretty scary one

"Excel Designer Could Not Be Activated"

After a bit of diggin on google I found this Post on the msdn forum, which helped me out sort the problem.

Office 2003, PIA Installation guidelines for .NET platform

In case you have problem getting the Primary Interop Assembly (PIA) working fine with your .NET Visual Studio platform, just check this link.

Installing the PIA for Office 2003

Excel Tip of the Day: How to work with Lists

A List is a set of ordered labels. For example the days of the week is an example of list

Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday


Excel has some built-in list:

Excel Tip of the Day: My favourite shortcuts

In this post I will publish my favourite shortcuts. Shortcuts are overlooked by most users, but I can guarantee you that if you start to learn them you will be as much as 30% faster while using Excel. You will not need to use the mouse anymore, which is very time saving.

Let's get started