Basic input output handling by functions in VBA scripts-CodePudding

In VBA there seems to be no distinction between input and output variables in the function definition syntax?

I have some difficulty calling output variables from functions in VBA. I come from MATLAB/R environments, and somehow the documentation for those programs was much easier to get.

However, below in the code comments, I specifiy what I want the function and caller sub (sub procedure) to do.

After some problem solving iterations, I have this:

Sub GetInput()

    Set Dates = GetDates(StartDateContract, EndDateContract)
    StartDateContract2 = Dates(1)

        Debug.Print StartDateContract2
    
End Sub


Public Function GetDates(Optional StartDateContract As Date, Optional EndDateContract As Date) As Collection 'Should I make it a public function?

    'Then, start the variable:

    Set GetDates = New Collection ' should name of variable colletion type be the same as name of function?

    Dim StartDateContract As Date
    Dim EndDateContract As Date

    Set StartDateContract = Range("B4").Value
    Set EndDateContract = Range("B5").Value
    
    GetDates.Add = StartDateContract
    GetDates.Add = EndDateContract
    
    'Debug
    'Here I want to add both dates in some kind of array or matrix and pass that as the output variable (DateArray)
    'Do I have to type: ?
    'Return OutputVariable = DatesArray
    
    Debug.Print StartDateContract
    Debug.Print EndDateContract

End Function

My first try and original question:

Sub Main()

 ' here I want to call the results from the function GetDates
 
 'e.g. StartDateContract = Dates(1)
 ' EndDateContract = Dates(2)

End Sub


Function GetDates(StartDateContract As Date, EndDateContract As Date) As Double

    Dim StartDateContract As Date
    Dim EndDateContract As Date

    Set StartDateContract = Range("B4").Value
    Set EndDateContract = Range("B5").Value
    
    'Debug
    'Here I want to add both dates in some kind of array or matrix and pass that as the output variable (DateArray)
    'Do I have to type: ?
    'Return OutputVariable = DatesArray


      
        Debug.Print StartDateContract
        Debug.Print EndDateContract

       'now calculate the year and data difference between those two dates, and also return that
    
    End Function

Another try was this, but I get 'object required' errors:

Sub GetInput()
    Dim CurrentInput As Collection
    Set CurrentInput = GetDates()
            
    ' Use the collection's first index to retrieve the first item.
    ' This is also valid: Debug.Print Employee(1)
    Debug.Print CurrentInput(1)
    Debug.Print CurrentInput.Item(2)
    
End Sub


Public Function GetDates(Optional StartDateContract As Date, Optional EndDateContract As Date) As Collection

    Dim CurrentInput As Collection
    Set CurrentInput = New Collection '?

    'Dim StartDateContract As Date
    'Dim EndDateContract As Date

    Set StartDateContract = Range("B4").Value
    Set EndDateContract = Range("B5").Value
    
    CurrentInput.Add = StartDateContract
    CurrentInput.Add = EndDateContract
    
    'Debug
    'Here I want to add both dates in some kind of array or matrix and pass that as the output variable (DateArray)
    'Do I have to type: ?
    'Return OutputVariable = DatesArray
    
    Debug.Print StartDateContract
    Debug.Print EndDateContract

End Function

CodePudding user response：

You can pass the variables By Ref (by reference instead of by value = By Val), like that you can change the value within your sub-routine:

Option Explicit

Sub Main()

Dim StartDateContract As Date
Dim EndDateContract As Date
Dim Diff As Variant '>>>> adjust the type to your needs

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

GetDates ws, StartDateContract, EndDateContract, Diff

End Sub


Private Sub GetDates(ws As Worksheet, _
    ByRef StartDateContract As Date, ByRef EndDateContract As Date, ByRef Diff As Variant)

StartDateContract = ws.Range("B4").Value
EndDateContract = ws.Range("B5").Value

'now calculate the year and data difference between those two dates, and also return that
Diff = EndDateContract - StartDateContract      '>>> adjust this to your needs

End Sub

I strongly recommend to not use Range implicitly, i.e. without explicit referencing a worksheet. Therefore I am passing the sheet from which you want to read the values as well to the sub.

Another side note: in VBA parameters are passed By Ref if you don't use By Val explicitly. It's good coding habit to always type it. An object like a worksheet can only be passed by Reference, never by value.

CodePudding user response：

You can use Collection.

First you need to declare the output of function like this:

Function GetDates(StartDateContract As Date, EndDateContract As Date) As Collection

Then, start the variable:

set GetDates = New Collection

After this you can add as much values as you want like this:

GetDates.Add Item 'Also ,Key in case you need keys.

After calling this function from other modules you will be able to access its values like this:

 StartDateContract = Dates(1) '1 being the key or index assigned in the function

Do not forget to call the function with a Set in order to work correctly:

set Dates = GetDates(StartDateContract, EndDateContract)

EDIT:

Adding full function for reference:

Sub GetInput()
    Dim CurrentInput As Collection
    Set CurrentInput = GetDates()
            
    ' Use the collection's first index to retrieve the first item.
    ' This is also valid: Debug.Print Employee(1)
    Debug.Print CurrentInput(1)
    Debug.Print CurrentInput.Item(2)
    
End Sub


Public Function GetDates(Optional StartDateContract As Date, Optional EndDateContract As Date) As Collection

    Set GetDates = New Collection

    'Dim StartDateContract As Date
    'Dim EndDateContract As Date

    Set StartDateContract = Range("B4").Value
    Set EndDateContract = Range("B5").Value
    
    GetDates.Add = StartDateContract
    GetDates.Add = EndDateContract
    
    'Debug
    'Here I want to add both dates in some kind of array or matrix and pass that as the output variable (DateArray)
    'Do I have to type: ?
    'Return OutputVariable = DatesArray
    
    Debug.Print StartDateContract
    Debug.Print EndDateContract
    
End Function