Chennai VBA

Two ways of Hiding Excel Worksheet

noreply@blogger.com (nkarthick) — Fri, 29 May 2009 12:58:00 +0000

Two ways of Hiding Worksheet –

There are two ways to hide the sheet in Excel workbook
1. Hidden –
Most of us know this way - that is by right click on the sheet tab and click Hide as shown below.

But we can unhide the hidden sheet(s) by clicking “Unhide” option, it will show the below user form there you have to select the sheets to unhide.

2. Very Hidden –
This is option is unfamiliar to most of excel users.
To accomplish this you have to do the following steps -
· First press Alt + F11 key, it will display the Visual Basic Editor
· Press Ctrl + R In the Project window double click on “Microsoft Excel Objects” and select the sheet to hide.

After selecting the sheet press F4 it will display the Property box
Change/Set the Visible property of the selected sheet to xlSheetVeryHidden.

To unhide the sheet set the selected sheet Visible property to xlSheetVisible.
Note: You cannot unhide (VeryHideen) sheet in the sheet tab itself.

Hope this tips are useful.

Ignore Task Bar Shaking While Opening a WorkBook in VBA

noreply@blogger.com (nkarthick) — Mon, 25 May 2009 10:08:00 +0000

If you are a VBA programmer then you could come across the following problem.

If your macro opens and closes the workbooks multiple times, then you can see annoying behavior happens in the Taskbar (i.e) like shaking action in Task bar take place.

In order to ignore this annoying behavior (Shaking) in windows task bar, you have to include the following line before the macro process starts or before opening a workbook.
Application.ShowWindowsInTaskbar = false

At the end of macro process you can set this value to true (original state).
Application.ShowWindowsInTaskbar = True

Hope this tips will be useful.

Stop Automatic Calculation of Excel Formulas

noreply@blogger.com (nkarthick) — Mon, 30 Mar 2009 08:10:00 +0000

If you want to stop automatic calculation of formulas in Excel file. You have to do the following steps to accompolish it.

In Excel 2007 –
1. Click the Excel Options shown in below screen shot.

2. Navigate to ‘Formulas’ group, change the workbook calculation to Manual and uncheck the Recalculate Workbook before saving pointed out in the below screen shot.

In Excel 2003 –

1. Go to Tools --> Options

2. Go to Calculations tab Select ‘Manual’ and Uncheck Recalulate Before Save options then click OK.

Note:
After Settings the above options in your excel file.
1.If you want to do formulas to recalculate in the workbook then press ‘F9’ key. It will recalulate the formulas in the work book.
2. If you want to do formulas to recalculate in the specific excel sheet then press ‘Shift + F9’ key. It will recalulate the formulas in the current worksheet.

Hope this post will be useful.

MS Access Database Limitations

noreply@blogger.com (nkarthick) — Fri, 15 Feb 2008 12:35:00 +0000

MS Access Database Limitations:

o Access database can be up to 2 GB in size.
o Access database doesn’t support more than 255 fields in a table. Only 255 fields are allowed in a table.
o Only 32 Indexes are allowed per table. (Indexes:- for retrieving or filtering the records faster)
o Only 10 fields are allowed in multiple field indexes and sum of fields length should not exceed 255 Bytes
o No limitation on the number of records in a table.
o A memo field can store up to 1 GB of characters.
o It can’t display a Memo larger than 64 KB in a form or datasheet.
o An ActiveX objects can be up to 2 GB in size.
o A row of the table should be lesser than 4 KB of size (exception for ActiveX and memo fields).
Above limitation is appliable to MS Access 2003 database.
I have gathered this information from various sources, hope this will be knowledgable for you.

Indexes in MS Access

noreply@blogger.com (nkarthick) — Wed, 13 Feb 2008 09:47:00 +0000

Hello Everybody,

This article is just to give an idea about indexes in MS Access database.

Indexes in MS Access

Why we need Indexing?

Indexes in any database (MS Access, Sql server, Oracle) are most important thing to deal with. Especially more the data in the tables of our database, it’s hard for DB to search or refine (filter) the data’s. To overcome this there is some thing called Indexing in the database. Indexes of fields in the table, will able to find data’s in those fields very quickly. Index can drastically increase the performance of DB application. Indexing here is similar to indexing of book contents.

How to create an Index in MS Access?

For creating an index on a field, first you need to open a table (not linked table) in design view. Select the field that you want to create an index. Then go to “General” tab, there you will find the “Indexed” property. Select the property value as “Yes (Duplicates OK)” or “Yes (No Duplicates)”. “No” value means no indexing. By changing the value from “No” to any one of the “Yes” value is completion of index creation.
Primary key automatically indexed to “Yes (No Duplicates)” and for Primary key the index value must be “Yes (No Duplicates)”. For Foreign key any one of the “Yes” value.

Creating Index on Multiple Fields :

In some scenarios, we often use few criteria’s on multiple fields of a table with large data. It’s better to set index for the multiple fields to improve the performance here.
To do this, open a table in design view, click “View” in menu bar and then click “Indexes”, it will open an “Indexes” form.
In “Indexes” form, in the first line give the index name, select the field, then select the Sort order. After that in the next line select the field, and then select the Sort order, you can set up to 10 fields for the index on multiple fields.
Below screen shot shows Single and multiple Index.

Auto Index in MS Access:

We can see some strange list “ID; key; code; num” on “Tables/Queries” tab.
You can find this list by navigating
Click “Tools” in menu bar --> click on “Options” --> “Tables/Queries” tab --> Auto Index on Import/Create.

List value here indicates, if any field name starts or ends with this “ID; key; code; num” values, then Access will automatically create an index for that field. Here you can add your own list. If you don’t want you can delete this list.

Limitation of Indexes in MS Access:

Up to 10 fields are allowed for setting an index on multiple fields.

Total number of indexes on a table is 32

More number of indexes will slow down the speed of updates on your records.

Hope this article is helpful for you. Please leave your comments.

Thanks & regards,

Karthick

Clock in Scatter Chart

noreply@blogger.com (nkarthick) — Wed, 13 Feb 2008 05:12:00 +0000

Hello Everybody,

Clock (without using Timer) created in Scatter chart (Excel Chart), yeah its true. I got inspired from the work “Fireworks in Scatter chart” done by Excel MVP “Andy Pope” (Chart Genius).

I have used the same logic to create clock in scatter chart, that I was used in “JAVA” language Clock in Java Applet”. Here I have used sine and cosine function to draw the outline for the chart, which is based on radius value in the sheet “Scatter Clock” (in the Attached file).

Please download the file “Download Scatter Chart” here.
Based on X and Y series, i have acheived the thing here.

If any queries, please free to mail me or add comment to this post.

Thanks & Regards,
Karthick

My First VBA tips

noreply@blogger.com (nkarthick) — Fri, 08 Feb 2008 06:36:00 +0000

Hello VBA developer,

From now onwards, am going to post topics (tips) related to VBA (Access & Excel).

Before that, am greatfully thankful to my Boss Naresh Nichani (Access MVP) and Virtual Boss Brian Reilly(Power Point MVP), who are giving more oppotunities to learn, develope and design the stuffs in VBA (Excel and Access).

Here is my first Tip in Excel.

Custom Formats

Without using the conditional formatting, we can customize the format (Font Color), based on cell value (applicable to number)

[Red]"Export";[Blue]"Import";[Black]"Balance"

First Copy the above line (in brown color) and then right click any one of the cell in excel sheet (where do u need to format),

Select "Format Cells" --> "Number" tab --> Click "Custom" --> paste it in "Type " text box.

For Eg:

Lets assume, you assigned the above custom format in cell "A2",

Then if Cell value is + ve then Export text will be displayed
is - ve then Import text will be displayed
is 0 then Balance text will be displayed

Hope you understand that, If any queries or doubts, please let me know, by commenting this post.

Thanks & Regards,
Karthick