Category Archives: Learning Excel / How To Have Fun With Excel

Learning Excel #3, Designing Math Challenge For Young Children, Template #3

Standard

Hello again,

 

We have finally got some summer weather here in England and although many of you won’t associate summer with Excel designing, I have decided to keep bravely going strong with it even on these hot days. (Correction: The days are quite hot, but the weather isn’t exactly great for outdoor activities due to summer storms hanging in the air, ready to hit as soon as you would delve into some enjoyable outdoor activity.)j

 

This is my third attempt at creating a simple, clean and visually appealing math challenge template for young children and this is the first one where I had a play with automated macro.

 

During the designing process I had to ask my Excel guru for a little advice on handling error messages from VBA (so thank you Gary), but apart from that, it is all my work. Well, mine and Internet’s. But I managed to find all the answers myself. I even found and corrected an error in processing the automated macros, which was causing conflict with the selection of the next field for next user’s input. This is not to show that after a month of designing little Math challenges I am a know-it-all Excel expert, it’s simply my attempt to describe my learning development with as much details as I can, so it’s also a bit of storytelling and contains an evidence of my progress and skills, should someone someday ask my what my Excel skills are like.

 

So what have we got here?

 

1, The idea

 

Once again the aim was to create a visually appealing file for young children (our daughter is almost six) and try something new (automated macro). As I was searching for a way to do this, I came across the following command:

 

Private Sub Worksheet_Change (ByVal Target As Range)

 

I am not 100% sure what exactly it does (many of you surely know) but I used it to run automated macro. (Code to follow.) Since I am learning all of this as I go, I don’t want to give anybody any advice as I myself feel a bit unsure when it comes to VBA and commands, and the logic of statements.

But I will be happy to provide what I can.

During the designing of the long code I had a struggle with the order of “If statements”, but in the end I got there. So I guess I must be doing something right.

 

This time I designed the layout to match the character of Ferda, the ant, and I quite like the red and black Kurt Cobain sweater design of it.

 

The automated macro moves the user from cell where he provided the answer into a cell where a new answer is required until the last answer is provided.

It took me a long time to write the correct code (and sometimes I think there might be errors in it, but as far as I can tell – it works! Yay!)

I have written the code in a way that also allows the user to change the numbers in the equations manually, without breaking what it does (previously I didn’t consider the user’s request to be able to change things in the file, something my guru told me to always consider, and also making the code as flexible as possible).

 

Here’s an example of above:

 If Range(“D6”) = Range(“F6”) – Range(“B6”) Then
    Call Cell3

– This means that as long as user changes pre-populated numbers and keeps the empty space for answers, he can adjust the equations as he wishes.

2, How does it work?

 

~ Checking the answers

Unlike in previous challenge, the incorrect answer doesn’t unhide a picture (at the time of writing the code this seemed to be a lot of work and I wanted to create this file quickly. But this is listed as possible improvement.)

The answers are checked by macro and once the answer is checked, macro moves user to next cell. The macro that checks the answer also lifts the white rectangle cover off the “thumbs up Ferda” if the answer is correct.

 

~ Checking whether all answers are correct

As in previous challenge, this is achieved by calculating the difference between the answer provided by user and between the correct answer checked by Excel itself. If the difference is 0, the answer is correct.

Then all zeroes are counted in a  master cell, which if all answers are correct shows 10, then for some reason I added -10 to get 0. If master check shows 0 a macro enabled button with Show Reward is made visible, in the case that not all answers are correct, button to show reward remains invisible.

 

~ Commands/Macros

 

There’s 10 macros to lift the white background under which the picture of “Thumbs up Ferda” is hiding.

 

Then there’s a long code that calls the macros one by one as user makes his/her way down the challenge. This code is (at least for me), stupidly long and easy to get lost within, but ultimately it can be broken into ten “commandments” consisting of 3 logical statements below:

 

1, If the cell with answer is blank, do nothing, exit

2, If the cell contains the correct answer, call macro to life the picture in referencing cell and move user to next cell where answer is required

3, Don’t unhide anything, just move user to next cell where answer is required

 

Once all answers are provided, user is either rewarded by a “Congratulations” message and picture of happy Ferda, or a message with “I’m sure you’ll do better next time.” is given.

Pictures:

Ferda 1

Ferda 2

Ferda 3

Ferda 4

~ Coding

Here’s the coding for the challenge:

 

> > >  Sheet 1 Code:

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Range(“D2”) = Range(“F2”) – Range(“B2”) Then

Call Cell1

End If

If Range(“D2”) <> Range(“F2”) – Range(“B2”) Then

Range(“B4”).Select

End If

If Range(“B4”) = Range(“F4”) – Range(“D4”) Then

Call Cell2

End If

If IsEmpty(Range(“B4”).Value) = True Then

Exit Sub

End If

If Range(“B4”) <> Range(“F4”) – Range(“D4”) Then

Range(“D6”).Select

End If

If IsEmpty(Range(“D6”).Value) = True Then

Exit Sub

End If

If Range(“D6”) = Range(“F6”) – Range(“B6”) Then

Call Cell3

End If

If Range(“D6”) <> Range(“F6”) – Range(“B6”) Then

Range(“B8”).Select

End If

If IsEmpty(Range(“B8”).Value) = True Then

Exit Sub

End If

If Range(“B8”) = Range(“F8”) – Range(“D8”) Then

Call Cell4

End If

If Range(“B8”) <> Range(“F8”) – Range(“D8”) Then

Range(“D10”).Select

End If

If IsEmpty(Range(“D10”).Value) = True Then

Exit Sub

End If

If Range(“D10”) = Range(“F10”) – Range(“B10”) Then

Call Cell5

End If

If Range(“D10”) <> Range(“F10”) – Range(“B10”) Then

Range(“B12”).Select

End If

If IsEmpty(Range(“B12”).Value) = True Then

Exit Sub

End If

If Range(“B12”) = Range(“F12”) – Range(“D12”) Then

Call Cell6

End If

If Range(“B12”) <> Range(“F12”) – Range(“D12”) Then

Range(“D14”).Select

End If

If IsEmpty(Range(“D14”).Value) = True Then

Exit Sub

End If

If Range(“D14”) = Range(“F14”) – Range(“B14”) Then

Call Cell7

End If

If Range(“D14”) <> Range(“F14”) – Range(“B14”) Then

Range(“B16”).Select

End If

If IsEmpty(Range(“B16”).Value) = True Then

Exit Sub

End If

If Range(“B16”) = Range(“F16”) – Range(“D16”) Then

Call Cell8

End If

If Range(“B16”) <> Range(“F16”) – Range(“D16”) Then

Range(“D18”).Select

End If

If IsEmpty(Range(“D18”).Value) = True Then

Exit Sub

End If

If Range(“D18”) = Range(“F18”) – Range(“B18”) Then

Call Cell9

End If

If Range(“D18”) <> Range(“F18”) – Range(“B18”) Then

Range(“B20”).Select

End If

If Range(“B20”) = Range(“F20”) – Range(“D20”) Then

Call Cell10

End If

If Range(“Y22”) = 0 Then

ActiveSheet.Shapes(“Rectangle 3”).Visible = True

End If

If Range(“Y23”) = 10 And Range(“Y22”) <> 0 Then

ActiveSheet.Pictures(“Ferda 1”).Visible = False

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = True

End If

End Sub

 

> > > This Workbook code

 

Private Sub Workbook_Open()

 

Range(“D2”).Select

ActiveSheet.Pictures(“Pic 1A”).Visible = True

ActiveSheet.Pictures(“Pic 2A”).Visible = True

ActiveSheet.Pictures(“Pic 3A”).Visible = True

ActiveSheet.Pictures(“Pic 4A”).Visible = True

ActiveSheet.Pictures(“Pic 5A”).Visible = True

ActiveSheet.Pictures(“Pic 6A”).Visible = True

ActiveSheet.Pictures(“Pic 7A”).Visible = True

ActiveSheet.Pictures(“Pic 8A”).Visible = True

ActiveSheet.Pictures(“Pic 9A”).Visible = True

ActiveSheet.Pictures(“Pic 10A”).Visible = True

ActiveSheet.Pictures(“Ferda 1”).Visible = True

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = False

ActiveSheet.Shapes(“Rectangle 3”).Visible = False

 

End Sub

 

> >> Macros to lift cover pictures

 

Sub Cell1()

‘ Cell1 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 1A”).Visible = False

Range(“B4”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell2()

‘ Cell2 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 2A”).Visible = False

Range(“D6”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell3()

‘ Cell3 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 3A”).Visible = False

Range(“B8”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell4()

‘ Cell4 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 4A”).Visible = False

Range(“D10”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell5()

‘ Cell5 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 5A”).Visible = False

Range(“B12”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell6()

‘ Cell6 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 6A”).Visible = False

Range(“D14”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell7()

‘ Cell7 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 7A”).Visible = False

Range(“B16”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell8()

‘ Cell8 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 8A”).Visible = False

Range(“D18”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell9()

‘ Cell9 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 9A”).Visible = False

Range(“B20”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell10()

‘ Cell10 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 10A”).Visible = False

 

ErrorHandler:

Exit Sub

End Sub

 

> > > Show Reward macro

 

Sub ShowReward()

‘ ShowReward Macro

 

ActiveSheet.Pictures(“Ferda 1”).Visible = False

ActiveSheet.Pictures(“Ferda 2”).Visible = True

ActiveSheet.Pictures(“Ferda 3”).Visible = False

Range(“A1”).Select

 

End Sub

 

> > > Start Again Macro

Sub StartAgain()

‘ StartAgain

 

Selection.ClearContents

Range(“D2”).Select

Selection.ClearContents

Range(“B4”).Select

Selection.ClearContents

Range(“D6”).Select

Selection.ClearContents

Range(“B8”).Select

Selection.ClearContents

Range(“D10”).Select

Selection.ClearContents

Range(“B12”).Select

Selection.ClearContents

Range(“D14”).Select

Selection.ClearContents

Range(“B16”).Select

Selection.ClearContents

Range(“D18”).Select

Selection.ClearContents

Range(“B20”).Select

Selection.ClearContents

Range(“D2”).Select

Range(“D2”).Select

ActiveSheet.Pictures(“Pic 1A”).Visible = True

ActiveSheet.Pictures(“Pic 2A”).Visible = True

ActiveSheet.Pictures(“Pic 3A”).Visible = True

ActiveSheet.Pictures(“Pic 4A”).Visible = True

ActiveSheet.Pictures(“Pic 5A”).Visible = True

ActiveSheet.Pictures(“Pic 6A”).Visible = True

ActiveSheet.Pictures(“Pic 7A”).Visible = True

ActiveSheet.Pictures(“Pic 8A”).Visible = True

ActiveSheet.Pictures(“Pic 9A”).Visible = True

ActiveSheet.Pictures(“Pic 10A”).Visible = True

ActiveSheet.Pictures(“Ferda 1”).Visible = True

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = False

ActiveSheet.Shapes(“Rectangle 3”).Visible = False

 

End Sub

 

~ Possible improvements

 

1,

Assign an action/macro to run when incorrect answer is provided

 

2, Randomised equations each time challenge is restarted

 

 

~

 

 

Download:

Full Excel file can be downloaded from here >>>

Learning Excel #2, Designing Math Challenge For Young Children, Template #2

Standard

Hello all,

 

As I was pleased with the achievement on the previous Math challenge for young children I have designed, motivated, I immediately went to design a new template.

 

Once again, I set up the challenge with two goals in mind:

– pleasing design with some lovely cartoon character

– interesting layout with some visual verification of the answers

 

I have to say that I really like this template and I hope you will like it too. Please have a read and download the file and try it. (Download at the end.)

 

1, The idea

The idea is pretty simple again.

What I have changed from the previous template is the layout.

This time the file after opening only displays one equation.

Once the answer is provided, the new equation is given by running a macro assigned to a button right of the equation, unhiding row above the starting task and new tasks continue to be given one by one until the last one is uncovered.

 

The answers are confirmed in a form of a picture. The correct answer gets Doc McStuffins and the incorrect a plaster. On top of this, the answer is filled with green, if correct, and with red, if incorrect.

 

And as a final reward for all answers being correct, there is a picture saying “Congratulations.”

 

2, How does it work?

~ Checking the answers

Each answer is checked through a macro. Next to each answer are four pictures laid one on each other, the top picture being a white background hiding all others underneath. The correct answer runs macro that removes the top white square uncovering the Doc McStuffins picture as a reward. The macro that runs after incorrect answer deletes the top white square, the Doc McStuffins underneath it, and also the white square floating above the plaster. So basically it removes three layers of pictures.

 

Both macros then unhide the row above and give user new task.

 

In the cells where answers are provided, conditional formatting is used to give the cell appropriate fill, either green, or red.

 

~ Checking whether all answers are correct

This is achieved by calculating the difference between the answer provided by user and between the correct answer checked by Excel itself. If the difference is 0, the answer is correct.

Then the SUM of all differences is pulled into the master cell, which again, if all differences are 0, means all answers are correct, and in case its value is different than 0, at least one answer is incorrect, in which case the reward picture message won’t be displayed.

 

~ Commands/Macros

Again, I decided to execute the checks by user. So there are macro enabled buttons next to each equation to execute this.

 

Within the last macro there’s also a code that checks whether all answers are correct and unhides the message with congratulations text.

 

~ Coding

Here’s the full code for the challenge:

Macro to delete pictures based on answer being either correct, or incorrect:

Sub Delete_White1()

‘ Delete_White1 Macro


If (Range(“F16”).Value = 9) Then
ActiveSheet.Shapes.Range(Array(“Pic 3A”)).Select
Selection.Delete
Range(“G2”).Select
Rows(“15:15”).Select
Selection.EntireRow.Hidden = False
Range(“F16”).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(“F15”).Select

End If


If (Range(“F16”).Value <> 9) Then
ActiveSheet.Shapes.Range(Array(“Pic 3A”)).Select
Selection.Delete
Range(“G2”).Select
ActiveSheet.Shapes.Range(Array(“Picture 43”)).Select
Selection.Delete
Range(“G2”).Select
ActiveSheet.Shapes.Range(Array(“Pic 3”)).Select
Selection.Delete
Range(“G2”).Select
Rows(“15:15”).Select
Selection.EntireRow.Hidden = False
Range(“F16”).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(“F15”).Select

End If

Rows(“15:15”).Select
Selection.EntireRow.Hidden = False
Range(“F15”).Select
End Sub

And this is macro that uncoveres the Well done! message:

Sub WellDone()

‘ WellDone Macro


ActiveSheet.Shapes.Range(Array(“Picture 101”)).Select
Selection.Delete
End Sub
Sub HideAll()

‘ HideAll Macro


Rows(“2:17”).Select
Selection.EntireRow.Hidden = True
End Sub

 

Sample pictures

doc mc stuffin math 1

doc mc stuffin math 2

correct incorrect

doc mc stuffin math 4

doc mc stuffin math 3

 

~ Possible improvements

 

1, It was suggested that instead of deleting the white background pictures covering the pictures I want to display I could use a True / False statement code to make whichever of the two I need visible, or invisible. (This wouldn’t make a difference for user, but would be easier to write and would give possibility to restart the challenge.)

 

2, Randomization of the equations automatically as soon as the file is open

 

~

 

So,

 

this is my second attempt at designing a Math challenge for young children. I might have explained this in previous post, but I will explain it again (just in case). The idea of designing these challenges comes from a chat with my Excel guru, who cleverly suggested that the best way is fine learning Excel is to play with it. Now to play with it, it’s probably good if one can have some challenge on mind. So to give myself a motivation I decided to kill two birds with one stone and I set up a target of creating a few math challenge templates for young children, including my daughter, which, in the end, could be used by any user who wants to teach his/her children math and computer skills in a simple, but visually appealing way.

 

I also wanted to create a little Excel portfolio of my own designs to be able to give anyone about idea about my Excel skills. So that’s why this blog.  And I also want to have a little platform for sharing creative Excel ideas with others.

So if you have any, please get in touch.

 

Download:

Full Excel file can be downloaded from here >>>

Learning Excel #1, Designing Math Challenge For Young Children, Template #1

Standard

Hello all,

It is a long time since I have written something and I kind of miss writing and while I am waiting for some brilliant idea for some short story, I decided to create a little section on my blog where I will be writing about my progress on learning new Excel skills.

One of the reasons why I am doing that is my obsession with documentation of what I do and also, to have some platform where I can get some feedback from more knowledgeable folks. Because I’m only a beginner in visual basic and it takes me a while to get appropriate advice from forums. Plus, apparently, journaling is good method of feeling good about one’s life.

That should be enough for brief introduction.

Now let me present you my first work. A simple math template that I have created for my daughter.

1, The idea
The idea is pretty simple. Although to execute it took me quite a while. But I learned a lot during the process.

The template is based on 10 simple equations, each of which has got a reference cell to show whether the answer is correct, or not. The correct answer gets a green smiley face and an incorrect answer gets a red frown face.
On top of that, if all 10 answers are correct, a nice picture is displayed as a “reward”.

2, How does it work?
~ Checking the answers
Each answer is checked through the conditional formatting. If the answer is correct, the cell with answer is filled with green, if not the fill is red. Next to each cell with answer is another cell into which a smiley, or frown face is pulled from a hidden referencing column. In those cells, again, conditional formatting is used to give the cell matching filling, again, either green, or red.

~ Checking whether all answers are correct
This is achieved by calculating the difference between the answer provided by user and between the correct answer checked by Excel itself. If the difference is 0, the answer is correct.
Then the SUM of all differences is pulled into the master cell, which again, if all differences are 0, means all answers are correct, and in case its value is different than 0, at least one answer is incorrect, in which case the reward picture won’t be displayed.

~ Commands/Macros
I decided to execute the checks by user. It was suggested to me, that I could have had an automated check run as soon as the last answer is provided, which I have to admit, was a relevant suggestion. I however wanted my daughter to get used to using a mouse and check it herself. I also wanted her to be able to restart the challenge manually. So there are two macro enabled buttons to execute this. One that checks the answers, and one that restarts the task.

~ Coding
Here’s the full code for the challenge:

Sub Check_Answers()

‘ Check_Answers Macro


Columns(“G:G”).Select
Selection.EntireColumn.Hidden = False
Range(“E14”).Select
If (Range(“L12”).Value = 0) Then

Columns(“K:K”).Select
Selection.EntireColumn.Hidden = False
Range(“E14”).Select

End If

End Sub
Sub Start_Again()

‘ Start_Again Macro


Range(“F2:F11”).Select
Selection.ClearContents
Columns(“G:G”).Select
Selection.EntireColumn.Hidden = True
Columns(“K:K”).Select
Selection.EntireColumn.Hidden = True
Range(“F2”).Select
End Sub

~ Pictures to give idea

image

image

image

image

image

image

~ Possible improvements

1, Automated macro to check all the answers and display the final result as soon as the last answer is provided

2, Randomization of the equations automatically as soon as the file is open

~

So this is my first post about my learning using VBA and designing in Excel. If you have any comments, or suggestions please let me know in comments.
Please remember that I am not an expert, but I hope that at some point in future, I will be. I couldn’t imagine Excel being fun, but surprisingly it is.

Download:
Full Excel file can be downloaded from here >>>