Return of a Function as Scripting.Dictionary Object

[keirnus]

I made kinda of an array named arActLvlDetails.
I want it to be returned by the Function but
seemed to have an error in the last part.

Here's the code:
----------------------------------------------------
Private Function GetExcelData(objExcel As Object) As Object

Dim arActLvlDetails As Object

Set arActLvlDetails = CreateObject("Scripting.Dictionary")

For i = 1 To 6
arActLvlDetails.Add "AA" & i, objExcel.Worksheets(1).Range("A" & (2 + i)).Value
arActLvlDetails.Add "BB" & i, objExcel.Worksheets(1).Range("B" & (2 + i)).Value
arActLvlDetails.Add "CC" & i, objExcel.Worksheets(1).Range("C" & (2 + i)).Value

Next i
GetExcelData = arActLvlDetails '<-- Error occurs here!

End Function
----------------------------------------------------

The error occurs in this line:
GetExcelData = arActLvlDetails

Is it because the datatype of the function is Object and different from arActLvlDetails?
Since I want to return the arActLvlDetails, what should be done to accomplish it?

Need the details for a greater good.
The example here retrieves 3 cell values from an excel only.
I'll be retrieving around 100 cell values.
Got any good advice on how to implement it?


keirnus

 

[CyberLynx]

Perhaps try making arActLvlDetails Public.

I personally like to destroy Objects after I have finished with them to keep things clean.

.

 

[keirnus]

Perhaps try making arActLvlDetails Public.

I personally like to destroy Objects after I have finished with them to keep things clean.

.

You mean make arActLvlDetails a Global variable?
Sorry if my understanding is wrong.

I agree with destroying unwanted Objects.
That's good programming.

 

[CyberLynx]

It depends upon what the overall scope will be for the Object and how wide within the Database you want to use it. It also depends upon how many libraries you want to reference for your application to function properly (less is better in my opinion).

Global...Yes. Global is an old declaration used in older VB and is now replaced with Public. It can still be used but is only available for backward compatability. One day it will be gone completely. You are obviously calling the GetExcelData from a procedure where objExcel has been declared. If this is from a Form and the arActLvlDetails variable will only be used within that procedure then simply declare it there and Set it. To use it, just pass it to the GetExcelData Function as an argument (remove the function return) like this:

Private Sub Whatever()
   Dim objExcel As Object
   Dim arActLvlDetails As Object
   Set arActLvlDetails = CreateObject("Scripting.Dictionary")
   Set objExcel = whatever..........
  [COLOR="DarkGreen"] '.......
   '.......[/COLOR]
   Call  GetExcelData(objExcel, arActLvlDetails)
  [COLOR="DarkGreen"] '.......
   '.......[/COLOR]

   Set objExcel = Nothing
   Set arActLvlDetails = Nothing
End Sub

if the arActLvlDetails variable is to be used Form wide then you would want to declare it within the Declarations Section of the Form Code Module and SET it within the procedure calling the GetExcelData like this:

Option Compare Database
Option Explicit

Dim arActLvlDetails As Object
____________________________________________________________________

Private Sub Whatever()
   Dim objExcel As Object
   Set arActLvlDetails = CreateObject("Scripting.Dictionary")
   Set objExcel = whatever..........
  [COLOR="DarkGreen"] '.......
   '.......[/COLOR]
   Call  GetExcelData(objExcel, arActLvlDetails)
[COLOR="DarkGreen"]   '.......
   '.......[/COLOR]

   Set objExcel = Nothing
   Set arActLvlDetails = Nothing
End Sub

The GetExcelData Function may now look like this for either way:

Private Function GetExcelData(objExcel As Object, arActLvlDetails As Object)
   For i = 1 To 6
      arActLvlDetails.Add "AA" & i, objExcel.Worksheets(1).Range("A" & (2 + i)).Value
      arActLvlDetails.Add "BB" & i, objExcel.Worksheets(1).Range("B" & (2 + i)).Value
      arActLvlDetails.Add "CC" & i, objExcel.Worksheets(1).Range("C" & (2 + i)).Value
   Next i
End Function

Down to the nitty gritty....

What exactly is the Error you are getting when running the function you currently have now?

You appear to be using the Scripting.Dictionary solely as an Array mechanism. I personally wouldn't bother with it and simply use an actual Multi Dimensional Variable Array since the use of one is as equally challanging. After all, you are only storing values of specific Excel Worksheet Cells:

Private Sub Whatever()
   Dim objExcel As Object
   Dim arActLvlDetails() As Variant

   Set objExcel = whatever..........
 [COLOR="DarkGreen"]  '.......
   '.......[/COLOR]
   Call  GetExcelData(objExcel, arActLvlDetails())

   'To Test:
   Dim i As Integer
   For i = 0 To 5  
      MsgBox arActLvlDetails(1, i) & vbCr & arActLvlDetails(2, i) & _
             vbCr & arActLvlDetails(3, i)
   Next i
  [COLOR="DarkGreen"] '.......
   '.......

   'Free Memory[/COLOR]
   Erase arActLvlDetails
End Sub

Private Function GetExcelData(ByRef objExcel As Object, ByRef arActLvlDetails() As Variant)
   For i = 0 To 5   [COLOR="DarkGreen"]' 6 values[/COLOR]
      Redim Preserve arActLvlDetails(3, i)
      arActLvlDetails(1, i) = objExcel.Worksheets(1).Range("A" & (2 + i)).Value
      arActLvlDetails(2, i) = objExcel.Worksheets(1).Range("B" & (2 + i)).Value
      arActLvlDetails(3, i) = objExcel.Worksheets(1).Range("C" & (2 + i)).Value
   Next i
End Function

Mind you...the key used with the Scripting.Dictionary Object (either numerical or String) can be more descriptive for later reference within code.

.

 

[keirnus]

wOw!

This

Dim arActLvlDetails() As Variant

this

ByRef arActLvlDetails() As Variant

and this

   For i = 0 To 5  
      MsgBox arActLvlDetails(1, i) & vbCr & arActLvlDetails(2, i) & _
             vbCr & arActLvlDetails(3, i)
   Next i

are what I was looking for.

arActLvlDetails will not be used Form wide.

The error was like "Object not set" or "With (blah2x) not set".
Something like that. Can't check it again coz i'm not in the office now.

I was using Scripting Dictionary because of the keys.
I was using Column Fields for the keys.
So it would be easier to save the data to Access DB.

Thank you so much, CyberLynx!!!
It is really a great help...now i know what to do.