VBA macro basics:

• Variables
• use Option Explicit to force declaring all variables
• Module-Wide variables: declare the variable before the module's first procedure
• Public variables: to make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module level (before the first procedure declaration) by using the Public keyword rather than Dim
• Static variables: they are declared at the procedure level, and they retain their value when the procedure ends normally. However, if the procedure is halted by an End statement, static variables do lose their values.
• Procedures
• By default, procedures are public
• the default method of passing an argument is By Reference
• Private procedures can be called by other procedures in the same module but not by procedures in other modules.
• to call a procedure

• Enter the procedure's name, followed by its arguments separated by commas.

                                 UpdateCost Parameter1, Parameter2, ...

• Use the call keyword followed by the procedure's name and then its arguments (if any) enclosed in parentheses and separated by commas

                                 Call UpdateCost(Paramemter1, Parameter2, ...)

• use the Run method of the Application object.

                                  Application.Run "UpdateCost"

• VBA allows identically named procedures in different module within the project. to call a procedure in different module

                                 Module1.MySub
                                 Call Module1.MySub

• Functions 
• It is important to understand a key distinction between functions that can be called from other VBA procedures and functions that can be used in worksheet formula. Function procedures  used in worksheet formulas must be passive. For example, code within a Function procedure can't manipulate ranges or change things on the worksheet
• Custom function is recalculated only when it needs to be, to force functions to recalculate more frequently, adding the following statement to a Function procedure makes the function recalculate whenever the sheet is recalculated.

                             Application.Volatile True

• Optional parameter, specify Optional keyword, then in the body, you can use IsMissing(parameter) function to check if the caller provide the parameter
• The function that returns an Error Value

VBA has built-in constants for the errors that you want to return from a custom function. These errors are Excel formula error values and not VBA runtime error values. These constants are as follows:

• xlErrDiv0 (for #DIV/0!)
• xlErrNA (for #N/A)
• xlErrName (for #NAME?)
• xlErrNull (for #NULL!)
• xlErrNum (for #NUM!)
• xlErrRef (for #REF!)
• xlErrValue (for #VALUE!)
  
  

  Function RemoveVowels(Txt) As Variant
  ' Removes all vowels from the Txt argument
  ' Returns #VALUE if Txt is not a string
      Dim i As Long
      RemoveVowels = ""
      If Application.WorksheetFunction.IsText(Txt) Then
          For i = 1 To Len(Txt)
              If Not UCase(Mid(Txt, i, 1)) Like "[AEIOU]" Then
                  RemoveVowels = RemoveVowels & Mid(Txt, i, 1)
              End If
          Next i
      Else
          RemoveVowels = CVErr(xlErrNA)
      End If
  End Function
  

• Error handling