Solved

Passing User Defined Type Arrays to and from functions

Posted on 2001-06-07
6
197 Views
Last Modified: 2010-08-05
Hi,
I'm doing this in VBA (MS access). Been going around in circles this
afternoon, trying to suss out quite how arrays
and UDTs work. Could any kind souls out there point me in the right
direction, as this is driving me mad!


The first problem is how does one pass an array back to the function that
called it? For example:

Function TestArray() As Variant
    Dim varArray As Variant
    Dim intA(3) As Integer

    'using variant for int
    intA(0) = 1
    intA(1) = 2
    intA(2) = 3

    varArray = intA
    varArray = ShowArray3(varArray)

    'intA = varArray        'It doesn't like this when I compile! Says Can't
assign to Array

    'Contents not changed - the contents of varArray have however.
    MsgBox intA(0) & ", " & intA(1) & ", " & intA(2)
End Function


Function ShowArray3(varA As Variant) As Variant
    MsgBox varA(0) & " : " & varA(1) & " : " & varA(2)

    varA(0) = 4
    varA(1) = 5
    varA(2) = 6
    ShowArray3 = varA
End Function

The second (related) issue is that I'm trying to pass an array of UDTs to a
function, doing stuff to them and then passing them
back. An example:


Public Type Alph
    Name As String
    Value As Integer
End Type


Function TestArray() As Variant
    Dim udtArray() As Alph
    Dim varArray As Variant

    ReDim Preserve udtArray(1)
    udtArray(0).Name = "one"
    udtArray(0).Value = 1
    ReDim Preserve udtArray(2)
    udtArray(1).Name = "two"
    udtArray(1).Value = 2

    'varArray = udtArray            'bit that its unhappy about :(
    'udtArray = ShowArray(udtArray)

    MsgBox udtArray(0).Name & " : " & udtArray(0).Value & " : " &
udtArray(1).Name & " : " & udtArray(1).Value
End Function

'Can pass udt ok but having problems with arrays of udts
Function ShowArray(udtA As Alph) As Alph
    'MsgBox udtA(0).Name & " : " & udtA(1).Name
    'udtA(0).Name = "three"
    'udtA(0).Value = 3
    'udtA(1).Name = "four"
    'udtA(1).Value = 4
    'ShowArray = udtA
End Function

Many many thanks to anyone who can pull the wool off my eyes, Thanks

Tom Roberts, puzzled of Waterloo

p.s. just been nosing around classes, is that the way one should do it
really?



0
Comment
Question by:TomRoberts
6 Comments
 
LVL 2

Expert Comment

by:Microsoft
Comment Utility
try defining INTA as a var first

you are trying to assign a function to a var without defining the var first.

dim inta as string

or something

this should sort out the compile error
0
 
LVL 18

Accepted Solution

by:
bobbit31 earned 200 total points
Comment Utility
This is for the second part:

instead of passing the array back and forth why not just use the reference as shown below (vb default passes parameters ByRef)

Function TestArray() As Variant
   Dim udtArray() As Alph
   Dim varArray() As Alph

   ReDim Preserve udtArray(1)
   udtArray(0).Name = "one"
   udtArray(0).Value = 1
   ReDim Preserve udtArray(2)
   udtArray(1).Name = "two"
   udtArray(1).Value = 2

   varArray = udtArray            'bit that its unhappy about :(
   ShowArray udtArray

   MsgBox udtArray(0).Name & " : " & udtArray(0).Value & " : " & udtArray(1).Name & " : " & udtArray(1).Value

End Function

'Can pass udt ok but having problems with arrays of udts
Private Function ShowArray(udtA() As Alph)
   MsgBox udtA(0).Name & " : " & udtA(1).Name
   udtA(0).Name = "three"
   udtA(0).Value = 3
   udtA(1).Name = "four"
   udtA(1).Value = 4
End Function

Private Sub Command1_Click()

    TestArray

End Sub
0
 
LVL 6

Expert Comment

by:sharmon
Comment Utility
If you are just using the UDT's in a standard project you do not need to make classes out of them.  If you need your UDT's to follow certain business rules, putting them in classes will allow you to deal with that.  However when making a dll that passes these UDT's between the client application and itself you must make them classes.  I have updated your code to work, although I am not sure what you are trying to do, it looks more like you are just testing how things work in the routines.  This may give you some ideas.  Although there are multiple ways one could go about doing what you did to make it work.


Option Explicit

Public Type Alph
   Name As String
   Value As Integer
End Type

Function TestArray() As Variant
   Dim intA(3) As Integer

   intA(0) = 1
   intA(1) = 2
   intA(2) = 3

   ShowArray intA

   MsgBox intA(0) & ", " & intA(1) & ", " & intA(2)
   
   TestArray = intA
End Function


Sub ShowArray(varA() As Integer)
   MsgBox varA(0) & " : " & varA(1) & " : " & varA(2)
   
   varA(0) = 4
   varA(1) = 5
   varA(2) = 6
End Sub

Function TestArray2() As Alph()
   Dim udtArray() As Alph

   ReDim Preserve udtArray(1)
   udtArray(0).Name = "one"
   udtArray(0).Value = 1
   
   ReDim Preserve udtArray(2)
   udtArray(1).Name = "two"
   udtArray(1).Value = 2

   ShowArray2 udtArray

   MsgBox udtArray(0).Name & " : " & udtArray(0).Value & " : " & _
   udtArray(1).Name & " : " & udtArray(1).Value
   
   TestArray2 = udtArray()
End Function

Sub ShowArray2(udtA() As Alph)
   MsgBox udtA(0).Name & " : " & udtA(1).Name
   udtA(0).Name = "three"
   udtA(0).Value = 3
   udtA(1).Name = "four"
   udtA(1).Value = 4
End Sub
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:TomRoberts
Comment Utility
Thanks.

What I was trying to do (cos I read somewhere that it is more efficient to pass arrays as variants (is this true?)
was use the variant to pass the array to the function.

I can see that varA = intA would work (as varA is a variant) but intA = varA doesn't.

So:
Dim intA(3) as variant would compile.

Anyhow, first bit solved now - I forgot about byRef! (oh dear).

ShowArray3 intA

to ShowArray3, where that is a sub does the trick.
Now to see if that works with udts...
0
 
LVL 18

Expert Comment

by:bobbit31
Comment Utility
as for the first part, i don't understand why you are doing intA = varArray...

why not just use varArray?

and again, pass varArray ByRef to ShowArray3

ie:

Sub TestArray()
   Dim varArray(3) as Integer

   ShowArray3 varArray

   msgbox varArray(0) " : " & varArray(1) & " : " & varArray(2)

End Sub

Sub ShowArray3(varA() as Integer)

   varA(0) = 1
   varA(1) = 2
   varA(2) = 3

End Sub

0
 

Author Comment

by:TomRoberts
Comment Utility
Thanks all of you for your help. Thats made my day.

Brief background is that I'm writing an error handler at the moment. What I'd like to do is pass additional criteria to it (such as userID, the call stack etc) without having 7 or so parameters.

I was playing around with something simple first, to see if I understood clearly what was going on.

Cheers
Tom
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now