VBA Ads

Friday, May 29, 2009

Two ways of Hiding Excel Worksheet

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.

Monday, May 25, 2009

Ignore Task Bar Shaking While Opening a WorkBook in VBA

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.

Monday, March 30, 2009

Stop Automatic Calculation of Excel Formulas

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.

Friday, February 15, 2008

MS Access Database Limitations

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.