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 ...
Main Topics
Browse All Topics
Loading Advertisement...
Question
User-defined type name as a parameter
I have >60 user-defined types. And I want to have a function like:
Function fSomeFunction(strUserDefin
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!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Subscribe now for full access to Experts Exchange and get
Instant Access to this Solution
- Plus...
- 30 Day FREE access, no risk, no obligation
- Collaborate with the world's top tech experts
- Unlimited access to our exclusive solution database
- Never be left without tech help again
Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.
- "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
- "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
- "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.
See what Experts Exchange can do for you.
Got a question?
We've got the answer.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
Need individual assistance?
Our experts are ready to help.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Want to learn from the best?
Read articles from industry experts.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Working on a long term project?
Store your work and research.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Trusted by the world's most respected brands.
Faithfully serving IT professionals since 1996.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Free Tech Articles
-
WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
-
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
-
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
-
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
-
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
-
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...
Cloud Class Webinars
- Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
- Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
- IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
- Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
- Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
- How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...
Join the Community
Give a Little. Get a Lot.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Answers
<@ 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?
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..
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!
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!
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?
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..
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.
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.
Thank you! I'll try to implement your suggestions in future projects.
Good reading is here as well:
http://www.experts-exchang
3 Ways to Join
Business Accounts
- Perfect for organizations
- Multiple users
- Save over 36%
- Create a Business Account
Answer for Membership
- Earn free access
- Showcase your knowledge
- No credit card required
- Sign Up to Answer
Loading Advertisement...
by: flavoPosted on 2009-04-06 at 14:57:15ID: 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.