Excel-lent VBA Range Macro Examples

You learn something new every day. Needed some range examples in Excel - couldn't find some appropriate examples on the internet ... so I created my own. Hope this is helpful to someone. :)

Sub RangeExamples()
Dim r As Range
Set r = Sheet1.Range("E5:G11")
' The range
Debug.Print "The Range: " & r.Address
' Last Column in the range
Debug.Print "Last Column: " & r.Columns(r.Columns.Count).EntireColumn.Address
' First Column in the range
Debug.Print "First Column: " & r.Columns(1).EntireColumn.Address
' First Row in the range
Debug.Print "First Row: " & r.Rows(1).EntireRow.Address
' Last Row in the range
Debug.Print "Last Row: " & r.Rows(r.Rows.Count).EntireRow.Address

' Top-right Cell in the range
Debug.Print "Top-right: " & r.Cells(, r.Columns.Count).Address
' Top-left Cell in the range
Debug.Print "Top-left: " & r.Cells(1).Address
' Bottom-right Cell in the range
Debug.Print "Bottom-right: " & r.Cells(r.Rows.Count, r.Columns.Count).Address
' Bottom-left Cell in the range
Debug.Print "Bottom-left: " & r.Cells(r.Rows.Count, 1).Address

' Intersection of range
Dim r2, r3, r4, r5 As Range
Set r2 = Sheet1.Range("$F:$F")
Set r3 = Intersect(r, r2)
Debug.Print "Intersection is: " & r3.Address

Set r4 = Sheet1.Range("$L:$L")
Set r5 = Union(r4, r3)

' the range between ranges
' between separate columns
Debug.Print Sheet1.Range(Sheet1.Cells(, r3.Column), Sheet1.Cells(, r4.Column)).EntireColumn.Address

' between separate rows
Dim r6, r7 As Range
Set r6 = Sheet1.Rows(10)
Set r7 = Sheet1.Rows(3)

Debug.Print Sheet1.Range(r7.Row & ":" & r6.Row).Address
End Sub

AJAX/Atlas UpdatePanel and UpdateProgress with GridView

There are a few basic tricks that I have discovered today.

1) When you click on a button to update an UpdatePanel, you can hide a GridView by adding code to the OnClientClickEvent (so only the UpdateProgress shows while the UpdatePanel is updating)

i.e.

OnClientClick="if(window.document.all('GridView')) window.document.all('GridView1').style.visibility = 'hidden';"

2) You can intercept the __doPostBack to add functionality by injecting Javascript code. The benefit of this is when you enable Paging on the GridView and the user clicks on another page. i.e.
inject the following code using Page.ClientScript.RegisterStartupScript(this.GetType(), "script" ...


// save the original function pointer of the .NET __doPostBack function
// in a global variable netPostBack
var netPostBack = __doPostBack
// replace __doPostBack with your own function
__doPostBack = GridViewHide;

function GridViewHide (eventTarget, eventArgument)
{ alert('test');
if(eventArgument.indexOf('Page') == 0) window.document.all(eventTarget).style.visibility = 'hidden';
// call base functionality

return netPostBack (eventTarget, eventArgument);
}

The effect is pretty cool.

Fun with Regular Expressions

I found a post on the Microsoft Forums that I had a bit of fun with.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2941690&SiteID=1

My response shows how to find a fixed length phrase in a document that start with certain words.

SQL2008 - Create Table Based on User-Defined Table Type

Table types are really neat. Mind you, one of the features I would love to see is the ability to create a table based on a table type (i.e. CREATE TABLE LIKE ).

Imagine this scenario, you define all your table types up front with extended property "TableType" i.e. (each Table Type will have whatever columns it needs - but will have extended property "TableType")

"TableType" 1 = Lookup table,

"TableType" 2 = Lookup table,

"TableType" 3 = Fact table,

"TableType" 4 = Dimension table

(Assuming a data warehouse here.)

... OR ...
Table Type 1 = Lookup table,

Table Type 2 = Data table,

Table Type 3 = History table

(Assuming an OLTP system)

You would be able to define the table types in one location, and the extended properties and indexes (optionally) could be propagated to the tables you create based on those types. I picture it almost like inheritance, you could create a table based on a table type - then extend the table type to include additional properties.

Advantages of this would be that you could apply manageability rules (similar to what VS2005 has and standard sets of unit tests to your tables that are created based on such types). Until such a time as this becomes reality, here are some workarounds. (Of course, you will have to do some work to propagate extended properties from tables/table types that you inherit from.)

1. Create Table based on another table


SELECT top 0 * INTO mynewtable from mytable

2. Create Temporary Table based on another table


SELECT top 0 * INTO #mynewtable from mytable

3. Create Table based on table type


DECLARE @mytable MyTableType
SELECT TOP 0 * INTO mynewtable FROM @mytable

DataSets, Xml Schemas, and Excel - Oh My!

I have created another article on code project detailing the transformation power of the dataset. It isn't just for getting data from the database. You can find it here.

Xml String Browser

I created my first web article. The title was "XML String Browser (just like Internet Explorer) using WebBrowser Control". You can find this article on Code Project by clicking on the following link. It was great fun to do and I hope to write a few more articles. I was motivated to do this because the .NET Web Browser control does not support pretty rendering of XML strings or XML Documents (unless you are viewing XML files).

Execute cmd/bat from VS2005

I haven't blogged for a while. Unfortunately, I have been really busy. Here is a helpful tip that I discovered a while ago .... It is an incredibly handy one .

1. First, you need to go to Tools->External Tools in the Visual Studio IDE.

2. Then, add a new one with the settings depicted in the picture below.

I would be interested to know if people find this one as useful as I have. I have been using it for a number of purposes (including executing various Nant scripts).

Also, An important note - you have to Customize the context menu before you can use the Execute Command External Tool. This is done by:

1. Clicking on Tools->Customize and select Context Menu.

2. Drag and drop the External Tool to the Project Items -> Project Item context menu.