In the previous section, we recorded a macro to color a cell selected. We also had a chance to check the code.
However, our automation was still not very effective. We still had to select each cell to color them.
Now, think about what you can automate to ease the process.
Let me help you with building a scenario.
Let's say you are developing want to play chess with your friend using excel.
Every time you start a new game, you start in new sheet leaving and build the chess board again.
And what if you play a lot and every time doing the same repetitive task would be annoying. Now you want to develop a method to get the board created automatically.
Well VBA can help you there.
Before you start coding I want you to take some time and think what algorithm you can add to the code to do whatever you do manually.
Best practice is to start thinking about how you would do that manually. Can you find any pattern there? Is there any way I can ask code to do a task with just a couple of parameter changed.?
Take you time...
Don't rush. It is very important to brainstorm before you start looking at the code.
Be creative.
Don't worry if you do not know the syntax. you just have to do build a logic.
Alright, I hope you have thought about it. I want you to write that in the comments or anywhere you want for your future reference.
Now, remember, the way I am going to show you might not be the best way but that is the way I would do unless the code you are suggesting is flexible and less time-consuming.
Let's get started.
The way I would manually start coloring the cells is I would color the top left corner cell black. Once done with that, I would move to first non-adjacent column from the cell selected. and I will continue till the I have moved 7 cells away from starting cell. This 7 would be the offset.
Once 7 rows are done, I will move down to next row but this time I will not color the same columns. I will color the next one and will color the next non-adjacent cell.
Let say we start from Row 2 and column 2.
Below would be the coloring pattern
- Row 2 and Column 2
- Row 2 and Column 4
- Row 2 and Column 6
- Row 2 and Column 8
- Row 3 and Column 1
- Row 3 and Column 3
- Row 3 and Column 5
- Row 3 and Column 7
- Row 4 and Column 2
- Row 4 and Column 4
- Row 4 and Column 6
- Row 4 and Column 8
- Row 5 and Column 1
- Row 5 and Column 3
- Row 5 and Column 5
- Row 5 and Column 7
- Row 6 and Column 2
- Row 6 and Column 4
- Row 6 and Column 6
- Row 6 and Column 8
- Row 7 and Column 2
- Row 7 and Column 4
- Row 7 and Column 6
- Row 7 and Column 8
- Row 8 and Column 1
- Row 8 and Column 3
- Row 8 and Column 5
- Row 8 and Column 7
1. We see a pattern here. From the list, first 4 are even columns and next 4 are odd then next 4 change back to even and so on.
2. We also find that in each row only 4 columns are colored.
Hmm! do you think we can use this information to loop through cells? actually, It would be better if we make one more observation
3. Notice that the sum of Row and Column are even for all the colored cell.
Now that's something that you can definitely use in your code and it would be much easier to code.
Sub Main() '---- this is the main subroutine which controls the function. For i = 2 To 9 '-- As discussed earlier that there should be a difference of 7
'-- between starting and ending position.
'-- Also, the length between 2 to 9 is 8 because here both the
'-- end points are inclusive
For j = 2 To 9 If IsEven(i + j) Then '-- We check if the number is even or not
With ActiveSheet.Cells(j, i).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .PatternTintAndShade = 0 End With
' --- below line of code is to adjust the hieght and width of the cell.
' --- this is not required for your objective and is just for cosmetic purpose
ActiveSheet.Cells(j, i).Rows.RowHeight = 45
ActiveSheet.Cells(j, i).Columns.ColumnWidth = 8.43
End If
Next
Next
End Sub
Function IsEven(i) ' - This function finds if the number passed is even or not.
If i Mod 2 = 0 Then 'The Mod is a VBA function which gives the remainder of the division
IsEven = True 'We are assigning TRUE to the function name to return TRUE to the calling function
Else
IsEven = False
End If
End Function
You can paste this code directly into your Module in VBA editor in excel and replace the recorded code with this.
Once done run the code to test your application. However, before you run make sure that the last excel sheet opened is the one where you want the chess board to be placed.
The code has 'Activesheet' in itself and hence whatever sheet was last activated would be used for coloring. You might not want that happen to one of your other important files.
You may complain that why your shortcut key is not working.
Well! because the macro you recorded before was assigned to that key which you replaced with this code. The Main() function is not associated with your shortcut key.
for your ease, you can record another macro with your shortcut and then modify that macro to call this macro.
Too much!!. lol, don't worry. just follow my steps
1. Go to Developer tab - > Click Record Macro
2. Provide your shortcut keys and click ok
3. Now stop the recording
If you did not follow above steps, refer previous sections or post in comments.
4. Go to developer tab and you should see something like this
5. Delete anything you have between Sub Macro1 .. and End Sub ( except the comments)
6 . Add a line of code to call the main function.
Your code should be something like this
Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+q ' Call Main '-- calling the main function End Sub
There are other ways to call a subroutine like a user form button or ActiveX button or any worksheet event. However, that would be covered later.
Click here to download the demo excel file.
Take your time to practice this. Play around with this and make sure you understand the concepts.
Any questions, let me know in the comments.
See you soon
No comments:
Post a Comment