Solved

User-defined type name as a parameter

Posted on 2009-04-06
15
1,265 Views
Last Modified: 2013-11-27
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
Comment
Question by:msacc97
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 24082091
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
 
LVL 84
ID: 24082167
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
 
LVL 34

Expert Comment

by:flavo
ID: 24082169
<Sorry, misread question.. time for a coffee>
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24082194
<@ 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
 
LVL 7

Author Comment

by:msacc97
ID: 24082282
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
 
LVL 7

Author Comment

by:msacc97
ID: 24082525
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
 
LVL 84
ID: 24082698
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
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

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24082727
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
 
LVL 7

Author Comment

by:msacc97
ID: 24082871
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24082944
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
 
LVL 84
ID: 24083023
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
 
LVL 7

Author Comment

by:msacc97
ID: 24083062
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 24083134
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
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 250 total points
ID: 24083146
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
 
LVL 7

Author Closing Comment

by:msacc97
ID: 31567257
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

707 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

18 Experts available now in Live!

Get 1:1 Help Now