Category Archives: Microsoft

Excel VBA Tips & Tricks

[adsense slot=”5402167102″ width=”468″]

Am consolidating here some commonly required Excel VBA snippets in an FAQ form.

How to find the last row in a Worksheet?

How to clear the clipboard after a copy/paste operation?

Why am I not able to copy a range?

If I get a Column number, how can I get the Column alphabet?

Is there something else you would like me to add here? Just leave a comment.

Excel Default Colors for VBA

This is a handy reference list for the default Workbook.Colors list in excel.

excel-colors-vba

In case you are interested in generating the same list, here is the code. ‘Colors’ here refer to the sheet name and you may have to replace it with a relevant value (say, Sheet1).

And wait, SplitColors is not a built in function. This is a small piece of code that takes in a Long color and returns 3 hex strings. Basically, a long to hex converter for a color. That one follows.

This is not a tutorial on how to use ColorIndex to set colors using Excel VBA. For details on that, take a look at this.

IIS7 – Integrated Windows Authentication

[adsense slot=”5402167102″ width=”468″]

This whole reduce-the-surface-area business is making life for developers a bit hard. Else, why would something as simple as unchecking a box and checking another become so complex? In Good old IIS6 you had to just disable anonyous access and enable Integrated Windows or Basic Authentication.

If you are using IIS7 with Windows Server 2008 (guess it is same with Vista also), here is how you can enable these authentication types.

Go to Server Manager > Roles > IIS Server
Right click on IIS Server and choose Add Role Services
iis-add-authentication-roles

Enable the required roles
iis-enable-integrated-basic-auth

Restart IIS7 and go to the Authentication option for the website or server
Now you can see the new options to enable the desired auth modes.
iis-inetmgr-authentication-options

Enable the required auth types here.

Saving a table change on SQL Server 2008

SQL Server 2008 Management Studio does not allow “saving” a table change, when it involves recreation. This is a good feture in general terms but may not be desired in a development environment. Here is how we can ask the designer to allow such changes.

Go to Tools > Options
And in the dialog below that opens, go to Designers > Table and Database Designers

Save Table Changes in SQL 2008

Generate Table Schema

This is a simple (but often useful) tool to generate the xml schema of a database table. The tool supports only SQL Server over Windows Authentication. The source code here can be easily extended for other uses.

SELECT column values into a concatenated string (SQL)

Often there is a requirement to get column values in a table to a concatenated string. Say, we have e-mail ID as one column on a person table and want a comma separated list of all (or a subset) of e-mail ID’s.

Here is one dirty little piece of code to do just that! :)

The above query returns a concatenated string of values in EmailID column, separated by semi-colon.