Posts Tagged ‘User Defined Function’

Problem Statement: I have a database of employees where I store information about each person. I wish to have a picture of the employee in my database that I want to retrieve when I select an employee ID. Solution: I am sharing a VBA code that saves the file in SQL server table in binary format. When the file is retrieved, it can be done in original format. Let’s say I’ve a table Employee, it has two columns ID (Datatype: int) and Image (Datatype: varBinary(Max)). Code to store an image as binary type: (more…)

While creating a subroutine we can define whether arguments will be passed ByVal  or ByRef however this behavior can be overridden as discussed below.

Let’s say we have a procedure Append which accepts an argument by reference.However at the time of calling the procedure, if argument is enclosed in parentheses then it is treated as if passed by Value.

Sub Append(ByRef strArgument As String)
    strArgument = Replace(strArgument , "by Value", "by Reference")
End Sub
Sub Caller()

    Dim strTemp     As String

    strTemp = "Argument passed by Value"
    Append (strTemp) ' copy of the variable is passed
    Debug.Print strTemp ' prints Argument passed by Value
    Call Append((strTemp)) 'copy of the variable is passed
    Debug.Print strTemp 'prints "Argument passed by Value"
    Append strTemp ' reference of the variable is passed
    Debug.Print strTemp 'prints "Argument passed by Reference"

End Sub 

When a function is created in Excel VBA then it can be called from a sheet as well as from another procedure in the code.If one wants to check from where the function has been called then one can use the Application.Caller.

Its behavior depends on the caller. If it is called from:

  1. Range/Cell then it returns Range
  2. Shape or some control then it returns String(Name of the shape/Control)
  3. Some procedure or function then it returns Error.

The following function can be used to check how it works.

Function GetCaller()

    Dim strCallerTyp        As String

    strCallerTyp = TypeName(Application.Caller)
    Select Case strCallerTyp
        Case "Range"
            MsgBox "Called From a Range and the address is: " & Application.Caller.Address
        Case "String"
            MsgBox "Called from a control/Shape and the name is: " & Application.Caller
        Case "Error"
            MsgBox "Error:Not called from the sheet."
    End Select

End Function