1 00:00:00,850 --> 00:00:06,130 In this video we're going to cover how to pull data or information from your Excel file into your VBA 2 00:00:06,130 --> 00:00:11,070 code and how to write data from your VBA code back into your Excel file. 3 00:00:11,210 --> 00:00:15,640 And there are a few different methods we can use to accomplish this reading and writing of data and 4 00:00:15,640 --> 00:00:18,480 they all operate based on a specific worksheet. 5 00:00:18,490 --> 00:00:24,910 What that means is that if we follow through our object hierarchy here we should start with our application 6 00:00:24,940 --> 00:00:30,790 which is Excel moved to our workbook which in this case is example and then select our sheet. 7 00:00:30,790 --> 00:00:36,220 In this case will mainly be working with data sheet down here and then we can start to interact with 8 00:00:36,220 --> 00:00:38,490 the specific data that we want to touch. 9 00:00:38,590 --> 00:00:43,900 And as we start to identify information for our code or write information back into the excel file it 10 00:00:43,900 --> 00:00:48,100 becomes really important that we've specified each step of the object hierarchy. 11 00:00:48,100 --> 00:00:53,890 It's important to ensure that VBA knows the specific cell on the specific sheet in the specific workbook 12 00:00:53,890 --> 00:00:55,340 file that we're dealing with. 13 00:00:55,510 --> 00:01:00,550 Otherwise VBA will default to assuming you're looking at the cell on the active worksheet which may 14 00:01:00,550 --> 00:01:06,220 or may not be the correct worksheet especially if your B VBA code is bouncing across several different 15 00:01:06,220 --> 00:01:10,210 workbooks or several different worksheets in order to work properly. 16 00:01:10,270 --> 00:01:15,910 So the first thing we're going to look at is this cell's method of writing data the cells method is 17 00:01:15,910 --> 00:01:20,250 something we've actually seen a couple of times now when we created example macros before. 18 00:01:20,320 --> 00:01:24,350 So now we can get a bit more detailed on the cells method of data. 19 00:01:24,460 --> 00:01:30,580 Cells are of course the individual cells on worksheets so we identify them by giving their row number 20 00:01:30,580 --> 00:01:33,550 and then their column number kind of like coordinate system. 21 00:01:33,580 --> 00:01:41,520 So if you come back to our data sheet here if I'm interested in referencing let's say Cell C to this 22 00:01:41,520 --> 00:01:52,650 would be wrote to in column 3 1 2 3 so I would reference the cell by saying cells 2 comma 3 and that 23 00:01:52,650 --> 00:01:56,020 would represent this highlighted cell we see on the screen here. 24 00:01:56,070 --> 00:01:58,710 So now we're gonna go ahead and open up Visual Basic. 25 00:01:58,800 --> 00:02:06,320 I go into my developer tab and then going over to Visual Basic and we can see I have some previous modules 26 00:02:06,320 --> 00:02:08,130 and previous code that I've written in here. 27 00:02:08,270 --> 00:02:12,560 So I'm going to go ahead and close out of this open module and insert a new one 28 00:02:15,680 --> 00:02:17,520 and we'll create a new public sub. 29 00:02:17,840 --> 00:02:19,070 We'll call this data testing 30 00:02:22,570 --> 00:02:29,470 and now I'm going to go ahead and type cells to three dot value. 31 00:02:29,470 --> 00:02:34,780 So again this dot here this little period means we've identified what we're working with and we're ready 32 00:02:34,780 --> 00:02:37,360 to move further into our object hierarchy. 33 00:02:37,540 --> 00:02:42,680 In this case I'm interested in what the value is of cell to 3 what's written in that cell. 34 00:02:42,770 --> 00:02:44,300 What data lives there right now. 35 00:02:44,660 --> 00:02:49,270 And we're actually going to overwrite that value with our own value by setting it equal to something 36 00:02:49,270 --> 00:02:59,250 like this is Cell C to like so now if I go ahead and shrink these windows like so and I come over here 37 00:02:59,280 --> 00:03:06,600 click into my sub and I click Run we can see myself see two was overwritten with exactly what I told 38 00:03:06,600 --> 00:03:07,820 the VBA to write in there. 39 00:03:07,830 --> 00:03:14,640 This is cell C to now we can actually reverse this logic and pull data out of our excel file and into 40 00:03:14,640 --> 00:03:18,060 our VBA code using the same cells method. 41 00:03:18,060 --> 00:03:24,450 So if we create a variable here and we'll say I just I'll just create this little e x variable for example 42 00:03:24,690 --> 00:03:35,720 and I'll say that's a string I can set E X equal to cells to three value and hit enter. 43 00:03:35,850 --> 00:03:42,990 And now when I run this the variable E X should actually pick up what value is in C to at the time that 44 00:03:42,990 --> 00:03:44,010 we run this. 45 00:03:44,010 --> 00:03:49,100 So I'm going to go ahead and output that in a little message box and we'll just message box directly 46 00:03:49,100 --> 00:03:50,520 what that variable is. 47 00:03:50,570 --> 00:03:55,820 So I'll go ahead and run this code and we see we get a little message box that pops up and says This 48 00:03:55,820 --> 00:04:02,690 is cell C 2 which is exactly what we would expect seeing that we assigned the value of the cells to 49 00:04:02,750 --> 00:04:09,520 three to our variable X. We'll go ahead and give us a little bit more space here. 50 00:04:09,680 --> 00:04:14,470 Now the cells method of interaction is really helpful for identifying specific cells. 51 00:04:14,480 --> 00:04:17,380 And we use a lot in loops and if statements. 52 00:04:17,390 --> 00:04:22,700 So this is probably the most common interaction type you'll use for specific data in VBA. 53 00:04:22,700 --> 00:04:27,620 And there are other things you can actually do with cells like select to select a cell or we can do 54 00:04:27,620 --> 00:04:30,370 things like clearing contents or changing the font around. 55 00:04:30,370 --> 00:04:37,040 So for example let's add a few lines and we'll keep working with cells 2 3 which is Cell C. 56 00:04:37,400 --> 00:04:44,120 This time we'll put dot font and now we know we're looking at the font properties of Cell C too and 57 00:04:44,120 --> 00:04:49,310 specifically we want to look at something like the size of that font and you can find out what all of 58 00:04:49,310 --> 00:04:54,200 these properties are through the object library or through Microsoft's help site but they're generally 59 00:04:54,200 --> 00:04:58,740 everything that you would expect to be able to change with the font given cell. 60 00:04:58,770 --> 00:05:04,820 So we'll go ahead and set this equal to something like 14 so that we can see a change now let's also 61 00:05:04,820 --> 00:05:12,300 go ahead and we will clear the contents of that cell and then last. 62 00:05:12,320 --> 00:05:17,270 We will go ahead and change its value to be something different. 63 00:05:17,500 --> 00:05:26,320 We'll say new cell value something like this and we'll go ahead and click Run we can see our X variables 64 00:05:26,320 --> 00:05:32,790 still reading this is Cell C too because that's what's typed into the cell right now or click OK and 65 00:05:32,800 --> 00:05:36,190 now we can see our font size has gotten a lot bigger. 66 00:05:36,460 --> 00:05:42,910 It's changed to this new cell value here that we had we didn't need to clear the contents if we didn't 67 00:05:42,910 --> 00:05:47,680 want to we could have actually just skipped this line entirely and just rewritten the value to be what 68 00:05:47,680 --> 00:05:53,080 we wanted it to be the clear contents can be helpful if you're trying to clear big ranges of data or 69 00:05:53,080 --> 00:05:58,840 anything like that on your file so we can change the font size we can change the font style we can make 70 00:05:58,840 --> 00:06:04,270 the color of a cell different we can resize the cell and all of those are really straightforward properties 71 00:06:04,300 --> 00:06:09,520 but when we use cells specifically we're referencing a single cell on that sheet. 72 00:06:09,520 --> 00:06:15,160 And this kind of begs the question what do we do if we went to mass reformat the cells we don't want 73 00:06:15,160 --> 00:06:16,930 to have to go cell by cell. 74 00:06:16,930 --> 00:06:22,240 So is there a way that we can mass change the properties of a range on a sheet and that is exactly what 75 00:06:22,240 --> 00:06:26,370 the range method of interaction is for the range method. 76 00:06:26,380 --> 00:06:31,360 Well you provide a start cell and an end cell and then let you interact with that entire range that 77 00:06:31,360 --> 00:06:32,610 you had selected. 78 00:06:32,740 --> 00:06:37,750 You can also use range to interact with a singular cell but most of the time you use range for interaction 79 00:06:37,780 --> 00:06:44,710 on a large scale and the cells method for interaction of a singular cell range you can also use named 80 00:06:44,710 --> 00:06:53,430 ranges in your Excel file so on my datasheet here you can see I have a list of fruit in column J apples 81 00:06:53,430 --> 00:07:00,720 through melons and I can convert this into a named range by going to the formula tab clicking on name 82 00:07:00,810 --> 00:07:06,090 manager here and clicking on new. 83 00:07:06,270 --> 00:07:12,120 Now if you haven't used name ranges before they're sort of like our variables in VBA. 84 00:07:12,120 --> 00:07:14,110 You can see here we have a chance to name them. 85 00:07:14,130 --> 00:07:19,950 So I'll name this something like fruit and then we can point to the name fruit in a certain cell or 86 00:07:19,950 --> 00:07:21,790 range of cells on my file. 87 00:07:21,840 --> 00:07:28,230 And in this case it's my my range of fruit here from J to to J 8 which we can see has already been selected. 88 00:07:28,230 --> 00:07:29,160 So we'll go ahead and click. 89 00:07:29,170 --> 00:07:36,890 Okay and now we can see it appears in our name manager as the fruit name applied to this particular 90 00:07:36,890 --> 00:07:43,920 data range that we have selected now anytime I referenced the name fruit if it's in a formula or my 91 00:07:43,920 --> 00:07:49,680 Excel file if it's in a range in my VBA code Excel we'll be able to tie it back to this range of cells 92 00:07:49,680 --> 00:07:50,580 here. 93 00:07:50,580 --> 00:07:58,470 So as an example if we come back over into our code give ourselves some room we can type range and now 94 00:07:58,470 --> 00:08:08,260 in quotes I'm going to type J to colon which means through J 8 and quotes close parentheses. 95 00:08:08,280 --> 00:08:13,650 Now this range we're giving it the start so we're giving it the end cell and we're saying we want you 96 00:08:13,650 --> 00:08:16,170 to select everything in between these two cells. 97 00:08:16,170 --> 00:08:18,600 That's what the colon stays stands for. 98 00:08:18,600 --> 00:08:22,660 So j 2 through J 8 and we've put that in quotes. 99 00:08:22,860 --> 00:08:25,600 Now we're going to type that font. 100 00:08:25,740 --> 00:08:31,470 We'll get into the font properties of this particular range and we'll go ahead and mess with the bold 101 00:08:31,620 --> 00:08:36,720 properties and we'll set that equal to true which means it will show as bold. 102 00:08:36,720 --> 00:08:42,180 So now instead of having to go step by step cell by cell through this list we're able to do it all at 103 00:08:42,180 --> 00:08:44,830 once by saying range J to to J. 104 00:08:45,810 --> 00:08:48,360 So this line references the range by its cells. 105 00:08:48,360 --> 00:08:51,460 So alternatively we could use our named range. 106 00:08:51,480 --> 00:08:59,000 So in this case we'll type range and instead of typing J to J 8 we'll type fruit and this time let's 107 00:08:59,000 --> 00:09:07,760 change the color so we'll say interior dot color index and we'll pick a number like 37 which should 108 00:09:07,760 --> 00:09:11,820 be a light blue and you can look up the color index list on line. 109 00:09:11,870 --> 00:09:17,240 Alternatively if you want to reference a more specific color if you want to use for example our G.B. 110 00:09:17,240 --> 00:09:18,740 inputs you can do that as well. 111 00:09:19,190 --> 00:09:28,010 So we'll type range fruit dot interior dot color and instead of saying color index this time we're just 112 00:09:28,010 --> 00:09:34,520 going to say color equals we'll type RG beat to make it clear that we're doing a red green blue input 113 00:09:35,240 --> 00:09:42,140 open parentheses and we'll type one hundred two hundred and two hundred and fifty there should be kind 114 00:09:42,140 --> 00:09:47,390 of a light bright colored blue but if you ever want to know exactly what your input is going to look 115 00:09:47,390 --> 00:09:55,490 like if you click back into Excel go to the Home tab under the back color dropdown you can choose more 116 00:09:55,490 --> 00:10:02,030 colors at the bottom here go to the custom tab instead of the standard tab and you can pick things in 117 00:10:02,030 --> 00:10:06,650 the standard tab if you want to we could pick kind of a lighter green or something but you can go into 118 00:10:06,650 --> 00:10:09,730 the custom tab and you can see this lighter green. 119 00:10:09,920 --> 00:10:15,830 This is the RG B code for that particular light green color we could also overwrite these directly with 120 00:10:15,830 --> 00:10:16,420 our inputs. 121 00:10:16,430 --> 00:10:21,980 In this case we said one hundred two hundred two hundred fifty five. 122 00:10:21,980 --> 00:10:26,900 So we can see this is that blue color that we're gonna get out of this particular the input so we'll 123 00:10:26,900 --> 00:10:31,670 go and close out of this and I'm going to step through this line by line so we can see what each of 124 00:10:31,670 --> 00:10:32,750 these does. 125 00:10:32,780 --> 00:10:38,900 So I will go ahead and you can either use debug step into or you can just hit F eight on your keyboard 126 00:10:38,900 --> 00:10:45,770 which is what I'll do so the first thing we should see is our cell to three value which is Cell C two 127 00:10:46,090 --> 00:10:50,080 should turn into this is Cell C to something to keep hitting f eight 128 00:10:52,830 --> 00:10:59,550 and now we should be pulling our x variable which should be equal to what is currently in Cell C 2 F 129 00:10:59,550 --> 00:11:04,030 eight one more time we should see that pop up in our message box and this looks right. 130 00:11:04,050 --> 00:11:10,740 So we'll click OK now cells C 2 should change its font size be fourteen it's already fourteen because 131 00:11:10,740 --> 00:11:15,420 we've already run this so we shouldn't see any change after this time we should see the contents of 132 00:11:15,420 --> 00:11:23,600 cell C to disappear and we'll rewrite them to say new cell value now we should be on this line changing 133 00:11:23,630 --> 00:11:30,890 the range of J to to J eight to be bold and that looks like it's worked properly now on this line we're 134 00:11:30,890 --> 00:11:36,920 referencing named range of fruit which is our range J to to J 8 and we should be changing the interior 135 00:11:36,920 --> 00:11:42,710 color of that to be index number thirty seven which is a light blue color so we'll hit eight and we 136 00:11:42,710 --> 00:11:48,350 can see that's behaved as expected and if we had a fake one more time we can just see that the RG B 137 00:11:48,350 --> 00:11:53,180 method works just as well so we'll hit F eight and we can see our blue changed colors just a little 138 00:11:53,180 --> 00:11:58,040 bit so there's two different methods of referencing those colors we'll head effort one more time to 139 00:11:58,040 --> 00:12:03,770 end our subroutine so these are gonna be our main methods of interacting with data through VBA this 140 00:12:03,770 --> 00:12:08,210 range and cell method and we talked a little bit about the difference between the two and how we can 141 00:12:08,210 --> 00:12:09,260 use them moving forward.