What is the syntax to count how many times a substring occurs in a string?

<integer variable> = Len(<string>) - Len(Replace(<string>, "<substring>", "")) 
'example
sentence = "Hello, this is a sentence,"
countCommas = Len(sentence) - Len(Replace(sentence, ",", "")) 
msgbox(countCommas) 'output is 2
Advertisements

What is the syntax for a function to return a value?

Function <function name> (<parameter name> As <data type>)
     'parameters are optional
     <function name> = <your code/calculations here>
End Function
'examples
Function TotalPay (hrs As Integer)
     TotalPay = hrs*12
End Function

Public Function returnOne() As Integer
     returnOne = 1
End Function

 

What is the syntax to calculate Present Value (PV) in VBA?

PV(Rate, Nper, Pmt, FV, Due)
'Rate - interest rate per period
'Nper - number of payment periods
'Pmt - amount of periodic payment
'FV - future value
'Due - 1 if payment due at beginning of month, 0 for end
'examples
MsgBox (PV(0.03, 4, 1)) 'yields -3.71709840281037
MsgBox (Abs(PV(0.03, 4, 1))) 'yields 3.71709840281037
MsgBox (Round(PV(0.03, 4, 1), 0)) 'yields -4

What is the syntax to declare procedures?

Private Function <your name> ()
   ‘your code here
   ‘callable only by procedures in the same module
End Function


Public Function <your name> ()
   ‘callable from any procedure
End Function

 
Private Sub <your name> ()
   ‘callable only by procedures in the same module
   ‘cannot be called from Macros dialog box
End Sub

 
Public Sub <your name> ()
   ‘callable from any procedure of any module
   ‘can run procedure from Macros dialog box
End Sub