I've tried searching the internet for a definitive answer to this two-part scenario but couldn't find anything conclusive. I've been writing VBA procedures for sometime now in both Access and Excel and while trying to streamline some code I came across something of a dilemma
The first part is about using functions to return objects. The example below is generally what I've seen on the web for a function to return an ADODB.Recordset (I've simplified the code so no error handling etc.).
Public Function CreateADORecordset(SQL As String, Connection As ADODB.Connection) As ADODB.Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Call rst.Open(SQL, Connection)
Set CreateADORecordset = rst
End Function
The first part of the question is, why do I need a variable called rst when I could rewrite the function as this:
Public Function CreateADORecordset(SQL As String, Connection As ADODB.Connection) As ADODB.Recordset
Set CreateADORecordset = New ADODB.Recordset
Call CreateADORecordset.Open(SQL, Connection)
End Function
Is there anything fundamentally wrong with the above rewrite of the function? As the function returns an ADO recordset, why declare a variable separately?
I can take this a step further:
Public Function CreateADOConnection(ConnectionString As String) As ADODB.Connection
Set CreateADOConnection = New ADODB.Connection
Call CreateADOConnection.Open(ConnectionString)
End Function
Public Function CreateADORecordset(SQL As String, ConnectionString As String) As ADODB.Recordset
Set CreateADORecordset = New ADODB.Recordset
Call CreateADORecordset.Open(SQL, CreateADOConnection(ConnectionString))
End Function
Again, is this a particularly bad thing to do by using function return objects over declaring objects within the procedures via Dim?
In the grand scheme of things, I've been writing VBA code to transfer the contents of a recordset via GetRows into an Excel range. The function declaration line is:
Public Sub TransferRecordsetArray(GetRows As Variant, Destination As Range)
As TransferRecordsetArray works correctly, I've not included the code.
The dilemma I'm in now is in this scenario, I've reached a point where I don't need to declare any variables for the code to run correctly and I'm unsure how much of a good or bad thing this in terms of functions returning objects, scope and variables, etc.
In order to run the code correctly, I only need one of two lines without variables:
Call TransferRecordsetArray(CreateADOConnection(ConnectionString).Execute(SQL).GetRows, Target)
or
Call TransferRecordsetArray(CreateADORecordset(SQL, CreateADOConnection(ConnectionString)).GetRows, Target)
Any advice/recommendations on this way of writing VBA code would be greatly appreciated. I have used the task manager to keep an eye on memory usage on both methods and it doesn't seem to greatly differ and, it appears that VBA destroys the function return objects after a while, despite them not being explicitly destroyed by setting them to Nothing.
Many thanks.
