Excel VBA Introduction Part 34 - Type Declarations (User-Defined Types)

welcome to this YSL tutorial in this video we are going to explain how to use type declarations in Excel VBA so the video is all about how you can declare your own user-defined data types in VBA we'll start by explaining what a type is and why you might want to consider using one in the first place before we show you how you can declare your first basic type once you've done that we'll show you how you can declare a variable which uses that type and how you can both read from and write to that variable we'll spend a short time looking at how you can combine enumeration z' something we've covered in a previous video with your type declarations and also to finish the video how you can use a type that you've declared in further type declarations to create sort of a nested type declaration in fact so there's a few things to cover let's get started in VBA the type statement allows you to declare your own user-defined data types and that can be a handy thing to be able to do when you're working with well structured data such as our list of films so every record in this list has a set of five properties an ID a title the release date and so on and if I wanted to work with a row of data in VBA I'd want to be able to store that data somewhere and I've got a variety of choices I guess the most obvious of which is to use a simple variable or set of variables if I switch into the visual basic editor I've got a routine here where I've declared five separate variables one for each property and I can choose the data type of those variables and assign a value to each one that works but it's a little bit tedious to have to declare multiple variables particularly I have to do that more than once if I create a new sibility now I will do something similar so another approach might be to use basic arrays if I switch into another module have declared a symbol array which has got five elements in it and I've assigned a value to each one of those five elements the downside to this approach is that you've got little control over which data types kayuu so I've used the variant data type here to allow vba to it to assign the correct data type but it's also a little bit difficult to see what values is supposed to be stored in each element of the array so our safe film - I'm not entirely certain whether that should be the date or the the length of the film it doesn't really become clear so another approach altogether is to use something called a type so in another module I've started a simple subroutine and I've declared already a user defined type called a film what that means is I can declare my own variables based on my new type so my type is called film which you'll see appears in the intellisense list and then if I want to assign values to some of its properties I can say simply things like new film dot and I give a list of all the properties the elements that I've declared for my type and I can assign a value to each one and just in a nice simple straightforward way like like so then it's very very obvious what each property is meant to contain I can control the data types of each property and so this video is going to show you how you can create your own versions of these so let's look at how to declare a type in the first place I've inserted a brand new blank module here and a type declaration is something that must be done on the module level you can't declare types within a subroutine so at the top of my module I'm going to type in or I'm going to start with the word type following that I'm going to enter the name that I want my type to have and earlier on I gotta call my film and then I'm going to give myself a couple of blank lines and I'm going to type in end type what I can then do inside these two lines is declare all the individual elements and the data types that I want my film type to have so I'm going to end in my colon space and I'm going to declare first of all an ID which I'm going to store as an integer and I'm going to play on the next line and name as a string and then on the next line I'm gonna have I know a date well if I can spell day probably date as a date datatype and I'll also have a length as an integer and finally I'm going to have a genre as a string as well so that's the basic way that you declare types it's almost like declaring variables just store inside the lines type and n type you can actually declare your types as public or private so by default I'll be allowed to use this type in any module in this project so it's already public if you prefer you can explicitly say that by saying public type if you prefer to keep this type limited to this particular module you can put in the word private instead so very similar to public employer variables public private enumerations and so on I'm quite happy this is a public type so I'm going to just take away the air the optional keyword there and that's essentially my type declared using your type in code then is just as simple as declaring a variable which sets its data type to be equal to the name of the type you've declared so if I create a new civil it in which I'm going to call test film type which isn't very inventive but it'll do for now I can declare a variable in here what I'm going to call new film and I'm going to say as film so your datatype should appear in the intellisense list to set the values of its various elements then this is just as simple as treating them as independent variables so if i say new film and dot's i'll get the full list of elements that i've declared and I can say ID equals and I put in a symbol number for example I can use any other method that I could use to set the value of a variable as well so I could use an input box for example so I could say film sorry new film dots name equals input box and then ask the user to type in a film name I could also use valleys of cells if I knew that there one of the cells in my worksheet contain the date that I wanted to use I could say new films updates equals and there's an arranged c10 value for example so these really genuinely derive just like traditional variables you can set their values in any way you see fit and but the beautiful thing is that you've got all these properties grouped together in a nice easy to use object reading values from a type is just as simple as writing values to it so if I wanted to for instance read the information into cells in the worksheet or just for the sake of demonstration I'll just display some information in a message box that the principal is the same thing so if I I display a message box which shows me the new film ID and I'll join that together with a space and then also show the new film dot name and if I execute that subroutine using the f5 key I can type in a film name I'll just type in Guardians Academy politicking in the whole film name click OK and I'll see those results return to the message box but I could just as easily have gone into cells in the worksheet um so it genuinely is just as simple as using normal variables one pipe fun thing to do with our types is to combine them with enumerations we've covered enumerations in a previous video in this series so i won't go into too much detail here but let's just say for example that for the genre at the moment I've got it stored as a string so if I wanted to sell its value I'd have to type in the explicit string that one's at the John let's be that doesn't give me any any sort of help in terms of what the valid options might be maybe I want to restrict the list to a specific range of values in that case what I can do is create an enumeration that contains the individual genres so I'm going to declare that at the top of my module again so just below my type I'm going to declare an enum called genres and I'm gonna say end enum and then inside that statement I'm going to write out the various types of films I might want see so this action there's adventure there's animation and so on and so on and so on they're sci-fi etc etc I could carry on the scene more and more but but that's enough to get the idea what I can do then is change my genre element to not store a string instead to store an item of the genres enumeration so again that should appear in the intellisense list just as to our type did earlier on but the cool thing about that is when I go to set my my genre productive my new film item I guess a new film dot genre and I can make it equal to and I get a nice build enumerated list to type in the whole word I can just select from that list now although the enumeration makes it quite easy to set the value of the genre element it's not quite so easy to read it back if I try to incorporate that into my message box so I'll add another space and then another proxy so it's a new film genre sadly what I won't get back is the word adventure or sci-fi or whatever what I've used here if I execute the routine I'll type in guardians again once I can't spell it Guardians there we go I'll get 99 Guardians 1 and then the reason I get the number 1 is because I've been sure is is that the second element so in the enumeration all of these these items all the elements in the enumeration are assigned a member so action is 0 Adventure is one animation 2 and so on and so on and so on so if I wanted to be able to return the applicable descriptive text what I'd have to do is create perhaps a custom function that takes in the genre used and converts that into a string just to show you the basic principle of how that might work I could declare a new function here called genre text perhaps on a text not John a test and in there I would accept an item so I would say something like I don't know value as genres and it's going to return a string and what I could then do in my function is like a test with a case statement paps so I could say select case value and select and I could say then case action and then I could say genre text equals excuse me oops genre text equals the word action so returns actual string of text case adventure and so on and so on and so on so this point everything is very straightforward once you've got the basic structure of the other function you could just add more and more and more cases to this to return the various strings of text for case so to actually use that men to display that on my message box all I would need to do is pass my new film genre into my genre text function so I could say rather than just new film la Hamra I could say on the text open some parentheses close parentheses at the end and that would return the correct word for whatever genre has been set so I just run that one again to show you that it works doesn't matter what the film name is at this point click OK I'll get the word adventure rather than the number 1 so it's a little bit more effort to use but I quite like enumerations I try to use them frequently it just means that you have to have if you want to return the actual textual description of your enumeration you'll need a function that will do that for you another useful feature of a type is that once you've declared it you can use it to set the data type of another element in another type so in this example I've worked up a quick contact type which has some basic personal details and then two sets of address information or a home and a work address it's fairly tedious to have to list out those elements essentially twice so what we'll do is convert these into another type called address so to do that I'm going to declare my other type above the contact one this is quite important otherwise I won't be able to reference it properly I'll show you that in just a moment I'm going to pick another type called address and then say end type and then I'll quickly cheat and copy a set of these address elements into my address type I'll just take the word work away from the start of each of those lines so we have a basic address type what I can then do wonderfully is remove all of these individually declared elements in the contact type and replace those with sit to simple home address as address and work address I shouldn't use capital letters should night to make things consistent welcomed us as address using this is just fantastic now so if I create a new subroutine which I'm going to called tests conduct and I can declare a new variable I'm gonna call mine C as a contact just to make it nice and short and easy what I can then do is say C dot first-name and so on just in the usual way when I get on to the address information that I can say C dot home address dot and then I get a sub list the intelligence shows me the individual properties are all elements of the address of the contact it's just fantastic sings a name or number equals three it's going to be a string and so on and so on and so on so nesting types is wonderful that you can carry on doing this to more levels as well if you need to that's just a great way to save time if you're intending to use types multiple times just a quick note on what happens if you declare your types in a different order so at this point my subroutine runs quite happily I can use f8 to step through and everything works normally if I declare my type my dress type after my contact type however things don't go quite so smoothly so here if I attempt to step through - everything using the FH key I'll get a compile error immediately saying that my contact type is referencing another user defined type but because that wants to collide afterwards it doesn't work and another way another way that you'll see that is another clue is that when you're actually writing your code if I say C dot at this point I don't get any intellisense anymore and the compile error stops the entirely senseless from working properly so just make sure that if you're going to do this you declare your types in the sensible order the correct order so to Clair them in the sequence you're going to use them it doesn't seem to have any effect if you declare them in different modules so I could have happily if I cut my address type all together from this one and insert it brand new module and declare my address type in here this one will happily now still work if I use the FAQ step through so as long as if you have them in different modules it's absolutely fine if you declare them in the same module make sure you declare them in the correct order if you've enjoyed this training video you can find many more online training resources at ww-why Zelko UK

Channel: WiseOwlTutorials

Views: 42,481

Keywords: Microsoft Excel (Software), Visual Basic For Applications, type, type declaration, user defined type, data type, wise owl

Id: y9hnnfjTxUA

Length: 14min 49sec (889 seconds)

Published: Fri Oct 10 2014