1. Make Use of the Macro Recorder
Microsoft Excel has a built in Macro recording feature, which give us a great way to learn about new objects and how to use them. I use the macro recorder whenever required to get the objects code in the part of my codes. The macro recorder does not always create the complete code which is required to get the best output. But is gives you the idea on how to write the code to perform specific set of actions. You have an option to record the code and then edit to get it work for the best output.
2. Make use of the immediate window
Excel VBA editor has a feature of the immediate window, which can be of great help while coding. You can write any VBA statements and get the resluts immediately on the immediate window. For example, say cell A1 = How ? and Cell A2 = by pdaphal.blogspot.com way
Select cell A1 and type activecell.value and press enter in the immediate window it will return you the value in cell A1. see the sample code and output below:
?activecell.Value
How ?
?activecell.Offset(1).Value
by pdphal.blogspot.com way
3. Make use of Debug.Print
Debug.print is the best way to find out the bugs (errors) in your code. Our code sometimes does not work the way we want to, due to variable situations we receive in the data processing. By using the debug.print code, you can see the output in the immediate window, as your code / program is running.
See example below for making use of the debug.Print code.
Say cells A1:A10 have the below list of name :
Kiran
Suresh
Arun
Vijay
Vikram
use code to test debug.print in immediate window:
Sub testdebug()
Dim name As Range, namelist As Range
Set namelist = Range("A1:A5")
For Each name In namelist
Debug.Print name.Value
Next name
End Sub
4. Think from all possible angles before starting the coding
Before starting the coding, planning or execution of any complex task, thinking thouroughly is the best way to begin. Start with wrtiting the logic on the paper, about how your code is going to work and execute the different steps. Check and understand every aspects of the problem, working this way will help to understand the problem and while understanding the problem, the solution find its way to you.
5. Make use of inbuilt Methods in excel VBA
It happens to everyone of us, we write the similar codes which we have used in past again and again for executing the different tasks. for e.g., finding the text, converting the text, splitting text. Before staring to write a code, please check the already defined functions / Methods in the Excel VBA editor and make the best use of them.
Few exmamples of methods are - Sendkeys, Second, Strreverse and more. To view methods Got to VBA editor window. Click list Properties / Methods. or you can use the shortcut Ctrl + J.
6. Program your code in Blocks
While wrtiting a bigger program, find the smallest working step which can be built easily. Build this first set of code and then start coding the rest bigger steps around it. By using this we you can test your code step by step, while for a bigger tasks you have something in hand everyday which is getting tested and completed day by day. This improves your turn around time and avoids the complexities in the code.
7. Always keep the references of the important codes / formulas you dont want to search again
If you are a regular VBA user, you should keep the VBA books references handy. Good reference books provide lots of information and tips for daily VBA usage. Also, whenever you have free time, you can always refer these books to learn something new. While, learning you can also create your personal reference bank which you require the most.
8. Break your code / project into smaller pieces
The project / problem you get might be very complex, but it always is a combined problem of different small problems, We need to identify these small problems and start finding the solution for them. So, break the problem into several small pieces. This coding method is called as modularization. Below are few advantages of using the modularization method:
You can resuse the code: once we break the big program in smalled parts, we can resue this smaller part in several places or even in different projects.
Easy to test: the smaller codes are easy to test and debug. this helps us to avoid to go through the bigger program for just testing a one line code.
Easy to maintain: as the program is broken into smaller parts, it is easy to upgrade them as required.
9. Make use of VBA only when you need it
Please make the use of VBA only when you actually use it. Once you start learning VBA code, you will feel excited about using VBA for everything, but there are tasks which can be acheived by using the excel formulas. Avoid using the VBA codes to make your situation more complex. Make the best use of the excel built in features which are readily available. e.g. data validation, conditional formatting, pivot tables, formulas and more.
10. Take Challenges to learn more
If you are into everyday VBA coding user, then taking challenges is the best way to keep your VBA techniques updated and sharpened. You need to keep sharpening your VBA saw, like the same way of the woodcutter story of sharpening the saw. Try to automate the report, that your team prepares manually, try to simplify the formlas, try enhancing the already written VBA code to improve performance. This will keep your VBA skills refreshed, updated and improve your confidence of problem solving capability
Microsoft Excel has a built in Macro recording feature, which give us a great way to learn about new objects and how to use them. I use the macro recorder whenever required to get the objects code in the part of my codes. The macro recorder does not always create the complete code which is required to get the best output. But is gives you the idea on how to write the code to perform specific set of actions. You have an option to record the code and then edit to get it work for the best output.
2. Make use of the immediate window
Excel VBA editor has a feature of the immediate window, which can be of great help while coding. You can write any VBA statements and get the resluts immediately on the immediate window. For example, say cell A1 = How ? and Cell A2 = by pdaphal.blogspot.com way
Select cell A1 and type activecell.value and press enter in the immediate window it will return you the value in cell A1. see the sample code and output below:
?activecell.Value
How ?
?activecell.Offset(1).Value
by pdphal.blogspot.com way
3. Make use of Debug.Print
Debug.print is the best way to find out the bugs (errors) in your code. Our code sometimes does not work the way we want to, due to variable situations we receive in the data processing. By using the debug.print code, you can see the output in the immediate window, as your code / program is running.
See example below for making use of the debug.Print code.
Say cells A1:A10 have the below list of name :
Kiran
Suresh
Arun
Vijay
Vikram
use code to test debug.print in immediate window:
Sub testdebug()
Dim name As Range, namelist As Range
Set namelist = Range("A1:A5")
For Each name In namelist
Debug.Print name.Value
Next name
End Sub
4. Think from all possible angles before starting the coding
Before starting the coding, planning or execution of any complex task, thinking thouroughly is the best way to begin. Start with wrtiting the logic on the paper, about how your code is going to work and execute the different steps. Check and understand every aspects of the problem, working this way will help to understand the problem and while understanding the problem, the solution find its way to you.
5. Make use of inbuilt Methods in excel VBA
It happens to everyone of us, we write the similar codes which we have used in past again and again for executing the different tasks. for e.g., finding the text, converting the text, splitting text. Before staring to write a code, please check the already defined functions / Methods in the Excel VBA editor and make the best use of them.
Few exmamples of methods are - Sendkeys, Second, Strreverse and more. To view methods Got to VBA editor window. Click list Properties / Methods. or you can use the shortcut Ctrl + J.
6. Program your code in Blocks
While wrtiting a bigger program, find the smallest working step which can be built easily. Build this first set of code and then start coding the rest bigger steps around it. By using this we you can test your code step by step, while for a bigger tasks you have something in hand everyday which is getting tested and completed day by day. This improves your turn around time and avoids the complexities in the code.
7. Always keep the references of the important codes / formulas you dont want to search again
If you are a regular VBA user, you should keep the VBA books references handy. Good reference books provide lots of information and tips for daily VBA usage. Also, whenever you have free time, you can always refer these books to learn something new. While, learning you can also create your personal reference bank which you require the most.
8. Break your code / project into smaller pieces
The project / problem you get might be very complex, but it always is a combined problem of different small problems, We need to identify these small problems and start finding the solution for them. So, break the problem into several small pieces. This coding method is called as modularization. Below are few advantages of using the modularization method:
You can resuse the code: once we break the big program in smalled parts, we can resue this smaller part in several places or even in different projects.
Easy to test: the smaller codes are easy to test and debug. this helps us to avoid to go through the bigger program for just testing a one line code.
Easy to maintain: as the program is broken into smaller parts, it is easy to upgrade them as required.
9. Make use of VBA only when you need it
Please make the use of VBA only when you actually use it. Once you start learning VBA code, you will feel excited about using VBA for everything, but there are tasks which can be acheived by using the excel formulas. Avoid using the VBA codes to make your situation more complex. Make the best use of the excel built in features which are readily available. e.g. data validation, conditional formatting, pivot tables, formulas and more.
10. Take Challenges to learn more
If you are into everyday VBA coding user, then taking challenges is the best way to keep your VBA techniques updated and sharpened. You need to keep sharpening your VBA saw, like the same way of the woodcutter story of sharpening the saw. Try to automate the report, that your team prepares manually, try to simplify the formlas, try enhancing the already written VBA code to improve performance. This will keep your VBA skills refreshed, updated and improve your confidence of problem solving capability
No comments:
Post a Comment