Excel And VBA Tips

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                 

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. Option Base - When an array is declared by d...

=IFERROR(INDEX($C$2:$C$9,MATCH($E3&F$2,$B$2:$B$9&$A$2:$A$9,0)),"") QUARY RESULT Month Name Productivity   Name Jan Feb Mar Jan a 180   a   150 130 Jan b 150   b 150     Feb a 150   c   145   Feb c 145   d     155 Mar d 155   e     160 Mar e 160           Mar a 130          

{=SUMPRODUCT(1/COUNTIF( A8:A18,A8:A18 ))} A               Name Amount a  £    80.00 b  £    33.00 c  £    56.00 d  £    88.00 a  £    80.00 f  £    60.00 g  £    55.00 a  £    80.00 i  £    90.00 j  £    96.00 a  £    80.00