Excel VBA Secrets: Master Pictures Tutorial + FREE Cheat Sheet

have you ever wanted to work with pictures in Excel using VBA hi this is Randy with Excel for Freelancers and in today's quick tutorial you're going to learn everything you need to know about how to insert position size and adjust pictures in VBA and we're going to cover it all live and from scratch in this training so you won't want to miss it it's going to be a great training I'm going to cover everything you need to know on how to work with pictures in this VBA basic training so if you have no VBA experience don't worry we're going to go step by step and we're going to get started right away I've started with a great cheat sheet here in our Excel file in fact this Excel file is absolutely free to download you can just look for the word download in the video description enter your name and email and I'm going to get that set over right to you I just ask a few things all you need to do make sure you are subscribed to our Channel click the notification icon Bell like the video and comment below let me know your thoughts ideas and feedback I create these trainings for you each and every week for VBA basic on the weekends and comprehensive application development every single Tuesday so I'm super excited to bring this training to you we're going to go over everything you need to know in these trainings so also if you do like the channel want to support us and you're kind of new to VBA or even if you've been working with VBA I've got an incredible ultimate developers VBA Library it's got over 500 macros in it and it's quickly easy to search for any macro you want you can select on a macro copy it or you can create an auto hotkey shortcut and that means just inside your VBA you just have to enter that auto hotkey and automatically your macro will write itself so this is the ultimate developers VBA Library I'm going to include the link down below so you can check it out I'm sure you're going to love it all right let's get started on this training because I have so much to share with you inserting pictures in Excel using VBA is an important part in fact in almost every single one of the applications that I've developed we've worked with pictures in some way shape or form so learning this and understanding how to work with those pictures is a really important tool in developing your own applications in Excel all right so we're going to get started right away I've got this cheat sheet you can refer to it we'll look at it a bit but basically what I want to do is I want to get right into the code and start writing some code so that you know how to insert your own pictures directly inside VBA so the first thing you want to do is go into your developers now if you don't have this developers tab open it's no problem anywhere on here you just need to click customize the ribbon and it's going to take you right into the Excel options where you want to make sure you've got developer selected once you do select it it'll become visible here then you can click on the Visual Basic here now you can also use a shortcut if you don't want alt f11 is the shortcut that'll get you into the VBA and it's going to look something like this I've got an AI tool pack that's an addin that might want to check out but that's the only other workbook so I've got this workbook open here now what we want to do is we want to start writing some code the best way to do that is inside a module we don't have a module available here but you can right click on here or you can click here and insert module here or you can do it anywhere on here insert module so it's kind of the same thing we're going to insert a module and it's going to take us right here now the first thing we want to do let's work on sheet two cuz there's nothing on this sheet and that's a good place to work it's a nice template for what we're going to do so what we want to do is we want to write our first sub routine and what I want to do is I want to be able to browse for a picture somewhere on our computer I want to grab the entire file path leading to that picture and I want to place it into a cell so we're going to write a sub routine we're going to call this sub browse for picture okay we also need to Define some variables for that so the first one is dimension the pick file as a file dialogue it's going to allow us to create that dialogue that allows the user to actually browse for something so we need that we also need that file path the full file path in a string variable so we'll do picture path as a string so that's important okay now what we want to do is that file dialogue we need to set that so what we're going to do is we're going to do set the picture file is going to be equal to now we need to set it up as to what type what are we creating it's going to be a file dialogue so we'll do application. file dialogue that's the what we want to use we're looking for a file not a folder picker right this would be for a folder this would be open and this would be save as I want a file picker so we're just going to go that we need to pick a specific file in fact it's going to be a picture file now what we can do is we can work with that picture file so with pick file and you see my endwith come up right away and if you want to make sure that you spell it right like if I don't spell it right and I put a period here nothing's going to happen if I do spell it right put a period here the intellisense is going to come up so we know that we've got the right one so my endwith came up because I use Auto hotkey to automate things and you can have that if you get Auto hotkey is a free software you can just browse for it so I've used that to program it it so here's what we're going to do the first thing what I want to do is that file dialogue that's going to come up I want to give it a title so I'm going to use tile is going to be equal to we'll call this select a picture and that's just the title on the dial it's going to come up now if there's any filters in that like let's say a file type like maybe a Word document or Excel or in our case a picture document if there's any previous filters we need to clear them out we're going to create our own filter so the first thing we want to do is clear any filters out so what we're going to do is do filters which is this one right here do clear so that clears any out now we need to add our own filter so do filter and why are we adding a filter because we don't want the user to browse for any type of file we're really focused on pictures so we're going to add a type now if we take a look we see the help as description as string so let's add a description we're just going to put select a picture keep it simple then what we're going to do and the quotation marks comma now we want to know the extension as a string so since it's a string we're going to put in the quotation marks and basically we're looking for file types so we need the extension of those file types and that means any file that ends with so we're going to insert the asteris let's use PNG okay that's one how about another one let's do one for jpeg so we'll do asteris do jpg comma and we can also do another one but you get the idea you can add as many as you want in here Dot and then jpg and we'll close the quotation marks next up also the position we want is one so that's going to add the filter there so now what we want to do is we need to consider if the user has not selected a file if they do select a file there's something called oh let's do one more thing first do allow multi select in this case we do not want the user to multi select so we're going to do false that means they're just selecting one picture next up is the one that we want to consider dot show if they've selected something if they've selected the okaya file the value is going to be -1 so if it's anything other than ne1 that means the user has not selected a picture they've clicked cancel or something else so what we're going to do is if do show nea1 is the correct value if they've selected something so it does not equal -1 then what do we want to do just simply exit the up very good so that gives us in case they have not selected something but what if they have selected something I want to take that so let's do the picture path is equal to and where is the full file path it's located in the selected items that first selected item as long so it's the index number the first one full file path to the picture now where do we want to put that let's put that inside one of our ranges so we'll do sheet two that's the one we working on range let's use a2. value is equal to that picture path very good so that's all we need we've got our sub routine written and let's say we want to create a button for that now we can run it here but why don't we just create a button so I'm going to copy this so just copy that then what I'm going to do is I'm going to insert a shape and that just means we're going to tie this to the button and I'll enter a shape here and we're just going to use let's drop it down here and I'll give it a format that's consistent with our theme although it's absolutely not necessary and we're going to call it browse for picture so we've got our button here now we want to tie that macro to that button so to do that we are simply going to right click it and also insert that macro so right click assign the macro here and we only have one macro available and click okay and now when we click it it's going to bring up that file here as we see the file doll let's take a look it says selected pictures on the title so we see that now we have again select a picture and we have PNG jpg and JB so those are the files it's going to look for so we have our filters set up here so we can select a file so let me select this file and we're just going to click okay all right great so now we've taken that file path and we've placed it in A2 and that's exactly what I want all right so now what I'd like to do is I'd like to write another macro that's going to actually insert that picture into a sheet so let's do that so we're going to scroll down here and next up we're going to insert a picture so we're going to do sub insert picture so with this thing I also want to Dimension the picture path as a string and also what we're going to do is we're going to dimenstion the pick Let's do pick as a shape since we're going to be using it in this case as a shape we could use a picture but I like to shape so we're going to create that picture and Define it as a shape so the first thing what I would like to do is fill this string with the actual picture path so to do that picture path is equal to sheet 2. range a2. value assign picture path to the string variable so now we've got that inside the variable and now what do we want to do I want to check to make sure that it is an accurate picture path if it's inaccurate it's going to create an issue if I try to insert it so what I want to do is let's do something like if directory picture path VB directory and we'll test this out equals empty that means it's an incorrect path then message box path is incorrect El message box path is correct that's a good start so we'll just see to make sure that we have a correct path and we'll test that out so what I'm going to do is I'm going to copy this here that's the macro and I'm going to create another button so I'm just going to use contrl D to duplicate that button let's click that duplicate that and then once it's created I'm just going to put insert picture insert and we're going to assign that to the macro that we just created so we're going to right click and go to assign macro and then insert picture nice spelling Randy let's respell that right insert okay there we go that makes sense and then we need to reassign that macro because we change the name so now what I would like to do is I would just like to know message box path is correct now what I'm going to do is I'm going to change the path I'm going to make it incorrect so let's put another number in here that does not exist and I want to make sure that we get the message m box says path is incorrect and that's good so we see that our test is working good we will reset that path to the correct so we know we've got a correct path and since we have a correct path we can then move on so if it's incorrect I'm simply going to exit sub now that we know it's correct what we can do is we can move on so I want to use sheet two and we're going to insert this picture inside so we're going to use pictures do insert and then we want the picture path so what is that picture path it is is simply the picture path and that's it now notice intellisense it doesn't come up in this case picture it's not here right but it does exist pictures do insert so if you're looking for it you're not going to find it and then picture path so I just wanted to show that too so that's simply going to inserted now let's take a quick look and see that okay we see that the picture has been inserted this is an AI generated picture by the way it's really beautiful for a treehouse so I've insert a picture there's no name it's given it a general name called picture 45 what if I want to give it a name so I'm just going to delete it now I want to assign a name to it because if we don't assign a name it's going to be hard to work with so we can just do that dotname equals my pick so we're going to run this macro one more time we can also just click this button here and it's going to insert the picture and we see now that it's given it a name my picture so now that we have the picture now we can focus and work with it so again let's delete it again but now what happens if we try to create it again it'll create it but it's going to create two so now what we're going to do is while it's still there I'm going to set the picture so we have a variable here as a shape so we're going to set that so that we can work with it set pick is equal to and we're going to call it sheet 2 do shapes and then what is the name of the shape my pick very good so now we've set this variable so that we can work with it let's say we want to do message boox pick. name it's going to return the name of the picture inside a message box so what we're going to do is we're just going to delete this for now and I'm going to run the macro again and you see it says my pick here and the picture has been inured so we have the picture that got displayed and the message box so we know that this variable is correct as a shape and we can work with that very good so now what I would like to do is if I run this macro again what it's going to do it's going to create duplicates here so now I've got two pictures and I really don't want to do that because I only want to work with one picture at a time and I don't want to have two pictures with the same name because that's going to create problems the Excel will not know which one to work with so what we want to do is we want to delete it so let's delete both of them manually for now and we're going to go into the VBA and we're going to delete it but it's going to create an error initially because it doesn't exist so we can do something like sheet 2. shapes working with a shape in case it exists my pick. delete okay so that's just simply going to delete the picture if it exists so I'm going to comment this out just for a moment I'm going to run the macro here it's going to create the picture we can get rid of that message box we don't need that anymore so now I'm going to run the macro let's do f8 so we can see that it's going to run slowly as it goes step by step so we'll bring this down here and we're going to run it so we're going to use f8 and that's going to step through our code so we're stepping through the variable gets assigned it's checking that it does have a directory we see the picture path the directory is correct so we're good there sheet two we're going to delete that picture which is correct we create a brand new one and we give it a name very very good so now that we've done that but what if it doesn't exist if I delete it here and I try to run this macro one more time we're going to see that on this line here it's going to create a bug and the reason the bug is because the picture does not exist so we only want to delete it if it exists now we can test if exists we see it's an issue or what we can do is we can capture that error and so we can do on air resume next and then we could do on air go to zero and basically what this says it says if this line results in an error just skip it we're skipping it and then we're resetting the airs and so now if it doesn't exist it's going to automatically work okay so basically we're inserting the picture and we're deleting it if it exists so that's kind of helpful as we move through it so now we've got this picture inserted but we really want to work with it and I would like to set the size and I would like to put it in a specific position that could be helpful let me just delete that and let's say I want to put it in a specific size and I want to put it in a specific location so let's take these cells right here let's go through J all the way through n and then four to let's say 19 so these are the cells that I want to put the picture in and I'm going to give this a unique color here so we can distinguish between that and I'll just merge and center that but that's not that important so let's say I want to put the picture inside this box here and I want it centered how would we do that well the first thing what we want to do is we want to set the left position and the top position based on what is in j4 so let's give that a try so we're going to go down here and we not going to focus on that so with our picture we can do many things with it and again if you want to make sure you've got the variable right you put in the period down here and all the intelligence shows up so we know we're on the right track so I'm going to set the left position now what is the left position well I want to base the left position on a very specific cell address the left position of a cell I want to put it right here inside this cell actually let's do this I'm going to unmerging Center this it's kind of helpful so I want to put it right here j4 so I want to use that position so how do we do that equals sheet 2. range j4 do left that's going to put in the left position set left position now what I want to do is I also want to set the top position also based on that it's actually in essence when we're setting the left position the four could be anything it could be 1 through 100 it doesn't matter the row it only matters that column so we could easily do something like this so could do that that's fine too so I just like putting in a single cell cuz it's the same both ways and then I could just copy and paste it I mean I could do just something like this do top is equal to sheet two oops too many equals sheet two and then this is the top position so in this instance any column wouldn't matter we're only focused really on the top position of the four so this is going to set the top position great so if we were to run this macro here it's going to put that top position right at j4 you see it was inserted right there and that's exactly what I want but it's too big I want to actually size it accordingly I want to size it less than this and I want also want to put it in the middle so let's first focus on the size and then we'll focus on the centering so we want to set both the width and the height however what I don't want to do is I don't want to distort it I don't want anything like this you know and I don't want anything like this so I don't want the aspect ratio to be distorted I want the aspect ratio of the picture to always be maintained so how do we do that we can lock that down so we do something like lock aspect ratio equals true so that is going to lock aspect ratio so now once you lock the aspect ratio if I adjust one the other one's going to automatically adjust as well so the aspect ratio is going to be locked and that's exactly what I want so now what we can do is we can do something like if do width if the width of the picture is greater than the height all we need to do is set one then we can set the dot width equals let's say 200 so that's going to set that width so we can put it at 200 else we'll set the height to 200. height equals 200 perfect so let's run that macro now and we're going to insert the picture let's delete that and we'll insert the picture okay great I like the size so it's perfect but now what I would like to do is I'd like to get it centered so how do we do that let's focus on the width if I know the width width of J through n if I take that entire width of all those columns and I subtract out the width of the picture that's going to get us the difference so basically it's going to get us this difference so all we're focused on let's say right about column n so then what I want to do is I want equal distance on both sides so all I need to do is take that difference and divide it by two and make sure that we're adding that difference onto this section here so that means if it's here so let the fold width if I divide it by two we're getting a and I'm going to put half of it on the left side and half of it on the right side essentially we're going to take that difference and we're simply adding it to the left position of column J so let me show you how that would work so we have the left position but we're adding on to that so we're going to do plus because we want it moved over to the right and what I want is the width of the column so sheet two in fact sheet columns J through n do range The Columns are what J through n now we want the width of that width but we also want to subtract out our width of the picture minus the width of the picture so we're going to take all of that and we're simply going to divide it by two very good but the only thing is we want to make sure that we set the height and the width before we position it right if we're determining the width and the height we want to make sure that we're first setting the width and we're first setting the lock aspect ratio so that means the first first thing that should happen is we're locking the aspect ratio then we're setting the width and the height then we are going to set the left position and then the top position so first we're locking it then we are adjusting the height and width then we're simply going to set the left position plus the width of J through n minus the width of the picture dividing that by two so let's give that a try Okay so we're going to run that let's just move the picture and then we're going to insert the picture very good so we see that it is now centered horizontally now what we need to do is use the same idea but Center it vertically so how are we going to do that well we've got the top position here it's in the same principle plus but in this case what I want to do is I want to take the height of all the rows from 4 to 19 I want to deduct the height of the picture and divide by two so let's do that plus sheet 2. range 4 through 19 do height so the height of that minus the height of the picture and then divided by two and that's going to center it accordingly so again we can run it from here and let's just move it out of the way so we know automatically that it's going to be okay great so we see now our picture is centered vertically and horizontally what else can we do we can actually assign a macro to that picture let's say when I click on this picture I want something to happen maybe I want to open up this picture in the default application used for the computer so we can create a macro to do that and so let's write the macro now and then we're going to assign the macro to the picture so let's do something like this sub open picture and we're going to Dimension the picture path as we have done before as a string and we can simply copy that what we've done here because we know that we have done that so we're simply going to assign that picture path to what's in A2 and we're going to check to make sure it exists so we can do that now once we have done that we know that we have a correct picture path we can do something like this workbook follow hyperlink and then picture path and what that's going to do it's going to follow that and assume you were clicking on it and it's going to open up the default application or program that your computer is using to open that picture great so that's exactly what we want to do if I were to run this right now it would open up that application so it's working correctly but what I want to do is I want to assign this macro to when I click on shape so what I'm going to do is I'm going to copy this now we see when I hover over this there's no hand right no macro has been assigned to that but I want to do it now I can do it of course like this but as we delete the picture it's not going to stick what I want to do it I want to do it through VBA and we can use the command called on action so on action meaning when the user clicks we want to do something equals and then then I'm just going to paste in the macro name assign macro to picture very good so now when we rerun this macro we won't see much happen all we see is a flash but now when I put the cursor over we see that the hand comes up knowing that a mackerel has been assigned if I click on it it's going to open up that picture okay very good so we see how we can do that there's other things that we can do once I've inserted the picture if I take a look at this button and I slide it over here we see that that button is behind what if I want to change the order of this in other words through VBA what if I want to send it to the back like um send to the back but I want to use VBA to do that it's called the zorder and we can do just that let me just show you one more time just so we understand that when we create a new picture the newest shape is always going to be on top so anything older is going to be on the bottom what if I want to change that I want to move this to the back I can do that with the Z order so we can do something like dot Z order and then we have different options bring forward bring to front of text bring to the front but I want to send all the way back so we're going to MSO send to the back that's the one I want so now when we insert a picture and it's already been sent it back you're going to see when I drag this button over it's already above the picture so we can use the Z order to set the order of the shapes so that can be very helpful very good there's other things that we can do with that so we can open picture now what if I've got an existing shape and I want to take that shape and I want to fill it with a picture I can do that as well so let's say we create a shape here and I'm just going to create a rounded Corner shape here and I want to put a picture in there so I've got my shape here now let's give it picture name now we can do this real time or we can do this with VBA so let's just call this fill picture shape so we've given a name I'm going to copy that name and press enter so that we know that when we select on this we see that we've got the right name okay so what I want to do is I'd like to take the shape and i' like to fill it with a picture now we can do it automatically if we want to format that shape if we were going to fill it without using VBA we would use a picture or text fill and then we would insert and we'd browse for that but I want to do it through VBA so how do we do that let's go inside here and we'll write another macro we're going to call this sub picture in shape very good so we have it so we're going to focus on that we're going to Dimension just as we've done before let's just copy this it's a bit quicker so we're going to copy all that to make sure that we have that picture path in that so it's very very easy so we can do sheet to do shapes there's a specific shape what shape is that that is the shape name that we have here called Phil pick I'm just going to Simply copy that and we're going to put it directly inside Here and Now what we're going to use is fill and what are we filling it we're filling it with the user picture now what is that user picture that user picture is what we're going to call picture path so that's the user picture and let's take a look at that and when we run that macro we see that the picture has now filled in the shape shape so we can also use an existing shape or create a new shape and then fill that shape so that's kind of a nice feature if you always have a specific shape in a specific location and you want it filled with different pictures you can use the fill user picture to do that so this has been a really cool training we covered a lot inside here because we're able to show you how you can create a picture browse for a picture specific type of a picture we can put the picture path in a specific cell we can assign up of course a variable to that we can then insert a picture check in to make sure that it's accurate first deleting any pictures that exist with the same name and trapping any erors that might come up before that we also can then learn how to lock the aspect ratio once we insert it and rename that picture working with that picture using a shaped variable locking it setting the width of the height setting the left position centering the picture within a range of cells and centering it both vertically and horizontally assigning a macro to that picture and of course checking the Z order and like wise we also take in an existing shape and then filled that with a picture this is a great training don't forget to check on our cheat sheet I've got all that here available you can download this workbook for absolutely free using the links in the description look for the word download I do appreciate your continued support don't forget to subscribe like this video comment below as I respond to each and every one of the comments if you want additional training I've got an additional training each and every week on our patreon platform and also YouTube members so make sure you jump on that thanks so much and we'll see you next week

Channel: Excel For Freelancers

Views: 5,528

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, pictures in vba, excel vba, visual basic for applications, excel pictures, insert picture in excel, pictures, pictures in excel, shapes, excel shapes, vba picture, macros, master picture

Id: FtQg4zBPvcw

Length: 28min 30sec (1710 seconds)

Published: Sat Apr 27 2024