Excel VBA Introduction Part 52.2 - Formatting Shapes

welcome to this wise owl excel vba tutorial this video talk about the basics of formatting shapes in VBA so it's a direct follow on from the previous video we talked about how to create and draw shapes we'll begin this one with a quick recap of those basic techniques before we start talking about the basics of formatting with how to change the fill colors of shapes we'll look at several ways to choose colors including using RGB colors the in colors and scheme colors explain the differences between those techniques and their penalties and disadvantages of each continuing the theme of colors we'll have a quick look at using tints and shades to darken lighten the same color that you've used we'll also look at how to apply pattern and color gradient effects to the fill effects of shapes we move on then and look at the basic reporting lines before we look at some of the slightly fancier morisot Taric techniques like glow effects reflections shadows and soft edges towards the end of the video we'll have a very brief look at some basic 3d effects before we wrap up with a quick look at you some copy formats between shapes and then set to default formats the shapes for the entire workbook so lots of you here let's get started okay well to get started all I've done so far is created a brand new blank Excel workbook and I've saved it as a macro enabled workbook called formatting shapes from here I'm going to head straight into the Developer tab and then the visual basic editor and then from there I'm going to insert a new module and our first job is to create a couple of simple subroutines that we're going to use a few times throughout the course of the video the first one is going to delete all the shapes that sit on sheet1 and then the second one is going to create a brand new simple rectangle shape so the first subroutine will create will be called something like delete sheet one shapes and then we still have to do this in the previous video but just as a very quick reminder one technique you can use fairly traditional where you can apply the same technique to every object in a collection is to write a for each loop so you begin by declaring the variable so in this case it will be dim s as a shape and then to process every item in the collection of shapes on G one we can say for each s in sheet 1 dot I'll closely by saying next S and then inside the loop I can apply the delete method to every single shape by saying s starts delete so that works quite neatly we saw that technique in the previous video there is a slightly quicker way to do this with shapes however if I just select that code and comment it out just the time being by hitting the comment button on the toolbar if you haven't got this toolbar displayed you can just right-click somewhere near the top of the screen and choose to view the edit toolbar and that will just edit out a block of selected code what we can then do is say sheet one sheet select rather than just selection that selects all which has Long's yes selects all the shapes on sheet 1 neat thing about that is that regardless of how many there are I can apply the delete method just to the selection object so I say selection doctor delete because I've selected all the shapes of course that will delete all the shapes on G 1 so that can work slightly quicker than looping through every single shape and deleting them individually it doesn't really matter for the purposes of this video which technique each use use but that's a quick simple way to get started a reciprocating that deletes all the shapes on sheet 1 okay next I'd like to create another subroutine that just generates a brand new simple rectangle shape so it's quite a new sub called drawn max angle or something along similar lines by descriptive Marcel rectangle properly as well and then all I'm going to do here is say sheet 1 shapes dot add shape just as we saw in the previous video the simple codes that allows you to create new shape objects with the basic add shape technique we have to first of all choose what type of object we're creating so in this case I'm going to say MSO shape rectangle then we need to decide in its position and its dimensions so first of all its position is control we're left so it's the distance in points from the left of the worksheet to the left of the shape so I set up some small like 20 and likewise the top let said that some like $20 so distance from the top of the sheet to the top of the shape width and height fairly obviously again in points so I'm going to give it a width of 200 and a height of 100 and that simple regime will draw one right angle I have to mention yes just a very quickly test that these two work I'm just going to restore down the VB editor window and arrange the screen so that I can see the top left-hand corner of sheet 1 and if I were to run my draw one rectangle routine months to make sure that it creates on and then run my delete sheet 1 shapes routine just to make sure everything gets deleted that looks pretty good to me so those two techniques work now we can get on with the process our formatting shapes let's start the section on formatting with a very quick look at the basics of changing the fill color I guess that's the most obvious change you can make to a shape in terms of formatting so let's begin a new subroutine that's going to be called something like change fill color and then let's make a call to each of the two routines requite so far I'm going to call delete sheet one shapes and I'm also going to call draw one rectangle so we have one object on the sheet that we know we can format I'm also going to declare a variable that's going to help it to reference the shape that exists on sheet 1 so knowing that it's the only shape that exists when I run this ability nor assuming that that's a case what I'm going to do is clarify declare a variable called stem as a shape and then I can reference that object once I've drawn it by saying set s equals sheet 1 dots and then refer to the first item in the collection so shapes one that just give me a slightly easier time in terms of wrestling with a new shape and modifying these properties just to tidy up a little bit as I'm going to delete this commented coder but the top just get might have a little more space and then hopefully that will make life a little easier and a little easy to show you the code case it from this point all I need to do is modify the appropriate property of the shape to change its fill color so I start by referencing the shape with my variable s now I can look for the filled property and then answer a little stop to see the intellisense with the list of various color properties so you'll see there are two properties that refer to color those back color and four color now in most Microsoft products you tend to change the back color to change the fill color of an object but when it comes to shapes on sheets like this in Excel it's actually the four color the world change will talk about what the back color is for a little bit later on but for now we'll commit stick with the four color we're gonna go for four color and enter another . to see yet more properties and there are three different properties in this list that will let me modify the fill color I'm going to stick with one called RGB just for the time being the two of the ones are going to encounter later on our objects theme color scheme color will mention those value briefly but RGB is almost certainly the one you're going to go with in the real world simply because it's the most reliable and it's got the widest range of possible values so technically speaking the RGB value is just a number a whole number between a value of zero and 16777215 I'll put the mellow comment in a moment so basically you can pick any number between that upper and lower limit and you'll get some sort of color so make it equal so one two three four five and let me just add a quick comment at the end of this that can be anything between zero to sixteen seven seven seven two one five so having just done that if I were to run that's a routine either by pressing f5 or clicking the green triangle button I'll see that I get a fairly horrible hideous shade of murky green that wasn't particularly good color to choose let's try something slightly different so that was just modified yeah the color number and then that one again not much less hideous but at least it's an obviously different color okay so there's one way to approach changing colors using the RGB property and modifying that to a number between zero and sixteen point seven million now although using the simple number provides you with a lot of choice of what color you use so to say sixteen point seven million different values a particular property it's not the most convenient thing to remember so there are a few more convenient ways to refer to a particular color that you might want to use so rather than using the number as we have here what I'm going to do is copy and paste part of this line and then I can comment out the existing one just keep it in there for reference now instead of using a number we can refer to one of the very basic visual basic color constants there were eight of these and they all begin with the letters VB so for instance if I wanted it to be red I could search for VB red a press ctrl + space to display the intellisense I'll find VB red in the list there and if I enter that in them run the subroutine by hitting the f5 key you'll see of course of the new shapes becomes red not a particular surprise now there are eight of these as red blue green yellow white black cyan and magenta but if you can't remember those then what you could do instead is remember just the name of the enumeration or in fact technically is a class that contains these color constants so there's a class that I called color constants and if you enter a full stop after that that will give you a list of all eight essentially the visual basic primary colors so you choose any of those in the list and then once you've done that you can hit f5 to execute it and it will use that specific color now although the color constant names are much easier to remember than a specific number of course you've only got H of them which is somewhat limiting when you realize those 16.7 million different colors you could use or set via the RGB property so there is a slightly wider range of names colors you can use as well there's a set of colors called the RGB color constants so in this case I'm going to just comment out that line again and then paste in what I copied previously and this time what I'm going to do is use the intellisense and look for a list of names colors that all begin with the letters RGB now again if you type in the letters RGB that will take you straight to the list where all these sit and every single one of these colors begins two letters RGB if you wanted to refer to the enumeration first like we did with color constants then the name of the enumeration in this case is XL RGB color held by . and then you'll see just that short list of colors so again you can scroll through this list and find the name of a color you particularly like there's an enormous number of these I mean my favorite one is RGB papaya whip so my favorite colors just they're the nicest one to say but feel free to choose any one they've all got some wonderfully flowery name's misty Rose for instance let's go with that one and not use that one before so if I were to run this everything this time again you'll get a different color your scam now although there are significantly more than just eight RGB colors there are significantly short of sixteen point seven million of them so how do you get access to all of the other colors rather than just remembering the specific number that the color you want well one week way to do this is by using the function called RGB which accepts a list of three different values and calculates a correct color number based on an amount of red and green and blue and two named RGB my favorite little demonstration for this that I do on training courses is for the NHS colors which are fairly easy to find with a really quick Google search so we just have a quick look at for MHS blue for instance or NHS brand guidelines or identity guidelines that link will pop that's fairly close at the top and if I click through into that page give me a list of all the acceptable NHS colors so the idea behind the NHS colors and like any brand guidelines any corporate identity is that the colors you use are or a specific definition of colors so it's not just a meal random blue this core NHS blue here is set by an RGB value of 0 red 94 green and 184 blue this particular mix of those three colors that generates this specific image as blue and the only just has a variety of different levels of these colors so it's dark blue and light bright blue with different RGB values and so on and so on and so on now if you're working for a particular organisation you may have already have these particular brand guidelines defined for your organisation so let's just say that I wanted to reproduce MHS blue for my rectangular shape in my Excel workbook so that would be an RGB of 0 94 and 184 so to make that work what I could do is head back into the visual basic editor and then again I'm just going to paste in what I copied earlier on and comment out the previous line and I'm going to make this RGB value equal to the result of the RGB function so I say RGB and then open up the set of parentheses I can specify the amount of red and green and blue now each of these three parameters accepts a number between 0 and 255 so there's 256 to the power of 3 colours which is exactly the same as the number of values for the RGB property so it was 0 comma 94 comma 184 for NHS blue closer parentheses point and then make sure I can see my shape and then run the subroutine again I'll see a new rectangular shape with NHS blue fill color now unless you've got a handy list of all your RGB definitions remembering the whole new list of RGB values and that much more convenient remembering just the raw underlying values so one neat trick to get around this is to create your own enumeration on your own list of constants so similar to those that we have for the excel RGB colors we've got flowering names like RGB mashiro's that's just a convenient name that actually stores a number time the seams if you want to find out what those numbers are a simple way to find out would be to use the immediate window so I headed up to the View menu at the top of the screen and choosing media window I can ask questions here so for instance if I typed in a question mark and then looked for RGB sees me RGB are spelled are should be probably RGB miss skiros and then simply hit enter then the value that's returned is the underlying RGB number that I could simply paste in directly rather than using the RGB mashiro's just to prove that I'm going to copy that number to the clipboard and then replace the number that I typed in myself earlier on just change the lines that our actives have comment at one line and uncomment that one and if I were to run that subroutine this at this point I'll get an an RGB mysterious colored rectangle so the trick to this then is creating your own constants that contain those values so let me just find out what the actual underlying number for NHS blue is if I copied this bit of code here RGB 0 94 184 and then I can paste that into the immediate window just after the question mark and then hit enter and the actual number for NHS blue is that number there I'm not going to pronounce it so one simple way to do that all I wanted was NHS blue and I didn't care about all of the other colors simple thing to do a B ticket to declare a single constant that contains that number so a quick simple way to do that will be at the top of this module I could declare a new constant so I can say Const NHS blue and the data type of this will be a long so long integer and then I have to make this equal to the number that's just in the immediate winner here let me just copy that number and then I've now got a new constant that stores the value of NHS blue so what I could do further on down in my procedure is let me create another s-stop Phillips for color RGB equals and then press control in space and not have access to MHS blue as a constant but if I were to run this subroutine at this point I'll get a single rectangle colored in NHS blue now the trick to this is really creating an enumeration that contains all of the possible colors you might want so let's have a quick go at doing that now let's create an enumeration that contains a few of these NHS colors so what I'm going to do first is remove the reference Const from the start of that line and what I'm going to do instead is above that line I'm going to create a new enumeration it's going to create a public in them and I'm going to call this one NHS colors and yes I'm afraid I'm spelling it the British English way rather than the American English Way apologies for that if you're watching in the States and what I'll do is give myself a couple of blank lines as they end in them and then I don't need to define the datatype for NHS blue all the valve using an enumeration are long integers so I can just remove the as long part and then simply say NHS blue equals that particular number now the great thing about that is that I've got a single constant in that enumeration but economists many more as I like and assign my own specific about these to each one so I just really quickly pick a couple of other simple ones from the same page that I looked at earlier on I guess we might as well pick Emma just dark blue and that's a 0 48 135 so let's just copy those values and then I can call this one NHS a dark blue and I can make that one equal to well first of all I need to know what the actual values are so I mean paste those values that I've just copied into the immediate window and change the slashes to commas and then hit enter at the end of that line and that gives me the number for NHS Darkly so I can copy and paste that and then let's just do one more quick symbol launch to dementia principles I've got something a little bit more interesting let's pick a completely different color are they just pink let's go adjust pink and you don't see that one use very often anyway let me just pink let's go back to the VB editor again and I'll paste those values into the RTD function in the immediate window and then hit enter excuse me change the forward slashes to commas first of all still be schoolboy error beg pardon and there we go there's an ally for NHS pink I will copy that to the clipboard I will call this constant NHS pink and then make that equal to the value I've just appended I'm going to close the immediate window at this point just so I can see more clearly on screen what I'm doing and again what I can do here is I can either refer to the NHS colors directly by looking for the individual NHS maned colours or I can refer to my enumeration NHS colors enter a full stop and then should take one from the simple shortlist like so let me just make sure I can see the excel will keep in the background then run the subroutine one more time there we go an NHS pink colored rectangle okay so those are the various techniques you need to set the RGB color for a shape but rusty were the color properties you use for various aspects of a shape let's have a quick look at something called the theme calor the object pinochle the next theme colors you may have encountered before just through using Excel or even any of the other office products themes or document themes are designers that could have collection of different colors and font styles and shapes files that you can apply quickly using a single click so for instance if I were to select this shape and then just head back to the regular Home tab in the ribbon if I looked at the drop-down list for fill colors here what I'm seeing is a list of theme colors you'll see it's listed as steam colors let me just choose I'm going to be choosing one here this sort of this bright blue color blue accent one now the idea behind this team colors it's just one of a collection of colors that can be modified by changing a property on the page Layout tab in the rhythm if I head to the page layout page then there's a section I called themes there's also a separate one just for colors so themes contain stuff like not just colors but also fonts and other formatting effects colors is purely just for colors so prints if I clicked on the colors drop-down this is a list of different color settings I can use you should be able to make out in the background now actually using this live preview feature as I hover the mouse over different items it changes the color to match the accent one color and a particular color theme the same should be true for the actual the full themes as well you might not be able to see this in the background let me just oh you can just about make it out in the background there we just move the shapes even see it a little more clearly so for the things that I hover over the different themes you'll see the color change as well so a theme colors are a reasonably good idea allows you to quickly change the visual appearance of an entire document using a simple click but as long as you use the theme colors in the first place and there could be also a danger of that of course if you choose a theme color using refill color tool thinking that it won't ever change then you may ruin to run the risk of finding it that color has changed if somebody applies a different theme so you've seen colors with caution but just to very quickly show you how they work in VBA terms let's switch back to the VBA editor and I'm just gonna add a couple of extra quick lines there's no real reason to create another new subroutine for this let me just come out the last time you wrote I'm going to give myself a bit more space as you can see there's a little more clearly people and clangs right at the bottom there and then I just I'll just have a quick comment using theme colors and then let's say something clicking seems like s dots of film dot for color dot object theme color I can make this equal to and the cool thing about this is that I don't have to choose a specific number it's got a list of actual named colors from this list so you still rarely run when I use the drop-down this I chose accent one for the background color so the accent one color is this one if you hover the mouse over each of the colors in the list it will show you what the particular named colors you start cycling - that's exam 3 then I've got the standard background 1 back text 1 and I should be background 2 and so on and so on so you see which color corresponds to which name by hovering the mouse over the colors in the grid let me just quickly choose a simple one let's go without let's go with accent accent 3 just assuming slightly different and then if I were to run a subroutine I'll end it with a new shape from on a particularly exciting color admittedly but if I did go back to the page layout page and looked at either the colors all the themes you'll see the color the shape I've just selected is changing so that's the basics of using themes and theme colors now as well as being able to apply a theme color to the for color of a shape you can also change the overall theme or indeed the set of colors use for the document using code as well the key to this is knowing where the files are stored that contain the theme colors so depending on which version of Office you're using the file type might be slightly different for you but for me on my particular machine using Excel 2016 at this point if I open a Windows Explorer window by holding down the windows key and pressing P then browse to the root of my C Drive and then look for Program Files x86 and then in there what I want to do is look for the Microsoft Office over so much scroll down far enough I'll find Microsoft Office and by double-clicking to there there should be a different file a folder for each different version of Office that you've got installed or at least have had installed previously but we ignore all of these individual office installations and just go head straight to the root folder so double click on the root folder there then there should be in their directory called document themes and that will correspond to the version of Office that you're currently using so office 16 is the same as office 2016 the other members don't necessarily match up however so M so office 15 was for office 2013 office 14 was for office 2010 and so on and so on so document theme 16 and there's a list of all my individual theme colors so let's say for instance I wants to apply the eye on theme I picked up long so it's got the shortest name obviously what I would need to do is they meet another folder path and the file name so I'm just going to copy the directory path from the title bar or the address bar of the window so just click into there and press ctrl C to copy and then if I had back to the visual basic editor I can say something like this workbook dots applies theme and the model would then need to do is pass in the full file name of folder path to the theme I want to apply so that's passed in as a string so inside this set of double quotes I just paste in the path I've just copied let me put this onto a separate line as well so you can see it a little more clearly without me having to scroll left from right on the screen then I can type in a backslash and say I on th M X so having done naturally if I were to execute a specific gene and I'll end up with the same shape that I've just create using an accent 3 color but this time our than that boring old gray I've got this very murky hideous yellowy brown in color and set and that's the eye on themes accent 3 color so we should be able to save or go back to the homepage and look at the fill color tool that that's the same as theme 3 or beg pardon accent 3 in the eye on theme and I should I want to switch this back to the original theme I could either do that manually back on the page layout page and choose the office theme or of course there is not obviously an option for that as well in the in the folder so the office theme theme file tha max so I could do that encourage bars let's change this so it goes back to the office team instead so the office theme it's not case instabilities given practice to match case so there we go so we were to run that one again at that point I'll end up with the same boring bald gray shape that I had earlier on now one of the features of applying a theme is that it doesn't just change the colors of the document it also changes things like fonts and other formatting effects if it was just the colors that you were interested in changing rather than applying an entire theme what you could do is modify the theme color scheme so let me just comment out the two lines that I've used they're doing really math because it would just apply the same theme anyways what I could do instead of applying an entire theme is I could just change the theme color now to do that you need to know the folder path and the file name of a theme color file so from the same root folder document theme 16 as a separate theme colors folder inside there is just a bunch of XML files containing definitions for individual colors so let's choose let's use again the shortest means one the Wankel red heart go with our red dot XML to apply it the code is ever so slightly different you begin with a reference to this workbook against I save this workbook followed by a full stop and then you can say theme dot theme color scheme and then you can say apply the load method to that so the load method has a single required parameter which is the file name so they enter a space underscore so I can continue this on the next line open and close a set of double quotes and then switch back to the windows explorer window and then copied the the path to this folder and then switch back to the VBA editor and paste that path in and then type in a backslash and then red dot XML so same idea again if I were to run this subroutine at this point I'll end up with same hideous murky yellowy brown color sadly we can see hopefully here on the out on the theme colors button and tool on the page Layout tab I've got the red team applied and once I've alien demon is that horrible murky green yellow a brown color I should have picked something I'd read file that would have been more interesting anyway hopefully that's enough to give you the idea now one small problem I got with this in color is I don't know where the file is stored that holds the original office themes if I have a quick look in the theme colors folder although I've got a reference to office 2007 to 2010 I don't know where the actual one just called office is to be sure I haven't really looked through that hard because I'd be very unlikely to do this personally in the real world so what I'm going to do just to cheat is switch the theme color back to office Manuel shouldn't click on the office theme document colors tool on the ribbon and make sure I got the office overall theme applied there's although I've already written code to do that so there you go as we add the basic principles of using themes and theme colors okay just to look at one final property that you can use to modify the colors of the shapes I'm just going to comment out some of the lines of code in this subroutine and games so I'm going to comment out the line it changes it to an object theme color and then comments out the lines that modify the theme color scheme the very last property to its news if I just refer to my s variable again then the fill property then the for color property and then finally in there the option called scheme color now skin colors are a little bit old-fashioned you'll hopefully recognize it if you've ever used a version of Excel from Excel 2003 or earlier you're probably going to recognize the colors were allowed to use here so the idea behind a scheme color is it's just like the RGB color it's just a number again but it's a much shorter range of colors much smaller range is that only a color between 0 and 80 in this case so I say scheme color equals and Millis 837 just for the sake of argument and then I would I were to run that savaging at this point that's scheme color 37 as it turns out you'll see if I change it again I run the Surbiton again I'll get a completely different color if I enter a value up to 80 then that's acceptable if I try to extend past that value I'll get a runtime error so the specified value is outside of the range so it's a very limited set of values of course only 81 different values 0 to 80 so much more restrictive than the RGB values the idea behind the scheme colors is they're related to the older versions of Microsoft Excel so Excel 2003 and earlier could only use a very limited palette of colors the palette could be different for each different workbook but if you wanted to see what those colors are and why they're stored how quick look in the file menu of Excel and then through the options list if you then go to the save page of the Excel options dialog box there's a little button on there somewhere called colors and if you click onto that button you'll see a list of colors now doesn't show the full list or object or sorry schema colors although some of them are duplicated as you'll see in a moment going to produce a routine that lists out all the schema colors for the particular workbook the idea would be let me just very quickly see I can't quite demonstrate this just yet if I were to run this subroutine again I just cancel out of this dialog box and it's okay and then I was a rimless subroutine again and let's change that back to something like 42 for example if I were to run this one at this point suspect that kind of fairly horrible minty green color if I switch back to excel shows file options and then went to the save page and chose colors again if I could identify where that minty green color was there like Emeritus so I could select that color and then choose and modify it so it's modify its using the obviously different size bright pink color and hit OK and then ok again and okay one more time we've always to run the subroutine again at this point to change the color to color number 42 it's now a completely different color so basically it's get away get around the fact earlier versions of excel only had a very limited set of colors for each workbook they gave you the option to modify the limited set of colors for each file so not quite as as powerful as the RGB values but still potentially useful to know today's what we're going to do with this particular workbook we're going to run a subroutine that loops through all 81 of our scheme colors and produces a different shape for each one so it's create a new subroutines down below it's going to be called something like list scheme colors I'm going to begin this one by declaring a whole bunch of different variables so let's do something quick and simple let's let's declare an X variable a shape variable says also dim s as a shape and then I want to declare a variable that's going to allow me to loop a set number of times so simply a loop counter variables I'm going to say dim I as integer but it needs a for next loop to do this I'm just going to keep track the positions of the case we're going to end up with 81 different shapes added in the same subletting this time and I don't want to office it on top of each other so I'd like to calculate the positions of my shapes as I add them in so I'm going to say dim X as integer comma Y as integer and that's going to allow me to keep track of the app from my opinions of the left and top positions effectively I'll call them X and y just for convenience I'd also like to keep track of the width and height of the shapes just in case I wanted to modify those later on I'm going to have a couple of variables dim s widths as integer and then also s height as integer s height as integer there we go one last one I want to keep a variable that stores how many columns of shapes I'm going to create so what I want to do basically is quite sort of a grid effect similar to one that I just showed you on the options dialog box to list out all the different colors I'd like to keep track of of how many columns I'd like to create so I'm going to say dim s calls also as integer one side or not I'd like to make sure that I've deleted any existing shapes on the worldsheet and that's going to be more important again if I were to run this one again I want to remove all 81 shapes so let's say I make a quick call to delete sheet 1 shapes now I'm going to set a couple of default values so I'm going to say s width equals 20 and s height equals 20 I'm going to set those to say male you'll end up with a grid of square shapes to human so I squids and s height equals 20 and I want to start with eight columns of boxes I'm going to say s calls equals 8 now I can start looping the 81 times I need to loop to produce a different box for each skin color so I'm going to use a for nextly to do that I'm going to say for I equals 0 to eighty that's the range of scheme colors I'm allowed to use and so that I don't forget later on I'll give myself a couple of blank lines and say next I the first thing I'd like to do in each for each iteration of the loop is to calculate the left and top positions I'm using I'm using X as the left position for the shape and Y as the top position of reads shape so I'm going to calculate X by saying x equals and then I'd like to wrap up in a couple of parentheses couple sets of parentheses I mod s coals so that individual part of the calculation does the mod operator or modulus operator returns a remainder of dividing one number by another so what this is going to do is divide the number of that I'm currently on in loop by the number of columns I want to get so I divided by so I will be 0 to begin this will be 0 divided by 8 for the first time to the loop and then 1 divided by 8mm 2 divided by 8 etc it will give the remainder of dividing that number by that number what I can then do is multiply that by the width of each shape so I can say s widths so for instance the results of the first iteration of this loop when I is 0 the result of that calculation will be 0 so the left-hand position of each shape the first time de loop will be 0 sorta to the left hand edge of the sheet the next time round the remainder of dividing 1 by 8 will be 1 x vs widths which is 20 so the position of the next shape will be 20 along so that's that's the width 20 points from the left and then the next iteration of the loop will be 2 divided by 8 and give me the remainder which is 2 multiplied by 20 which will be 40 so essentially all you're building up a row of shapes each one is sitting right next to the other we can use a similar sort of technique to calculate the top positional it's a slightly different calculation so I'm going to do here say y equals then I'm going to use the int function to make sure that I return a whole number I'm going to int and then instead of parentheses I divided by s calls and then if I close the parentheses I can then multiply that by the s height so a similar sort of principle it divides the value--the of loop counter by the number of columns I've got and returns just a whole number portion of that so return many decimal places and then simply multiplies that by the height of each shape to effectively work out the position of each shape from the top okay so having done that what I can then do is create a new shape and set the position and dimensions of that appropriately so I can say set F equals sheet one dots and then side shape and size of shape on one side is going to be another MSO shaped rectangle and then the left of the shape will be positioned by X but some of the shape will be positioned by Y the width will be s width and the height will be s height so I fill in all those values the final thing I need to do is change the four color of the shape to be equal to the skin color the four color to be equal to the value of I so having the maps divided to say s dot fill top four color scheme color equals I and that's it so it's not a lot of code there as long as you understand the basic maps behind these two calculations that's that's basically the core part of the procedure so if I were to maybe set a break point and then just hit the f5 key to run up to that point then what I can do is use the f8 key to begin stepping through so x and y to begin with these should both be set to zero so X is zero and Y is zero so the new shape that gets created will sit a position zero zero top left hand corner and if I change the fill color that will be equal to scheme color zero and then the next iteration of the loop so this time X will be 20 and then Y will still be zero and then actually box it in next door and then actually will be colored I use number one so that skin color one and so on and so on and so on so you could carry on using the fhg step through or if you just get bored you can remove the break point by clicking onto the add their little brown circle and then press f5 to run or blow through to the very end and we'll end up with 81 different shapes each one colored in with a different scheme color now I appreciate that was quite a lot of time and effort to show you something that in the real world in reality you're fairly unlikely to use but I couldn't not cover I guess if I hadn't covered it there'll be loads of questions at the bottom of the video saying what on earth is a scheme color and I'd have to explain it anyway so at least now you know that you don't need to use it unless I suppose maybe you're still supporting legacy workbooks in which case you may find that it comes in handy from time to time I guess the biggest limitation is of course the limited range of colors and by default a lots of them are duplicated as well and it's quite a lot of effort to change them so you could use it if you really wanted to but I don't think that in the real world you will I think is far more likely you're going to be using just the RGB colors maybe theme colors if you want that sort of flexibility to change the overall appearance of your documents one last quick little things look at with the basic fill colors before we move on some more exciting things like patterns and and color gradients and things how you change the field color of a shape do you have no fill whatsoever so the simplest way to achieve that is just to set the fill property or the visible property of the fill property to be off so very quickly just a quick subroutine here that says something like clear fill and then I'm going to say make a reference to also it make a call to delete sheet 1 shapes and then I'll make a call to draw one rectangle and I'll declare a variable that can hold the reference to a shapes of dim s as shape and I'll say set s equals sheet 1 stock shapes opens in parentheses and then refer to the number 1 again so having done that I can now say s dot fill and dots are visible by must all visible property with a V rather than a C and then make that equal to MSO false microsoft office false so if I were to set a break point on that line and then hit f5 to run all the way up to that point and then just use the f8 key to step through just so you improve it at the moment it does have a fill color by hit f8 to execute that line you'll still now has no fill color is completely transparent so something quickly easy to demonstrate but it's worthwhile knowing how you can clear the fill for a shape now should I want to turn the fill back on again that's pretty straightforward the same fill effects are still in a place so in place it's still got the same fill color so before I stop running their subroutine if I just change this back to MSO true and then drag the yellow arrow back up to that previous line and then just use the f8 key to step through that line again you'll see the fill color becomes visible again retaining all the same properties if you add last time so whatever color you duration reset so that's the basics of making the fill invisible and visible again okay the next thing to have equipped the cat is changing the brightness or the tint of a color in your shape so just to mention the idea of tints or shades if I select this shape that I've got in the worksheet at this point in that back to the Home tab in the ribbon in Excel but look at the fill color tool you know that each of the main colors in the theme color has a set of different values presenting essentially like a percentage fill of that particular color so these referred to as as tints and shades now there are properties of the shape that you can modify in code to generate those tints and shades so switching back to the BB editor and in fact let's have a good look back at the NHS website to see why this might come in handy you'll see this sort of thing is part of many corporate branding guidelines if I scroll down far enough there we go so there's a second air about using tints so the base color sitting limits up there but there are various different shades of basically percentage values of the colors of the base colors so there are two properties of a shape fill that you can use to modify that there's a brightness property and there's a tint and shade property and they both work in fundamentally the same way so I switch back to the VB editor again what I'm going to do is make a copy of the last subroutine that we created actually the entire thing let's copy the whole thing and if I paste it in down below I'll just modify the name of the subroutine so I'm going to call it color tints or something along those lines then I can remove the stop fill valve visible and what I'll do instead is I'll say with s dot fill vocal and we need a with struct here so I'm going to say with s dot fill dot for color and then I'll make sure I add in the end with and then let's have a quick look at the the brightness property first of all so we need to give this herb this shape of base color first of all let's set its base color two dots RGB equals going to my NHS blue color that I created earlier on when I'm now going to do is modify its brightness by saying dot brightness equals now with the brightness property you don't get any help here whatsoever the brightness property is just a value is just a number and the number is between minus 1 and 1 so so zero is the base color negative numbers make that color darker and positive numbers make that color brighter so just to domestic the absolute basic principle if I said something like let's say brightness equals minus one and then I'm just going to copy that line actually and paste it in a few more times and I'll change it so it's minus 0.5 and then I'll make it zero and then I'll make it positive zero point five and then finally positive one so we'll step through this just so you can quickly see the changes as we go through ok so just having a quick break point on this line 1 and setting the color to be NHS blue making sure I can see the worksheet in the background if I use f5 to and all the way up to that point and then begin using the f8 key so that's saying that guess blue the brightness sets and minus 1 will make it essentially black zero brightness effectively then zero point five so it's a sort of dark shade of NHS blue and then zero makes it of the original base NHS blue color and 0.5 makes it'll 50% lighter and then brightness one will be white so that's the basics of setting the brightness property you can achieve essentially the same effect using the tin tan shade property as well so I'm going to do here is I'm going to copy and paste that set of five lines so where I was referencing the brightness property and then just paste those in down below and then what I'm going to do is comment out the brightness lines there and then I need to change the bright word brightness to the word tint and shade a simple quick easy way to achieve that I think is to use a find replace so if I select that sort of lines arrow and press ctrl H on the keyboard to find replace I want to look for the word brightness and replace with tint and shade now I never would just hit the replace all button there making sure I've got that set of text selected rather than the entire document and so five appointments should be made tint and shade equals okay so once again I'm going to run up to the point where I just before sets the NHS blue color if I hit f5 to run up to that point and then use f8 to step through from there so tint and shade minus one well as integer it's black and then that's fifty Cent darker that's the base color 50% lighter slightly different when you tint a color rather than changing its brightness you might notice a slight difference that the tint isn't quite the same as increasing the brightness of the color but again if you put the tint up to a value of one then it essentially goes to white again made you also have to be slightly careful with using brightness and tint and shade in the same procedure so for example if I were to just do one comments the brightness equals one line and then I were to run this subroutine all the way up to the breakpoint you can get five then use the f8 to keep step three from there if I set the brightness equal to one that makes the shape it's actually white so I change the tint and shade to minus one which makes it black and then distant shade thickness and darker is essentially 50% darker from white so I get a sort of pale gray color rather than a dark blue color the infinite eight zero is white because that's the tinted and shaded or the non tinted and shaded version of the original NHS blue color set to a brightness of one so like waiting to change 0.5 will make the white 50% brighter or 50% tinted and then one will make no the white super white I don't know it doesn't change the base color basically so just be slightly careful of using brightnesses and tints and shades each Kanab and if X on the other of course so to make you aware of that in reality again I'm not sure whether you're likely to use that you can generate all the colors you need using simple RGB values so each of these colors it would be setting there with tints and shades were just more values in the range of RGB colors and if you can establish what those specific numbers are you could then just simply add those to an enumeration then you've got quick simple access to them rather than having to worry about whether you've got the correct shade each time so let me just remove the break point from that one and then we'll move on to something slightly different now so far all we've done is change the four color of the shape that we've created but you may have noticed much earlier on that there was also a back color property as well currently if we were to change the black color property you would have no effect whatsoever on the shapes we're creating and just to very quickly prove that let's just head back up and I'm going to make a copy of my clear fill subroutine I'm going to select the entire superteam and copy that and then paste it in down towards the bottom of the module let's just change the name of the subroutine so that it says something like change back color and then let's just change the s dot fill dots and then we can say back color dot you've again got RGB object theme color and scheme colors are the same three properties for modifying the color again I'm going to go for RGB and I'm going to try to make the RGB equal to let's say make it something obvious RGB hot pink now the emotion obvious obviously different to the original colors we're using so having them matched if I were to a rimless of receding I'll end up with a shape whose background color hasn't change it retains the default color or the shape for this particular document theme the idea behind a black color is it only has an effect on shapes which display more than one color and usually you do that using some kind of cut and fill so before we can modify the the back color we need to change the pattern of the shape let's couple of different ways you can do that but quickly and simply what we're going to do here let's create a with a with block again I'm gonna say with s dot fill and then I'm going to say dot that colored RGB equals RGB hot pink on a separate line numbers again and end with I'm going to set a for colors are going to say dot for color let's make it very obviously different but for color to RGB equals let's bill for RGB in lime-green that'll look beautiful together and then before I do all that I'm going to change the pattern that's used on the shape so if a dot patterned and then I can pass in a value to the patterned method so you deserve there's a list of MSO pattern types we can use so some of these are just a different sort of percentage fills so they sort of dot shaded to try to shade in the different shapes I'm gonna go for a slightly more interesting one you can find one in another list there let's go pursue moon like I mean there's a spear in here there we go I'm so passing the sphere feel free to try out some different ones if you like but anyway having done that if I were now to change well let me just let me just run up to that one with the breakpoint if I hit the f5 key turn up to that point and then if I hit f8 at that point you'll hopefully see that you've already got the pattern applied with a back color set to white so the four color is that original blue color so I change if I hit f8 again now that should change the little circle shapes and their little spheres to green and then I change the back color that will change the white background color to pink you just about make that out I should have picked a different pattern should night to make it a little more obvious anyway you're thrilled to see this and you can test obviously many many different patterns using the other list let's see what else we've got just something very quick and simple let's give her a large grid that shouldn't be a more obvious change by run that one again there we go so you can clearly see the pink color in the background and hopefully you make out there crosshatch shading there the grid color is in that lime green okay now another way to display more than one color on the same shape is to apply a fill gradient rather than a fill pattern such as Emma so it had those work let's make a copy of our change back color subroutine and let's paste that one in and I will change the name of this one so that it says I just realize that I can put your misspelled change back color call it Chang tobacco linen mind and the change back let me spell that from properly at this point apologies for that let's change the name in this new subroutine will correspond to something like use one color gradient can you a clearly we might do more than one color shortly so we'll stick with the same basic setup will delete the existing shapes and create a brand new one and then we'll use a with block again I'm just going to get rid of the black color yeah technically set the black color for a one color gradient I'm also gonna get rid of patterned fill effect and I'll changed it so we've got a basic four color let's go to something with more subtle than lime-green this time let's go is something like RGB RGB blue RGB blue violet let's say sort of mid purplish sort of color okay so having them that's what I can now do is apply to the fill property I can apply a one color gradient method so I can do that by saying dots one color gradient and then type in a space and you'll see there's a bunch of different parameters to fill in so there are three parameters first of all the style of gradient let's go with MSO gradient diagonal down let's say let's do the first one I guess feel free to experiment with these various options they're all reasonably well described so that's the style then the variant now different gradient effects have looked to four different variants and you simply set one of those by putting in a number between one and four so for a diagonal down that could be going from the top left or the top right so I believe for this on the jeus two variants I can choose either one or two person let's go with grading number one I don't have another comma I givens enter the degree now the degree here is a little like the tint and shade and brightness properties the range of values you're allowed to enter here for the degree is between 0 and 1 so 0 would fade my RGB will be violet color towards black and one would fade g/b blue-violet towards white so let's enter about I'm looking for a value of one so it fades blue violets towards white and then having done that if I were simply to run that subroutine there's not much point stepping through it or applying gold that properties in one go if I hit the the Run button I'll end up with a new blue violet faded towards whites with a diagonal fill one color gradient beautiful okay so setting a one color gradient is fairly straightforward and as it turns out so asserting a two color gradient there's uninventive Li titles two color gradient method so switching back to the VB editor let's just make a quick copy of our use one color gradient subroutine and then we can paste it in just down below and I guess we better change the name of the subroutines let's call it to use two color gradient just to be as inventive as Microsoft have been and then we'll replace the one color gradient method here with a new line in the with block that says dot two color gradient now it's a slightly simpler method to use you don't have to fill in a an intensity you just need to choose a style and then choose a variant for the style so let's go with I'm gonna go with a horizontal gradient this time and I'm going to set the variant so let's go with number two next thing we need to do is set a back color so the two colors are both the four color in the back color and it is important what you're devious in by the way I'm I'm making sure to set the back color after I've sets the gradient so back color RGB equals and let's go with RGB lime green it was so beautiful and successful last time so let's go with that so having a nut if I were to run a sub regime I'll end up with a purple to green so is that what that hideous as those okay I've certainly seen what I've created worse trust me just as I was like that it said that it is important to set the back color after you've set the gradient I'm going to cut that back color line and place it above the gradient line and then run that sub resume again something in a brand new shape and you'll see that the back color is masked at this point so although I've set the back color the gradient overrides it so do be slightly careful with is make sure if I just undo that cut and paste make sure that you do set the back color after you've set the gradient if I run that one again we'll end up with what we expected now it is possible to use far more than just two colors in the color gradient but suddenly there aren't any more inventive leaning methods like three color gradient etc the way you apply multiple colors to a color gradient is by applying something called a gradient stop so to begin with what I'll do is switch back to the VB editor and then let's make a copy of the use one color gradient subroutine it's important to start with a shape that already has at least one color ingredient so I'm going to start with the use one color gradient let's just modify the name so it says something like use multiple gradients and then I want to essentially start the shape so that it's got a base background color of white so to do that I'm going to set the for color to be RGB white let's go for RGB white and then I'm going to use a vertical gradient this time so rather than gradients diagonal down I'm going to change that so it's MSO of gradient of vertical and I'm going to use variant number one and then I'm going to use the value one as well and essentially I'm a fading I'm going to fade white into white the end result of this routine at this point if I were to run the subroutine is I just end up with a white colored shape but it's important to have set a gradient so that I can now add in something called a gradient stop so inside my with block what I'm going to do now is refer to the gradient stops collection so I'm going to give myself blank line they're going to say dot gradient stops and I'm going to say dot insert that will allow me to create a brand new gradient stop there's a couple of properties to fill in are a couple of parameters to fail infamous the first is the V RGB value so what color I want to use for the gradient stops us that's going to be an RGB color either as a number or an RGB constant or using the RGB function then the position of the stop which is entered as a percentage notice is a decimal value that represents a percentage of the way along the tape so what I would like to be able to do my first example is I would like to insert a color a gradient stop of red that sits 1/4 of the way along this shape so at 0.25 for the position with an RGB value of RGB red let's go for RGB red and then the position of that will be 0.25 so just with that having been done can I just run this everything to damage rate what I woman will do so you'll see at that point it's phase from white to absolute red the specific RGB red body there and then it fades back out again towards the end of the shape to whatever color it was left with so that makes sense hopefully so you can have as many of those gradient stops as you like so the next one along here is about halfway along the shape I'd like to see their color Gradius towards screen so let's switch back to the VBA xur and I can just say dots gradient stops again so I'm going to say stop gradient stops dot insert I'm going to set this on to the RGB green followed by a position of 0.5 and then let's just go the whole hog - let's see blue as well so doc gradient stops dot insert and when the next one will be RGB blue 3 colors RGB of course are so I'm doing this and then say 0.75 ok so having done that if I were to just run that entire subroutine at this point I'll end up with a le skimpiest looking shape to be brutally there but hope that demonstrates the principle of how you can insert various gradient salts you can see all these replicated on the if you selected the shape and head to the format tab and then look for the shape fill option you might even want to just choose from the from the little album little dialog box button at the bottom right hand corner there to have a look at your fill colors and you can hopefully see there three great ease of stops that I've added represents it in this little dialog here on the right hand side okay that's probably more than you ever wanted to know about filter colors the shapes I hope you found some of those techniques useful there are quite a few other formatting options of course that we could apply so let's just move on a little bit and look at some things like shape lines and some other of they're interesting shape effects so they shape outlines next I guess let's get back to the VB editor and I guess we'll just create a copy of one of our simpler routines let me just go back gotta to use one color gradient subregime and if i simply copy that entire routine and paste it in and down the bottom will change its name so it's called something like line form I think I guess and then have a great look at some of the basic things that we can do so let me just get rid of the little segment inside the width block I won't get rid of the width block entirely because what I'm going to do here is refer to the line property of my shape so I can say with s dot line now inside the wit block there's a variety different things I can do just like with the filled colors I can change the line for color and back color so I say dot for color that will allow me to change the main line color black color just like for a fill color allows you to set patterned lines with different colors for the back color I'm going to go before color for now is going to say dot floor color equals and doesn't know let's go with let's go with RGB red let me let's make it nice and obvious of course I've got to choose what type of color I'm applying as well so just like with the four colors and back colors I can choose either RGB or object theme color or skin color but I think we've established by now that RGB is probably the most sensible want to go for or at least in this video it's the most sensible ones go for so goes da da da g b equals RGB red as a couple of the basic things I can do as well so I can apply a dash style to the line and I guess the sensible thing to do with the formatting properties with lines is to have a quick check through the names of the properties they're all relatively straightforwardly named and you can you can probably tell most of these do just by the names of the properties so I just change a couple of extra ones let's go for - style which we'll set to be something like let's go for some kind of dotted line let's go with MSO line line - I should just go with the top option there make it nice and quick and simple and we can also change the weight of the lines of safe dot weight equals and this is as a value in points again I believe so let's say I set the weight to two point five points okay so having the nut will run the subroutine and we'll end up with a shape with a red dotted two point five point thickness line alternative way to outliner shape is to use a glow effect now you can use those either in conjunction with a line or instead of a line so I just changed the the supreme in fact let's make a copy of this subroutines or you can demonstrate the basics of a glow effect all these the code behind it let's call it glow formatting and what I'll do in this case is I'm going to set the line to be invisible so I'm going to write a separate line here it will say S dot line dots visible equals MSO false that will turn the outline off and then rather than using the the Whitlock to format the line I'm going to use a whisk block to format the glow effect of the shape so I can say with s dot glow now again there's a variety of different things I can do with the glow effects let's change the color first of all and as ever I can set the color to be fire RGB colors or rocket theme colors or scheme colors again I'm going to use RGB colors let's go with making it equal to RGB hot pink and then I can set the transparency of the glow effect so let's say dr. transparency equals now this is known as a percentage so if I said it to be 0.25 that's 25% transparent and mattes at the radius so how far out from the edge of the shape the the glow effect glows I suppose if you like that's at the radius to be equal to let's go for let's give us 15 points against I'll be 15 points glowing around this blue rectangle shape so having done all that power to simply run this up regime there's my lovely pink glow effect another simple effect you can apply to the border of the shape is some illegals a soft edge so to demonstrate that less again make a quick copy of my glow formatting regime and then I can change that so that it's called edge formatting something quick and simple and I'll send the line off again so I'll make the line invisible but this time rather than setting all these properties for the glow in the whipped lock I'm going to change that so it's s dot soft edge and inside that is a couple of simple properties I can change let's go with a type first of all so is a soft edge type equals and this is just a case of choosing bombin lists so let's go with was go with the first one in the list I suppose and we can always change that if we don't like the effects afterwards and then we just need to choose a radius so sort of how far in in points does the soft edge e effect attach the shape so I set the radius to be equal to let's go with six points this time I see what effect that has so you want to see the pink glow effect this time but we should see the edges of the shape soften so sort of fades into view rather than that harsh boundary between math and the worksheet less room there's a between Omega as a soft edge effect for our basic rectangle shape yet another effect you can apply is a reflection effect so let's just copy the previous subroutine one more time and paste it in down below give myself a bit more space let's change the name of the subroutine so that is called reflection formatting and then once again we'll take away the code from the middle of the with block and also the dot soft edge part the width line instead I'll say with s dot reflection this time now there's a few different properties to fill in first of all there's a transparency effects kind of just like with the the glow effect remember I said this transparency to be 0.25 s as a percentage value again we can also set the size of the effect this is done evaluates so the size I'm going to make it equal to 75 then I'm going to set the offset to fat using points as well so the offset is the distance from the actual shape itself that the reflection begins I'll set that to be quite smaller set the offset to about 3 then I can also apply a blur effect so I say let's add a blur of of 10 so this is done to sort of make be the reflection less sort of sort of sharp I guess what Socrates blurring gates of course is going to make it less sharp so if you wanted to modify me these values to see their effects please do feel free otherwise I'm just going to run this subroutine and we'll see a slightly blurred reflections sitting just below the shape with about a three-point gap between the shape and I start the reflection itself one last simple type of effect you can apply to a shape is a shadow effect so once again let's just copy the previous regime and paste it down below and then change the name as a subroutine so we'll call this one shadow formatting and then let's check take the card out of it with block and change dot reflection to dot shadow there's quite a few different properties to change with a shadow so first of all let's choose a a particular shadow style so we say dots file equals and then go for an outer shadow and I'm going to choose a type of shadow so as I dot type equals this is just cheating from elisa preset shadow type so I'm going with one inventively called number 25 just because I've tested this one previously that's the one that I want to feel free to test this CRS can tend to choose as many different shadow types as you like and so you get the one that you actually want and then there's a blur effect we kind of isomers had a blur of five just like with the reflection effect week just like that then there's also an offset fetch just again like the reflection effect now with the reflection you can only offset in one direction but with a Chevy you can offset in both the X and the y direction so there are two separate property survivors and offset pecks let's set that to be something like eight and now I'm going to do an offset Y and set that to be equal to eight as well and then a couple more to go let's have a transparency effect again just like with the reflections a transparency equals zero point two five and then last of all I can choose a color for the shadow so we without using the four color property since a dots for color dot and then once again I can choose either RGB or theme color or skin color but let's go with RGB and I've like my shadow to be basically like a pale bright color it's actually kind like a shadow color now ways to generate great colors using RGB values the simplest way to do that I think is use the RGB function and then set every value of RGB to be exactly the same number so I've set them to be zero comma zero comma zero that would end up as completely black if I set them to be 255 comma 255 comma T 55 that would be completely white and any value in between those as long as they're all the same will generate a shade of gray so yes yeah the author like to you are you in a loosest possible sense of the word what's an Amy L James yeah there aren't just fifty shades of gray and there are slightly more than that according to VP a at least so let's go with our sort of a middling gray color I'm going to with 150 comma 150 comma 150 and there we go okay so having done all of that I won't bother stepping through this one you can easily do that in your at your own measure I'm going to run this entire submitting and that will end up with my slightly offset slightly blurred middling gray shadow effect okay so there is one final set of formatting options I'd like to very quickly look at if difficult to look at them quickly because there are simply so many settings to apply these are the 3d effects you can apply to shape sins and modern versions of office so just to manually show you how this works in the first place if I switch back into the VPLS I soak back into Excel and select my shape and then head to the format tab in the ribbon looking at the shape effects button here and half way along the format tab obviously we looked at the basics of shadow effects reflections glow effects soft edges we've also got these bevel effects which provide a 3d elements to the shape and also 3d rotation now there are an astonishing number of options and properties you can set with that with these 3d effects if you have a quick look if you choose the 3d options menu from the absent from the list there let's see a whole big long list of different properties and was just the wildering list of these I'm going to go into a huge amount of detail but just show you the basics of how you can apply these 3d bevel effects and 3d rotation effects at least you've seen the basics of the code what we're going to do is write a simple little subroutine just as we do value on copy pasting what we've done previously so switching batches of evyatar let's copy our shadow formatting regime I'm going to copy that and then just paste it in quickly and just modify the name so that it's called 3d formatting now knowing that you can't begin a subroutine with a number you will expose this 3d formatting with the actual literal spelling of the word and then let's just take away all of the code from inside the width block and let's change the width block so this as with s dot 3d okay so I'm just going to apply a bunch of different properties here and then step through the code so you can see the effects of each one let's let's start with a depth property thing as a dot depth equals and then I'm going to set that to be equal to a value of 1 then I'm going to set console width property so I'm going to cons or width equals 1 as well and then I'm going to set a preset material so as a preset material excuse me get there eventually a preset material equals and I'm going to choose a MSO material on mapped to as it turns out I'm using bonds that I've previously used which I know generates some kind of interesting looking effect so you can see something going on and then I'm also going to set a preset lighting values also dot preset lighting and crystalizing equals and I'm going to go with a lighter rig right room so as a second option in the list okay a little bit of rotation as well so let's say a dot increment rotation I'm going to with the x-axis first of all I'm going to do that by 25 points and then I'm going to set an increment rotation Y so increment rotation Y I'm going to set that negative 5 and then dot increment rotation Z it's three different axes as you'd expect with a three-dimensional shape that increment rotation Z let's set that fingerman tip by five a couple of extra things to do with a bevel which describes the border of this 3d shape let's set the add the bevel top types first of all to be equal to MSO bevels circle and I'm gonna set the devil top inset for something that I know exactly what all of you sing with you I just know that generates some kind of basic effect to demonstrate to them bevel top depth I'll set that to be ten as well and then level bottom type nearly there so that's the type I'm going to set that to be like button equals to n MSO bevel circle again likewise as with all the other properties do you feel free to play around with these to generate some other effects recording macros to definitely have 3d effects is a pretty useful technique as well just so you can see exactly what's been changed when you've changed properties manually so goes bottom in set equals and finally dot bevel topping set equals a big bottom bottom gaps polka dots bevel bottom depth equals 10 as well okay so without going into much detail as I say there are lots of different ways to get exactly the properties you want and I think we could probably fill an entire videos worth of individual 3d effect properties maybe that's worthwhile doing in the future but for now all I'm going to do is set a quick breakpoint on the depth line then hit f5 to and up to that point so you can see clearly there is no 3d effect at this point if I hit f8 to begin stepping through you'll see these effects gradually begin that to be applied and the rotation occurs is long until we really get to the bevel properties that things really starts to become three-dimensional broadly the properties are important to get this effect we've got the highlighting and the depth effect you can see there hopefully just about and I said carry on going you'll see that all these properties are being applied and then eventually is ended up with some kind of three-dimensional h2 looking shape it's not the most impressive thing in the world I admit I haven't spent a huge amount of time doing it but you'll hopefully be able to see again on the format shape panel on the right hand side of the screen if you have that displayed that all the properties setting code here have also appeared and I've been changed in the dialog box in the panel on the right so that's giving you a bit of a little bit of a preview of the sorts of things you can do I'm sure you can generate much more exciting and interesting things and I just have done there with that simple demonstration but there's a nice little starting point for you okay so we're nearly there for this video we've looked at quite a variety different formatting techniques so far what I'd like to do just for the very last part is look at a couple of techniques you can use to quickly apply formats to shapes so first of all want to look at how you can copy for my finger packs from one shape to another so to demonstrate how that works I'm going to switch back to the VB editor and then I'm going to scroll back up to the subroutine in which we created a two color gradient and I'm going to copy the entire routine and that I'm just going to switch in fact our going to create a new module let's insert a new module and then paste that code in and then let's just change the name of this one so it says apply multiple formatting or apply many formatting options I changed my mind speed lazy probably many formatting options so I'm going to switch back to module 1 by double clicking on it and then I'm just going to take some line formatting and then go back to module 2 and paste that whisp lock in and then back to module 1 again and scroll down to the glow formatting then take the width block from there you really get the idea by this point and give us a couple of done lines and then back to module 1 and the edge formatting and then two more to go I just pasted that one in I've got the reflection and the shadow skills go so let's just copy that reflection code and back to module 2 and paste that in and then finally that's a module on one more time and copy the shadow effect stuff as well okay so you'll agree that's a reasonably large amount of formatting options all being applied to a single shape if I were to run that single-sim regime I'll end up with a fairly hideous horrific looking shape there let's just imagine that that wasn't as horrific as it currently looks and let's say you want to copy those formatting options to a new shape that you've just created here's how you can go about doing it so let's create a new subroutine just below the one we've we've pasted together I'll give myself a bit more room and killed quite a nice routine called copy formatting and then I'm going to declare two variables just to make my life slightly easier I'm going to say dim s1 shape and then also dim s2 as shape so I'm looking particularly amountof I'm going to set s1 to be equal to 1.1 so that references the existing shape on the sheet then I'm going to set s to to be equal to sheet 1 dot shape and dot add shape and I'm going to create an MSO rectangle doesn't have to be a rectangle I'm associate angle big one it doesn't have to be a rectangle but it seemed like the most obvious choice after using rectangles so far the left I'm going to set to be 20 the top I'm going to set to be let's say about 250 and then I'm going to refer set the width to 200 and the height to 100 okay now having done that I'd like my new shape to take on the except for the same formatting properties as we had the previous one so to do that I can say s1 dot pick up so it's almost like using the format painter button if you're familiar the format painter you can click on a shape and then click format painter to pick up all the formats then click onto another shape to apply them so s1 dot tick up and then s 2 dot apply it's as simple as that really really straightforward so just to step through I'll set a breakpoint in then hit the f5 key up to that point so I've got my new basic boring blue shape I'll use f8 to pick up the formats and then f8 again to apply them I've got another equally beautiful pink blowing purple and green reflected shape I do apologize for the hideous appearance of that shape I hadn't realized how horrible are we going to look anyway probably that's enough to give the idea about copying formatting now imagine if you can and I appreciate that is quite difficult of the appearance of this particular shape but imagine if you wanted every new shape that you do in this workbook to have the exactly the same formatting options as one of these two so to make that work let me just my just gonna manually delete one of those two shapes and then what I'm going to do next is write a new subroutine that sets the default shape formats in fact what might be easier to do is just copy and paste via the existing one and I can place it in and I'm going to change this one so it says setting default formats something along those lines okay so the important thing here is that I've set a reference to shape one first of all which is the only shape that currently exists on that sheet now they've got a reference to it what I can do is say s1 dot set shape default properties and it's as simple as that what that means is now any new shape that I draw will have the exact same formatting properties as the one that I've just created yeah so let's just prove that by changing the shape type for the warm milk raise less draws and like I know an MS o shape smiley face more anything else more interesting than that and we don't need to apply any formatting up into this new shape at all if I just run the subroutine we're not copy-pasting things I'm so sorry we're not copy-pasting will pick up in picking up and applying formats any new shape we draw now from this point on we'll have those exact same formatting property sets that's even true on a new worksheet if I were to create a brand new blank worksheet called sheet two and then switch back to the VB editor let's just really quickly make a quick copy base of this regime and then let's just take away the code that sets a reference to shape one entirely and that variable and let's modify the name of the the routine let's call it using default formats and then of course make sure that I'm adding this to sheet two rather than sheet one and if I were to run that one now we'll see that this shape yet again has those six formats so yeah every new shape that I draw now in this workbook will add those eggs taking shape default formats okay the very laughing just to quickly cover in this video if you didn't want to bother creating your own particular shape default formats or your own particular shape styles you can always use some other preset shape styles and sets if I selected my my fairly hideous deformed shape and head to the format tab in the ribbon there's a whole bunch of built in shapes files you can apply zoom the shape Styles menu so this to the correspond or I'll take their colors at least from the theme color you've applied so if wanted FLE slightly more so good-looking smiley face than the one I've just created then yes something like applying a shape style will be a sensible choice you can apply shake Styles really really easily just using a very simple method so if I just quickly deleted that shape from sheet to manually switch back to the VBA editor now I'm just going to use the same subroutine I've just written here I'm going to say s2 dot shape style equals and then simply select one from the list sent you to pick one a random number thirteen as it turns out if I use the f8 key to step through find that first of all my shape appears with its fairly hideous formatting and never hit pepperidge again it will apply a slightly more sober looking shape style so yeah you got all the built-in shake styles there as well okay so I think that's probably enough for this basic video on formatting shapes we've got a few more things to talk about the shapes primarily we've got text to deal with them added any text to any of our shapes yet and that's a slightly different topics you'll have a separate video or four text I hope you found this one useful and your eyes don't hurt us after some of the formatting applied there thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 15,281

Rating: 4.9749999 out of 5

Keywords: vba, visual basic for applications, excel, microsoft, shape, shapes, format, color, colour, fill, effect, gradient, pattern, fillcolour, fillcolor, line, formatting, shadow, reflection, glow, three d, 3d, three dimension, wise owl, default format, set default, copy format, format painter, paste format

Id: 4ynjc4rFR0c

Length: 81min 17sec (4877 seconds)

Published: Mon Feb 13 2017