Please Note that I have changed address
Go to
Baking Ways / Productive Bytes



Search This Blog

Pages

Sunday, July 4, 2010

Example of VBA code formatting using manoli.net

if you go to this website http://www.manoli.net/csharpformat/ you will be able in a breeze to parse your VBA/C# code in HTML format. You can see at the bottom of this blog post an example.
To make it work in blogspot, you just need to go in Design -> Edit HTML and just after


<b:skin><![CDATA[/*

insert the following code. You can find http://www.manoli.net/csharpformat/format.aspx at the bottom of the page the link to the .css style sheet.

/* CSharp VB Formatting */

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, "Courier New", Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

This is how the VBA code looks like formatte using the manoli.net application.

Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
Dim DBQ As String
Dim DefaultDir As String
Dim Connection As String

'Store the original connectin before overwriting
mOldConnection = mQry.Connection

'Build a DSN connectionless connection using OLEDB
DBQ = ThisWorkbook.FullName
DefaultDir = ThisWorkbook.Path
Connection = "ODBC;DBQ=" & DBQ & ";"
Connection = Connection & "DefaultDir=" & DefaultDir & ";"

'For Excel 2003
'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2007
Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'For Excel 2003 Just change the Connecton for listed query
'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
' mQry.Connection = Connection
'End If

'For Excel 2007 Just change the Connecton for listed query
If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
mQry.Connection = Connection
End If


End Sub

No comments:

Post a Comment