Sub reverse_sheet_order()
Dim TotalSheets As Integer
TotalSheets = ActiveWorkbook.Sheets.Count
For i = 1 To TotalSheets
Sheets(TotalSheets).Move before:=Sheets(i)
Next
End Sub
This macro is used to reverse the order of sheets in Active workbook. Rerun the macro to get the original order of sheets.
Friday, December 31, 2010
Thursday, December 30, 2010
SHRINK REDUCE EXCEL FILE SIZE
If minor or little changes in your excel workbook bloated its size to 200% and above then it's a serious issue but not worry issue as it has solution.
Understand the problem: First understand the difference between 'Excel Default Last Cell' and 'Actual Last Cell'. When you do 'Ctrl+End' to find last cell, you'll reach to 'Excel Default Last Cell' which may be the 'Actual Last Cell' or beyond the 'Actual Last Cell'. The more beyond 'Excel Default Last Cell' would be from 'Actual Last Cell', the more unnecessary size of excel workbook would it be having.
Solution: Delete all rows and columns beyond the 'Actual Last Cell' in every worksheet. If there are too many worksheets and large sets of data, you can use the VBA macro mentioned on eXceLiTems.com.
Read full article on reducing excel workbook size using VBA
Understand the problem: First understand the difference between 'Excel Default Last Cell' and 'Actual Last Cell'. When you do 'Ctrl+End' to find last cell, you'll reach to 'Excel Default Last Cell' which may be the 'Actual Last Cell' or beyond the 'Actual Last Cell'. The more beyond 'Excel Default Last Cell' would be from 'Actual Last Cell', the more unnecessary size of excel workbook would it be having.
Solution: Delete all rows and columns beyond the 'Actual Last Cell' in every worksheet. If there are too many worksheets and large sets of data, you can use the VBA macro mentioned on eXceLiTems.com.
Read full article on reducing excel workbook size using VBA
Wednesday, December 29, 2010
3 Things to remember before Excel VBA Job Interview
Like giving interview is a skill, taking interview is another great skill. Ashish Jain have taken many interviews in the past and given more than he has taken and here he is sharing the content from his experience. Your interview experience may be totally different than he has mentioned here but you must be aware of it and should not miss some common things mentioned here. These are his personal views and have nothing to do any of his past or present employers.
If a good interviewer will conduct an interview, he will not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette. You must remember these 3 things are interviewed in a good Excel and VBA job interview for Analyst position:
1. General Programming Attitude
2. Knowledge of Excel Object Model
3. Excel Knowledge (Data Validation, Subtotal, Formulas etc.)
Read the full article here written by Ashish Jain on eXceLiTems.com
If a good interviewer will conduct an interview, he will not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette. You must remember these 3 things are interviewed in a good Excel and VBA job interview for Analyst position:
1. General Programming Attitude
2. Knowledge of Excel Object Model
3. Excel Knowledge (Data Validation, Subtotal, Formulas etc.)
Read the full article here written by Ashish Jain on eXceLiTems.com
Tuesday, December 28, 2010
50 Excel VBA Oral Interview Questions
These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA.
If you're looking for a job in MIS/Automation/Dashboard creation etc. as a Business Analyst, Senior Analyst, Associate Analyst, etc involving MS Excel, MS Access, VBA, SQL, Cognos, ASP.NET etc then Click here on 'Excel VBA Job Postings'
Click here to read all the 50 questions and answers.
ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.
ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.
i) The Boolean data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you're working over 32767 rows use Long as data type.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.
vii) The String data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.
i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.
i) Subroutines never return a value but functions does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.
All the controls in the ToolBox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events.
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming. The TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the TextBox
* The Click event fires when the TextBox control is clicked.
* The MouseMove event fires when the mouse is moved over the TextBox
Click here to read all the 50 questions and answers.
If you're looking for a job in MIS/Automation/Dashboard creation etc. as a Business Analyst, Senior Analyst, Associate Analyst, etc involving MS Excel, MS Access, VBA, SQL, Cognos, ASP.NET etc then Click here on 'Excel VBA Job Postings'
Ques 01. What is the difference between ByVal and ByRef and which is default ?
Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure.ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.
ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.
Ques 02. What is the meaning of Option Explicit and Option Base?
Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0.Ques 03. What are various data type and their size?
Solution:i) The Boolean data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you're working over 32767 rows use Long as data type.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.
vii) The String data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.
Ques 04. Difference between ActiveWorkbook and ThisWorkbook.
Solution: ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook.Ques 05. Code to find a Last used Row in a column or Last used column of a Row.
Solution: Last Row in a column can be find using End(xlUp) and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.Ques 06. Difference between ActiveX and Form Controls.
Solution: i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.
Ques 07. What is the difference b/w Functions and Subroutines?
Solution: i) Subroutines never return a value but functions does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.
Ques 08. How to debug a VBA code?
Solution: Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.Ques 09. Draw basic Excel Object Model.
Solution: Application --> Workbooks --> Worksheets --> Range / ChartQues 10. What are properties, methods, events and objects?
Solution: For details click here --> http://msdn.microsoft.com/en-us/library/ms172576%28VS.80%29.aspxAll the controls in the ToolBox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events.
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming. The TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the TextBox
* The Click event fires when the TextBox control is clicked.
* The MouseMove event fires when the mouse is moved over the TextBox
Monday, December 27, 2010
Excel 2003 Style Menu in Excel 2007/2010
I really don't want to write this article as I'm in love with new interface of Excel 2007 and wants my blog readers and every excel user to understand the new Ribbon Menu structure than to go back and use Excel 2003 style menu in Excel 2007. However I don't want to write but I'm not writing this article on a gun-point. Lolzzzzzzzz... The objective is to help those users who want to remain stick with legacy programs just because they don't understand new and improved systems. So, that they can move to new systems, explore it, gradually learn it and hence praise it. Second reason is that such utilities are available @ cost of $20-$50 over the internet which my dear readers and excel users do not need to pay anyway.
In my total experience with Excel and Excel users, I encountered many peoples (major of them Senior Managers and Managers) who resist Excel 2007 primarily due to its new ribbon structure. They were habitual to 2003 and before menu style and found it hard to locate their favorite commands. Anyway, let's come to technical part now.
 |
| Excel 2003 Style Menu in Excel 2007 |
In my total experience with Excel and Excel users, I encountered many peoples (major of them Senior Managers and Managers) who resist Excel 2007 primarily due to its new ribbon structure. They were habitual to 2003 and before menu style and found it hard to locate their favorite commands. Anyway, let's come to technical part now.
Click here to create Excel 2003 Style Menu in Excel 2007/2010
Sunday, December 26, 2010
17 ways to Optimize VBA Code for FASTER Macros
Here is a summary of the article:
1. Analyze the Logic
2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'
4. Disable Events
5. Hide Page breaks
6. Use 'WITH' statement
7. Use vbNullString instead of ""
8. Release memory of Object variables
9. Reduce the number of lines using colon(:)
10. Prefer constants
11. Avoid Unnecessary Copy and Paste
12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.
14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'
16. Avoid using Variant
17. Use Worksheet Functions wherever applicable
Do read full article to understand the logic behind them.
1. Analyze the Logic
2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'
4. Disable Events
5. Hide Page breaks
6. Use 'WITH' statement
7. Use vbNullString instead of ""
8. Release memory of Object variables
9. Reduce the number of lines using colon(:)
10. Prefer constants
11. Avoid Unnecessary Copy and Paste
12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.
14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'
16. Avoid using Variant
17. Use Worksheet Functions wherever applicable
Do read full article to understand the logic behind them.
Sunday, December 12, 2010
Import a PDF in Excel
Here is the Macro to import text from a PDF file to your excel sheet. Make sure you have Adobe Reader 9.0 installed on your PC.
Sub BackToA1()
Range("A1").Select
End Sub
Sub GetPDFnow()
Dim varRetVal As Variant, strFullyPathedFileName As String, strDoIt As String
'Add a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
'Name it
ActiveSheet.Name = "Input01"
'Back to "A1"
Range("A1").Activate
'HERE YOU DEFINE THE FULLY PATHED PDF FILE
strFullyPathedFileName = "C:\Documents and Settings\ayujain1\Desktop\Excel_Tutorials\Ayush ebooks\14 secret shortcuts of Excel.pdf"
'HERE YOU SET UP THE SHELL COMMAND
strDoIt = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFullyPathedFileName
'The Shell command
varRetVal = Shell(strDoIt, 1)
'Clear CutCopyMode
Application.CutCopyMode = False
AppActivate varRetVal
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'IN ACROBAT :
'SELECT ALL
SendKeys "^a"
'COPY
SendKeys "^c"
'EXIT (Close & Exit)
SendKeys "^q"
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'Paste
ActiveSheet.Paste
'Go back to cell A1
Call BackToA1
End Sub
Sub BackToA1()
Range("A1").Select
End Sub
Sub GetPDFnow()
Dim varRetVal As Variant, strFullyPathedFileName As String, strDoIt As String
'Add a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
'Name it
ActiveSheet.Name = "Input01"
'Back to "A1"
Range("A1").Activate
'HERE YOU DEFINE THE FULLY PATHED PDF FILE
strFullyPathedFileName = "C:\Documents and Settings\ayujain1\Desktop\Excel_Tutorials\Ayush ebooks\14 secret shortcuts of Excel.pdf"
'HERE YOU SET UP THE SHELL COMMAND
strDoIt = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFullyPathedFileName
'The Shell command
varRetVal = Shell(strDoIt, 1)
'Clear CutCopyMode
Application.CutCopyMode = False
AppActivate varRetVal
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'IN ACROBAT :
'SELECT ALL
SendKeys "^a"
'COPY
SendKeys "^c"
'EXIT (Close & Exit)
SendKeys "^q"
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'Paste
ActiveSheet.Paste
'Go back to cell A1
Call BackToA1
End Sub
Subscribe to:
Posts (Atom)