Excel Function format

Question

I am quite new to writing functions in excel (been mainly coding sub procedures).

I was wondering what the following function declaration means?

public function function_name(args as string) as string

I understand everything up to the second instance of as string. This is something new to me and I am not sure how this is different to just declaring:

public function function_name(args as string)

Answer 1

public function function_name(args as string) as string

• public is the access definition. public means accessible across the VBA Project

• function means that is it a function (meaning it is supposed to return something)

• function_name is the name of a function (can't start with 1 or underscore)

• args is the local parameter name to be used within the body of function

• args as String indicates that the function is expecting the args to be of a String type

• ...) As String indicates that the function will be returning a String data type. So if you have had dimensioned a String type variable you would be able to assign a value to it using the function.

---

the standard (default) declaration without explicitly specifying the type to be returned returns a Variant

It's the same as declaring a variable without specifying its type.

Dim aVariable

with is equivalent to

Dim aVariable as Variant

because Variant is the default type.

so the as Variant always exist unless there is a different type specified. And because it's default you do not have to explicitly code it.

It's somehow similar to Range("A1").Value and Range("A1") - both are the same because .Value is the default property of a Range object.

What happens now is the compiler evaluates what value goes into the aVariable under the hood and assigns that type to the variable.

Let's say you have

Sub Main()

    Dim varVariable
    Dim strVariable As String

    varVariable = "hello world"
    strVariable = "hello world"

    MsgBox "varVariable is of " & TypeName(varVariable) & " type" & vbCrLf & _
           "strVariable is of " & TypeName(strVariable) & " type"

End Sub

Like I've said now both are of String type

---

Not sure how familiar with for example C# you are but in C# you declare the return type of a function right after the access modifier ie.

public string myFunction(string args)

so in VB/VBA the second as String is equal to the first string (right after public) in C#

in C# you would use a return keyword while in VBA you replace the return keyword with the function name. Therefore in VBA a very basic sample

Public Function ReturnFirst3Characters(args As String) As String
    ReturnFirst3Characters = IIf(Len(args) > 2, Left(args, 3), args)
End Function

Function returns first 3 characters of the string you have passed to it (if the string is longer then 3 characters, if not it returns the string you passed to the function)

Answer 2

The second "as" indicates the type the function returns. That function returns a string, you can return a value inside the function in this way:

function_name="Value"

Answer 3

From http://www.cpearson.com/excel/writingfunctionsinvba.aspx :

Function RectangleArea(Height As Double, Width As Double) As Double
    RectangleArea = Height * Width
End Function

This function takes as inputs two Double type variables, Height and Width, and returns a Double as its result.

Answer 4

If you're familiar with Procedures, think of a Function as a Procedure that returns a value. The as String as the end indicates what type of value is returned.

The way you return a value is to use the name of the function as if it were a variable:

Public Function ConvertToUpperCase(str as string) as string
    ConvertToUpperCase = UCase(str)
End Function