Hi,
Please note that I have split my old blog argurosblog in two parts.
I have noticed that looking at some computer programming code with the next post being about a pasta or rice dish was not working very well.
This is my blog about computing
Arguros Computing
This is my blog about cooking
Arguros Cooking
I left this blog on the web because google has already indexed it and you might not have found anymore a live link in case you searched by blog using the search engine.
I hope you will enjoy the change
Saturday, January 7, 2012
Wednesday, January 4, 2012
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.
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.
Friday, November 18, 2011
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
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
Thursday, November 10, 2011
Formatting code for HTML display
Hi,
This is really a great link
http://www.manoli.net/csharpformat/
to format c# or VB.NET/VBA/VB code in HTML 4 format to display on the web
This is really a great link
http://www.manoli.net/csharpformat/
to format c# or VB.NET/VBA/VB code in HTML 4 format to display on the web
How to Create a task or appointment using VBA Code in outlook
Hi,
I have changed the original code at this link to show you how to create either a task or an appointment from an e-mail item.
This is very handy when you want quickly create task/appointment without wasting your time to attach the original mail.
The only problem is that the item is attached at the end of the file. This is due to a bug in Outlook 2008/2010 for which the postion item does not work.
Just copy and paste the code below into an outlook module to make it work.
I have changed the original code at this link to show you how to create either a task or an appointment from an e-mail item.
This is very handy when you want quickly create task/appointment without wasting your time to attach the original mail.
The only problem is that the item is attached at the end of the file. This is due to a bug in Outlook 2008/2010 for which the postion item does not work.
Just copy and paste the code below into an outlook module to make it work.
Sub CreateTaskFromMail()
Const mailItem_c As String = "MailItem"
Dim OE As Outlook.Explorer
Dim MI As Outlook.MailItem
Dim AI As Outlook.AppointmentItem
Dim TI As Outlook.TaskItem
Set OE = Application.ActiveExplorer
'Abort sub if no item selected:
If OE.Selection.Count < 1 Then
MsgBox "Please select an already saved message before" & vbCrLf & _
"attempting to create a task" & vbCrLf & _
"with this button ...", vbInformation, "No message selected ..."
Exit Sub
'Abort sub if item selected is not a MailItem.
ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
Exit Sub
End If
Set MI = OE.Selection(1)
Set TI = Application.CreateItem(olTaskItem)
With TI
.Subject = MI.Subject
.Body = .Body & vbCrLf & vbCrLf
.Body = .Body & "-----Original Message-----" & vbCrLf
.Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
.Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
.Body = .Body & "To: " & MI.To & vbCrLf
.Body = .Body & "Cc: " & MI.CC & vbCrLf
.Body = .Body & "Subject: " & MI.Subject & vbCrLf
.Body = .Body & vbCrLf
.Body = .Body & MI.Body
'.StartDate = Date
'.DueDate = Date + 1
'.ReminderTime = .DueDate & " 10:00"
Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
Case vbYes
TI.Body = "View Original Mail attacched at the bottom" & vbCrLf & TI.Body
TI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
TI.Display
Case vbNo
TI.Display
Case vbCancel
Exit Sub
End Select
End With
End Sub
Sub CreateAppointmentFromMail()
Const mailItem_c As String = "MailItem"
Dim OE As Outlook.Explorer
Dim MI As Outlook.MailItem
Dim AI As Outlook.AppointmentItem
Dim TI As Outlook.TaskItem
Set OE = Application.ActiveExplorer
'Abort sub if no item selected:
If OE.Selection.Count < 1 Then
MsgBox "Please select an already saved message before" & vbCrLf & _
"attempting to create an appointment" & vbCrLf & _
"with this button ...", vbInformation, "No message selected ..."
Exit Sub
'Abort sub if item selected is not a MailItem.
ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
Exit Sub
End If
Set MI = OE.Selection(1)
Set AI = Outlook.CreateItem(olAppointmentItem)
With AI
.Subject = MI.Subject
.Body = .Body & vbCrLf & vbCrLf
.Body = .Body & "-----Original Message-----" & vbCrLf
.Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
.Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
.Body = .Body & "To: " & MI.To & vbCrLf
.Body = .Body & "Cc: " & MI.CC & vbCrLf
.Body = .Body & "Subject: " & MI.Subject & vbCrLf
.Body = .Body & vbCrLf
.Body = .Body & MI.Body
'.StartDate = Date
'.DueDate = Date + 1
'.ReminderTime = .DueDate & " 10:00"
Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
Case vbYes
AI.Body = "View Original Mail attacched at the bottom" & vbCrLf & AI.Body
AI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
AI.Display
Case vbNo
AI.Display
Case vbCancel
Exit Sub
End Select
End With
End Sub
Sub NewTaskOrAppoitmentFromMail()
Const mailItem_c As String = "MailItem"
Dim OE As Outlook.Explorer
Dim MI As Outlook.MailItem
Dim AI As Outlook.AppointmentItem
Dim TI As Outlook.TaskItem
Set OE = Application.ActiveExplorer
'Abort sub if no item selected:
If OE.Selection.Count < 1 Then
MsgBox "Please select an already saved message before" & vbCrLf & _
"attempting to create an appointment or task" & vbCrLf & _
"with this button ...", vbInformation, "No message selected ..."
Exit Sub
'Abort sub if item selected is not a MailItem.
ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
Exit Sub
End If
Set MI = OE.Selection(1)
'Beep
Select Case MsgBox("Do you want to create a Task?" & vbLf & _
"To Add Task (Yes) / To Add Appointment (No) / To Quit (Cancel)" & _
vbCrLf, vbYesNoCancel + vbQuestion, "Create a task or appointment ...")
Case vbNo 'If No, create appointment
Set AI = Outlook.CreateItem(olAppointmentItem)
With AI
.Subject = MI.Subject
.Body = "View Original Mail attacched at the bottom"
.Body = .Body & vbCrLf & vbCrLf
.Body = .Body & "-----Original Message-----" & vbCrLf
.Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
.Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
.Body = .Body & "To: " & MI.To & vbCrLf
.Body = .Body & "Cc: " & MI.CC & vbCrLf
.Body = .Body & "Subject: " & MI.Subject & vbCrLf
.Body = .Body & vbCrLf
.Body = .Body & MI.Body
.Attachments.Add MI, , 1
.Display
End With
Case vbYes
'If Yes, create task with no due or start date
Set TI = Application.CreateItem(olTaskItem)
With TI
.Subject = MI.Subject
.Body = "View Original Mail attacched at the bottom"
.Body = .Body & vbCrLf & vbCrLf
.Body = .Body & "-----Original Message-----" & vbCrLf
.Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
.Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
.Body = .Body & "To: " & MI.To & vbCrLf
.Body = .Body & "Cc: " & MI.CC & vbCrLf
.Body = .Body & "Subject: " & MI.Subject & vbCrLf
.Body = .Body & vbCrLf
.Body = .Body & MI.Body
.Attachments.Add MI, , 1
'.StartDate = Date
'.DueDate = Date + 1
'.ReminderTime = .DueDate & " 10:00"
'.Save
.Display
End With
'Case vbCancel
' Exit Sub
End Select
End Sub
Wednesday, October 5, 2011
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!!!
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!!!
Tuesday, November 30, 2010
How to use 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
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.
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.
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.
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 Explicit2) 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
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) 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.
Subscribe to:
Posts (Atom)