UDT passing byref works in WinXP Excel 2010 fails on Win7 64 bit Excel 2010 32 bit

I'm an experienced Access, VB6 and SQL Developer (data warehouse work) but a newbie at Excel I know too much from VB6 and Access VBA and frankly I'm a little dangerous in Excel. The code below works flawlessly when run on Windows XP and executed in Excel 2010 and 2003 environments. I'm really hoping there's a simple solution otherwise I'll have to replace my UDT array with code that reads the worksheet directly, back when I built this the UDT array it was conceptually easier for me than the worksheet but really isn't that different. I would probably build this without the UDT array if I were doing it today, I just hope I'm not rebuilding this. Two possible complaints against my solution are speed, amazingly it's blazingly fast, another is system memory which hasn't been a problem.

So I'll start at the top when compiling I get a type mismatch error flagging the UDT typRecord.

Please Login or Register  to view this content.

So here is the declaration of typRecord

Please Login or Register  to view this content.

Here is the procedures declaration

Please Login or Register  to view this content.

And the declaration of the UDT itself and a few other globals and yes I'm having issues with the enum too but that's easier to work around. I have tried declaring the type as Public and also declared without the words Private or Public before it. I end up with the same type mismatch error. I'm really hoping someone tells me I have boneheaded mistake in the declarations and I can get on with this project. I'd rather be a bonehead than have to rewrite this.

Please Login or Register  to view this content.

Finally it type mismatches on more than just my custom function, I've added this code to address a duplicate record issue and it type mismatches against the native CSTR function. The enumeration shows up there as well.

Please Login or Register  to view this content.

I'm receiving 140 spreadsheets from various facilities and importing them into a database so an auditor can review the data and do comparisons. The workbooks all have 13 sheets and one hidden sheet, I'm iterating through the hidden sheet and picking off the data points that link into that. The designer of the whole collection built something very visually functional for a human but kind of a pain to deal with pro grammatically. So it was easier to dump this into an array and then parse out the elements of the array to gather the data. So ultimately I could take this code I'm using to load the UDT and write to my SQL Server, but it will be a lot messier. With the Jet or MSDE backend behind Excel I'm rather surprised this isn't easier. I'd rather just dump this into a transformation table in SQL Server and mess with the data there. If I'm missing an easier solution that way a link to an explanation page would be appreciated.

Please Login or Register  to view this content.

eProvID is an enumeration and is just letters representing the number 2, in the UDT declaration the second postion is AcctCD which is a string. So unless typRecord(2) which is identical to typRecord(eProvID) is null (and it's not in any of my spreadsheet I did check that) the resulting value is 2. My problem is this blows on compile in Windows 7 but works fine when run and compiled in XP. The rub with the compiler blowing it is it's before there is any data. I think there is a fundamental flaw in my user defined type declaration, I simply can't find any examples that differ from mine. First thing I checked is if the UDT had been deprecated and that doesn't appear to be the case in Microsft's documentation. I probably should not have posted that above since it's misleading it's really the least of my problems.

The fact I can't pass the whole UDT to another function is what is really killing me.

Ok I'm seeing one possible bonehead mistake here with the strProviderID = CSTR(typeRecord(eProvID), since it's an array I might not be calling the right data element. So please ignore that folks and looks at the passing the UDT to another function.

Well I'm really puzzled now. I have remoted into my work computer from home with Citrix, the offending code above (passing the UDT to a function) works just fine now. I have a hunch if this fails tomorrow when I'm at my system I'll be sending this to the IT people that support our computer systems to figure out. I will report back on what happens tomorrow when I'm physically at my computer. It's not impossible that I'm currently remoted into a Virtual Machine that's not the same as my desktop and just looks like it. I'm the guy who always gets the weird problems (think Wile E Coyote with the thunder cloud following him around).

shg I'll mock that up and give that a shot as well. Here's the new news.

Well I'm really puzzled now. I have remoted into my work computer from home with Citrix, the offending code above (passing the UDT to a function) works just fine now. I have a hunch if this fails tomorrow when I'm at my system I'll be sending this to the IT people that support our computer systems to figure out. I will report back on what happens tomorrow when I'm physically at my computer. It's not impossible that I'm currently remoted into a Virtual Machine that's not the same as my desktop and just looks like it. I'm the guy who always gets the weird problems (think Wile E Coyote with the thunder cloud following him around).

For the sake of future people looking for solutions I'm going to post this with an ample serving of humble pie.

It works now, so lacking any better explanation I have to blame it on the interface between chair and keyboard (me). I hate that explanation but that's all I got.

Please Login or Register  to view this content.

This code snippet is wrong since this is a user defined type and functions a bit differently than a standard variable. This works

Please Login or Register  to view this content.

once the string is the actual value being passed the type mismatch error stops.

The mystery I haven't figured out and have to attribute to my error is this.

Please Login or Register  to view this content.

When I first ran this I thought I was get type mismatches on this line of code. Since it now functions and has no logical reason not to function. I can only conclude I misunderstood the compiler error and had a problem somewhere else. When I first ran this I had not put the code in that looked up the prior existence of the provider. So the error above wasn't my initial compiler problem. As I was working on this project trying to debug it I made some changes here and there in the code and must have picked off the issue the compiler was hitting without realizing I had fixed it. What I should have done was comment out this line of code

Please Login or Register  to view this content.

and run the compiler again to verify it was the error. A fail forgetting a pretty basic rule of debugging.

If anyone wants to throw a couple of links into good sites for debugging information below for the next person who has an issue like this I'm sure the people who hit this in the future would appreciate it. Appears I could use a good review. If there was an embarrassed emoticon I'd put it here.

Thanks shg for the help, your post (#6) helped me realize my mistake with how I was pulling the value out of my udt. I was a VB6 and Access developer for years, moved to medical EDI and X12 several years ago and now SQL in a datawarehouse, I've forgotten a lot of basic VB skills. This also was my first use of a udt in any application. Great construct in VB but a little confusing in it's first round of use.

Thanks