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 create custom collection in VBA tricks

This solution was taken from a forum entry I found on the web



You can, 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

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.


(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.



UDFs as Automation Add-in

This technique is good if you want to make the function available for every workbook in the Excel Application. It is based on the development of an "Excel Automation Add-in".
This consist of developing a kind of C# COM exposed class.
It was a good idea that the author included in the example the GUID identifiers to avoid registry bloating, however I don't know why he did not defined an Internface and let the class implement it, which is the standard way to develop COM exposed class in .NET

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx



UDFs in Code behind files
This technique is good when you want to define workbook level UDFs. It is based on a COM exposed class and some VBA wrapper that you need to write at workbook level. The only problem I had was to the the Exel.Application.Run command work fine. My mistake was due to the fact that I was adding the VBA code the the Excel file that shows up after building and running the program. I did not realized that each time I run, the original .xls file used by the solution was overwriting the one I was editing.

http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx



Consideration
Given the fact that VSTO does not suggest to save aa .XLA, a .XLS files based on VSTO solution, it is still quite hard to deploy an add-in that can use and excel file as data storage.
For example I developed some years ago an Excel Add-in .XLA to bootstrap the Interest Rate Swap Curve in US and Euro area, and I was using a spreadsheet in the .xls file to store data. The add-in makes available to every workbook a set of functions to compute some fixed income analytics : PV, duration, factor analysis...
Of course if this is the idea, the code-behind pattern VSTO solution is ruled out (it cannot make available function to everywork book that runs in an Excel application).
 We are left with the Automation add-in solution, which is basically a COM Exposed Class. However, the automation add-in does not have any worksheet to store the data. To be honest one could also develop a COM based add-in (which I do not cover here), but I think it would not sort the problem out.

If anybody has any idea on how to develop .xla kind of add-in, ie a VSTO solution that allows to

1) make UDFs available at application level
2) use an Excel file to store some data

as an .xla solution does, please let me know.

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.



Among the different suggestions the one that worked for me was to reinstall the VSTO run time.

If you are developing solution for Excel 2003 and VS2008 this is the one you need


Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)


or the latest version (up to date of publishing)


Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)(build 8.0.50727.940)




If you are developing solution for Excel 2007 and VS2008 this is the other link

Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0 Runtime) SP1 (x86)

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





For example I kept having a "tlbimp.exe" generated assembly (not the PIA) when adding a COM Reference to Microsoft.Office.Interop.Excel, from the "Add Reference", COM tab window.

This means the the .dll path of this reference was pointing to a local dll (local to the project), rather than the one registered in the GAC.


I sorted the problem just changing the "Copy Local" property to false, removing the reference and adding it again.

However, you could also have had some problem with the registration of the PIA in the GAC (Global Assembly Cache). The above link help you sort this kind of problems.

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:

Let's suppose that you need to list the day of the week in your spreadsheet. Since this is a built-in list, a quick way to achieve this is do to as follow
Just type the fist day in column A as in the figure

Then grab down the handle in the bottom-right corner

This is what you will get. Pretty straightforward isn't it?

The most interesting part is that Excel gives you the options to create custom Lists.
Let see how to do it
In Office 2007 you need to

• Click the Microsoft Office Button, and then Excel Options.

• Click Popular, and then under Top options for working with Excel, click Edit Custom Lists.

• In the Custom lists box, click NEW LIST, and then type the entries in the List entries box, starting with the first entry.
• Then click OK twice

Once we have build the list, we can use it as if it was a built-in one.
Type Blue in A3, grab down the fill handle and you will have the custom colour list filled in your worksheet

As a side note, if the list is lengthy, you can also import the list from cells.

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




These are the most basic ones

Ctrl  +  C                      Copy
Ctrl  +  V                      Paste
Ctrl  +  X                      Cut
Ctrl  +  Z                      Undo
Ctrl  +  R                      Redo

Now let's see some others for copying and pasting

Alt + E + S + V            Copy Values
Alt + E + S + F            Copy  Formulas
Alt + E + S + M           Multiply
Alt + E + S + D            Add
Alt + E + S + E            Transpose



Quick Cell Formatting 

Alt + O + C                 Autofit Column Width
Ctrl + Shift + !             Change Format: two decimal place, thousands separator
Ctrl + Shift + ~            Change Format: General format


Selection ShortCuts


Alt + ;                         Display only visible cells
Shift + Space Bar        Select Rows
Ctrl  + Space Bar        Select Columns
Ctrl  + A                     Select Current Region
Ctrl  + A + A              Select Entire Worksheet
Ctrl  + /                       Select current array formula


While Editing a Formula

Ctrl + A                      Display the formula input box
Ctrl + Shift + A           Print in the formula bar the formula with arguments (not so useful in Excel 2010)

Filtering

Alt + D + F                Dispaly Filter



Grouping
 
Shift + Alt + Righ Arrow     Group
Shift + Alt + Left Arrow      Ungroup



Please feel free to add your most used shortcuts in the comments.