• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1324
  • Last Modified:

User-defined type name as a parameter

I have >60 user-defined types. And I want to have a function like:

Function fSomeFunction(strUserDefinedTypeName As String)
Dim varMyVariable As strUserDefinedTypeName
...
End Function

I.e. I want to pass name of user-defined type as a parameter.
Is it possible to have something of that in VBA?
Use of Select Case with over then 60 cases is not an option..

Thanks in advance for any help or advice!
0
msacc97
Asked:
msacc97
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
flavoCommented:
Create a new function like this:

Public Function CalFunct(s As String) As Variant

    CalFunct = Eval(s)

End Function

Then call your other functions like this:
SELECT CalFunct("fSomeFunction(" & myField & ")")
FROM tblMyTable

The first function allows the Eval() function to be used in a query.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If your UDT is Public, then:

Function MyCoolFunction(oMyUDT As MyUDT) As Boolean

End Function

If, however, you mean that you want to pass in a String and "turn that into" a UDT, then I'm not sure you can do so ...
0
 
flavoCommented:
<Sorry, misread question.. time for a coffee>
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Leigh PurvisDatabase DeveloperCommented:
<@ Flavo>
Well hello there! ;-)
I dunno Dave, twice in as many weeks?  We're getting as chatty as a couple of awld birds. lol
msacc97, it seems to me your fundamental limitation is in the runtime resolution of the passed parameter value from within the function it's passed to.
(I'll not consider the runtime creation/editing of modules as an option - as it wouldn't work for so many deploying MDEs or runtime installs).
Dave's suggestion is perfect for creating and passing adaptable function defintions and values.
Eval can't help us once we're within a procedure though.
In VBA declarations are compiled.  Each variable is typed.  (Some strongly typed).
For the variable type to be passed to the function (be it a UDT or a built in one) would require late binding.
(i.e. use a variant and pass into that another variable typed of your choosing).

If that's what you want you'll still need to have that massive Case statement you fear - but have itonly once in a single core function that you pass out to.
Naturally you'll never get any help from intellisense regardless - as a late bound type couldn't be interpreted by the VBE at design time anyway.
Not what you want to hear no doubt, but does it make sense?
0
 
msacc97Author Commented:
flavo

My problem is that in my code line:

Dim varMyVariable As strUserDefinedTypeName - does not (of course) work..

What I need is to be able to define in VBA a type of varMyVariable variable.
The type NAME I'm passing as a parameter is one of my already existing custom  user-defined types names.
0
 
msacc97Author Commented:
Thank you guys for your responses!

What I'm trying to do is:

I have a flat text file.
Every line of this file is a record with its own number and length of fields.
I need to put every such record to a different table according to first 4 symbols of this line.
So I have created UDT for every type of record.
Now I have to read line, look what 4 first symbols in line do I have, read the rest of line into an appropriate variable of appropriate type, and then write every element of UDT variable into appropriate field of appropriate table.

The solution seems to be too straightforward, but I was short on time. Now I want to improve my code, because everything works fine, but I ended up with 60+ functions.. Using Select Case will not make much difference..
I was looking for a solution that could allow me to reduce code size, but looks like I will be able not much to improve..

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So all of your UDTs are different? That is, the have different elements and such, different data types? In general, the reasons for using a UDT is to more easily work with a "block" of data ... can you give us an example of a couple of different UDTs?
0
 
Leigh PurvisDatabase DeveloperCommented:
It sounds slightly that, having gone down the UDT road to describe your data, you're left wondering how to coerce use of this into your ongoing work.
I don't think there's anything drastically wrong with the late bound variant subtype... But that's still coercing what you already have to the task.  I'm not sure why dealing with the records (inserting the rows into the different tables) is made any easier using the UDTs.

It seems to me that if the first 4 characters of each line describe the type of record / entity that the line represents, then you don't need to hard code the description into Types at all.  You could equally describe the structure by other means - which aren't so rigid and you can just pass the type of record which you have previously identified (to be one of 60 different examples??)
You could go down an array, collection or class route of course.
But it seems to me that if you're wanting to describe data, and you're working in a database application - then you can use tables to describe your data.
A core (parent) table to hold the 60 different type records and if you wanted the detail still, a child table to hold related field information for each.
However once you've identified your type of row - why the intermediate step of a UDT?  Why not get to stuffing that data directly into the table?  Parsing along the fields which you then know comprise that row?  (Which I'm guessing you do into the UDT at present?)

If you want to provide some example data and code to illustrate your position then feel free.
Cheers!
0
 
msacc97Author Commented:
Type tA
ControlIdentifier As String * 1
CurrentDate As String * 8
ApplicationIdentifier As String * 2
DELIMITER As String * 2
End Type

Type tB
ControlIdentifier As String * 1
BatchNumber As String * 3
ApplicationIdentifier As String * 2
DELIMITER As String * 2
End Type
...

Type tZ
ControlIdentifier As String * 2
RecordType As String * 2
MessageNumber As String * 8
MessageText As String * 92
DELIMITER As String * 2
End Type

Every field in line is of fixed length and, yes, I have different sets of fields in each line.
I feel myself more comfortable to have all data structure descriptions in one place - that was the 2nd reason for me to use UDT.

Thanks for your patience!
0
 
Leigh PurvisDatabase DeveloperCommented:
Fair enough, but what advantage does having these UDTs offer you, when your ultimate goal is to insert this data into distinct tables yes?
(FWIW I'd consider having the table definitions held in local tables to be in "one place" too, but that's perhaps subjective).
Essentially I'm asking: if you would (or do) stuff the data from the text file into the individual UDF variables, why do so as opposed to just directly into the related tables?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm with Leigh - I just don't really see the reason to use a UDT to do this. It seems that you're moving the data twice - once into the UDT, then again into the database. It would seem easiest to just move it directly into the db.
0
 
msacc97Author Commented:
I have to make some processing of data extracted from text file. So anyway I have to store it temporarily into some variables.
Afterall I expect some possible changes in this processing in the future, so in future I'll have to reference elements of already existing UDT variables, which is IMHO better..

And again, the system is working and the speed is fine, but I thought it could be some solution to reduce lines of code..
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
IMO Temporary tables would be a better solution, considering that you must do some post-processing. Dump your raw data to a temp table, do whatever processing is needed, then move it to the live table. Temp tables are persistent (assuming you must do some processing in the future, as you mention) and thus ultimately reduce overhead and increase performance.

That said, if you're dead set on using them then as Leigh has stated you can't provide a single point of entry into your function where you could pass the name of a UDT and have it be built, but you can provide a single function (with your 60+ member SELECT CASE statement) that would return a valid Object ... of course, once you were returned that Object, you'd still need special handling to determine how to work with the individual elements of that object (again with the 60+ member SELECT CASE structure) ... for example, if I were returned an object built on a tA type structure, I'd have to work with it differently than if I were returned an object built on a tB type structure.

The only other suggestion would be an Element:Value sort of setup, with an array:

Type udtData
  ElementName As String
  ElementType as Long
  ElementData as Variant
End Type

Public mData As udtData
Public arr() As udtData

That is, as Leigh suggested earlier, an array of udtData Type structures ... so in essence you would (a) fill a single instance of udtData and then (b) add that instance to your array. For example, you'd have structures like this:

With mData
  .ElementName="CurrentDate"
  .ElementType = adDate
  .ElementValue = #02/02/2009#
End With
'/now add mData to the Array
Redim Preserve arr(Ubound(arr)+1)
arr(ubound(arr)) = mData
'/now do it again
With mData
  .ElementName="BatchNumber"
  .ElementType = adText
  .ElementValue = "00982-99"
End With
Redim Preserve arr(Ubound(arr)+1)
arr(ubound(arr)) = mData
With mData
  .ElementName = "RecordType"
  .ElementType = adInteger
  .ElementValue = 4
End With
etc etc etc

Using class module techniques, you could build a structure that would allow you to add, remove and "set" your class to a specific element, given the ElementName, and then return properties of that class for the udtData structure that is currently selected. Or you could use a Collection.
0
 
Leigh PurvisDatabase DeveloperCommented:
Yes I'd imagined that some "scrubbing" might be playing a part in this.

Reducing the lines of code often not only doesn't equate to better performance but can degrade it.
For example - consider the table structure means of describing the schema.  You could then store the data itself in rows each of which relates to a describing field row in the Child data dictionary table.
(Think Sharepoint lol)
Relatively inefficient - but an entirely adaptable schema operation.  Normalisation taken to the Nth degree really.
OK then - move this into a code based equivalent.
Class objects with child objects related to a parent.  Instantiated differently depending upon the type.
Again though - you're walking through hierarchies.
To be honest, the quickest, simplest (,dirtiest? ;-) method that springs to mind would be collections or arrays stored in a parent array.
The child collection would mean you don't have to know how many fields are present - you'd iterate the collection.
Arrays would have the advantage of being able to store meta data about the field in question in another dimension.
Each array is held in the parent array - which could also hold info about the type of record held in the associated array/collection, again in another dimension - this time of that parent array.
Of course - the simplest of all methods... ?
A set of dummy, import (scrubbing) tables.  Import into there, scrub away and then INSERT directly to the live tables (using simple, standard SQL INSERT statements).
Sound like a lot of work maintaining those 60 dummy tables?
Why need it be?
Copy an entire MDB/ACCDB file.  Make one file the "Scrubbing" version copy - edit in it, and then paste to the live copy and delete the scrubbing version.  It's simplicitly itself to enumerate tables in a database in code.
You need to make the tables anyway.  What does it matter if there's then copies made in code?
Cheers.
0
 
msacc97Author Commented:
Thank you! I'll try to implement your suggestions in future projects.

Good reading is here as well:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22765680.html
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now