EN1201: Excel VBA

Excel VBA:

Create your own functions and Basic codes in Excel!

VBA - Visual Basic for Applications
Macros - short programs or sets of recorded keystrokes

Two ways to create a Macro in Excel:
1. Record a set of keystrokes
2. write a VBA subprogram

Excercise #1: Record a set of keystrokes
Open up the circuits Macro-Lab from D2L:

MeyersMacroEx.xlsm
Enable editing
Enable Macros

Click on the "Developer" tab.
If the developer tab is not showing up in your ribbon:
(you might have a different version of excel, but it will be something similar to -
File→Options→Advanced→Customize Ribbon→Main Tabs

Have a look around in the Developer tab
- hold your curser over an icon to see what it does.

Create a Macro program by recording your keystrokes:

Set up your cell referencing:

Absolute referencing - if your calculations come from a stationary cell like "A3" etc. that will never change.
Relative referencing - if your calculations will be made relative to a given cell - example, "in the cell to the right of the currently selected cell"

We are going to use relative referencing. Select the yellow cell as the place everything else will be referenced from:

Click on the Developer tab
Click on the yellow cell
Click on "Use Relative References"

Now, select "Record Macro"
Name your Macro "Series", set up a shortcut key of "S", add description of what your program will do "Macro to calculate voltage drops and current for 3 resistors connected in series"

Once you hit "OK" the Macro will start recording everything that you do.
Fill in the first table just like you did for the lab. When you are done, hit "Stop Recording"

Note:
Do not use dollar signs in your macro ($F$8) etc. as this will not allow you to use relative referencing for new tables.

Enter each new equation by hand rather than copy/paste.

"Stop Recording" when you are done filling in your table.

Once you have created your Series Macro, try it out on the second table:
Click on the "Series Macro Starting Cell:
Enter your shortcut key "Cntr + s"
Did it fill in the table in for you?

You can also run your Macro from the ribbon:
Select your starting reference cell
hit "Macros"
Find the Macros you just made
hit "run" to run your program
hit "Edit" to view the VBA code

Click on "Edit" and have a look at the code that was generated from how you filled in your table:

Sub Series()
'
' Series Macro
' Macro to calculate voltage drops and current for 3 resistors in series.
'
' Keyboard Shortcut: Ctrl+s
'
'lines that start with ' are comment lines. This is not part of the code, it just explains to other users what the program does.

ActiveCell.Offset(4, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]+RC[-1]"

The first thing I did was to calculate the total resistance.

From the starting cell, I moved down 4, and over 4 (4,4)
Then I entered an equation in the new active cell → Row one Column one (R1C1)
I grabbed the cell in the same Row, 3 columns to the left (RC[-3]) added it to RC[-2] and so on

ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R[1]C"

Next I moved up one row, stayed in the same column, ActiveCell.Offset(-1, 0) and calculated the current from the cells above and below where the new active cell is "=R[-1]C/R[1]C:

Read through the rest of your code, do you recognize what each line is doing?

    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[1]"
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[1]"
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[1]"
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C*R[2]C"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C*R[2]C"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C*R[2]C"
    ActiveCell.Offset(4, -3).Range("A1").Select
    Application.Run "'circuits Macro-Lab.xlsx'!Series"
    ActiveCell.Offset(0, 10).Range("A1").Select
    ActiveWindow.Zoom = 115
    ActiveWindow.Zoom = 130
    ActiveWindow.Zoom = 145
    ActiveWindow.Zoom = 160
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.Offset(-6, -10).Range("A1").Select
    Application.Goto Reference:="Series"
End Sub

Create Macros for the Parallel and Combined tables just like you created it for the series tables.

Save your file as a Macros enabled workbook, and email me your file:
File→ Save As→ Save as Type→ Excel Macro-Enabled workbook *.xlsm

Video: https://www.youtube.com/watch?time_continue=1&v=Hxgxv2vvGz8

```````````````````````````````````````````````````````````````````````````````

Recall: Two ways to create a Macro in Excel:
1. Record a set of keystrokes
2. write a VBA subprogram

Let's try out #2.

Start in the Hello Sheet and look around. Click on the buttons to see what they do!

Create a new Sheet to make your own "Hello" program

+ to add a sheet
Double click on the sheet to rename it.

Create a "Hello" Button:
Insert → Button

There are two types of buttons:

Form Control - simpler, easier to use, but limited
ActiveX Controls - Allows you to control more properties

Choose an "ActiveX" button so that you can change the color etc.

Click and drag on the screen to place the button.

Select "Design Mode:

Select, then right click your button to:

Edit the text: Command Button Object - Edit
Change the "properties" (font color, background color color),
View your code

Right click and "View Code" or Double click on the button to view code

Type in some code to make the button do something when you click on it, like write "Hello World" into cell D12

Range("D12").Value = "Hello world!"
Range("D13").Value = "How are you today?"

Press F5 key to run your code, or click the green arrow.

Close your editor window, and return to your excel sheet.
Erase the text your program created, click on the button, test it to make sure it is working ok.

Add some more buttons:

Do you remember how to format the font, text, and color of your buttons?
Design mode →right click!

Add some more code to each of your new buttons:

If OptionButton1.Value = True Then Range("D23").Value = "I'm happy to hear you are doing well!"

If OptionButton2.Value = True Then Range("D23").Value = "So sorry to hear you are not doing well."

Note: You can change your font style for each cell from the home tab

Save your work as a macro enabled worksheet:

Look through the programs in the other sheets:

Edit the below Meyer's personality test to write out your star trek character!
see:
http://randommization.com/2013/12/10/myers-briggs-personality-type-matched-star-wars-star-trek-characters/

Go to the Meyer's sheet
Open up the code by click on "Macros" in the Developer ribbon
"Step into" any of the Macros
Click through the different modules until you find the personality type program

Read through the personality code, can you figure out what it is doing?

Can you edit this program to also display Star Trek Characters?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Button2_Click()
Dim Muppet As Variant
Range("B18").Value = "Your Personality type is:"

If Range("B6").Value > Range("B7").Value Then
Range("C19").Value = "E"
Else
Range("C19").Value = "I"
End If

If Range("B9").Value > Range("B10").Value Then
Range("D19").Value = "S"
Else
Range("D19").Value = "N"
End If

If Range("B12").Value > Range("B13").Value Then
Range("E19").Value = "T"
Else
Range("E19").Value = "F"
End If

If Range("B15").Value > Range("B16").Value Then
Range("F19").Value = "J"
Else
Range("F19").Value = "P"
End If

Range("B18").Value = "Your personality type is:"
Range("B21").Value = "Your Star Wars Character is:"
Range("B23").Value = "Your Harry Potter Character is:"
Range("B26").Value = "Your Lord of the Rings Character is:"
Range("B29").Value = "Your Star Trek Character is:"

'ISTJ
If Range("C19").Value = "I" And Range("D19").Value = "S" And Range("E19").Value = "T" And Range("F19").Value = "J" Then
Range("C20").Value = "The Duty FulFiller - Serious and quiet, interested in security and peaceful living. Extremely thorough, responsible, and dependable. Well-developed powers of concentration. Usually interested in supporting and promoting traditions and establishments. Well-organized and hard working, they work steadily towards identified goals. They can usually accomplish any task once they have set their mind to it. "
Range("C22").Value = "Owen Lars"
Range("C24").Value = "Severus Snape"
Range("C27").Value = "Aragorn"
Range("C30").Value = ""
End If

'ISTP
If Range("C19").Value = "I" And Range("D19").Value = "S" And Range("E19").Value = "T" And Range("F19").Value = "P" Then
Range("C20").Value = "The Mechanic - Quiet and reserved, interested in how and why things work. Excellent skills with mechanical things. Risk-takers who they live for the moment. Usually interested in and talented at extreme sports. Uncomplicated in their desires. Loyal to their peers and to their internal value systems, but not overly concerned with respecting laws and rules if they get in the way of getting something done. Detached and analytical, they excel at finding solutions to practical problems. "
Range("C22").Value = "Chewbacca"
Range("C24").Value = "Harry Potter"
Range("C27").Value = "Eowyn"
Range("C30").Value = ""
End If

'ISFJ
If Range("C19").Value = "I" And Range("D19").Value = "S" And Range("E19").Value = "F" And Range("F19").Value = "J" Then
Range("C20").Value = "The Nurturer- Quiet, kind, and conscientious. Can be depended on to follow through. Usually puts the needs of others above their own needs. Stable and practical, they value security and traditions. Well-developed sense of space and function. Rich inner world of observations about people. Extremely perceptive of other's feelings. Interested in serving others."
Range("C22").Value = "C-3PO"
Range("C24").Value = "Neville Longbottom"
Range("C27").Value = "Samwise"
Range("C30").Value = ""
End If

'ISFP
If Range("C19").Value = "I" And Range("D19").Value = "S" And Range("E19").Value = "F" And Range("F19").Value = "P" Then
Range("C20").Value = "The Artist - Quiet, serious, sensitive and kind. Do not like conflict, and not likely to do things which may generate conflict. Loyal and faithful. Extremely well-developed senses, and aesthetic appreciation for beauty. Not interested in leading or controlling others. Flexible and open-minded. Likely to be original and creative. Enjoy the present moment."
Range("C221").Value = "Bail Organa"
Range("C24").Value = "Rubeus Hagrid"
Range("C27").Value = "Legolas"
Range("C30").Value = ""
End If

'INFJ
If Range("C19").Value = "I" And Range("D19").Value = "N" And Range("E19").Value = "F" And Range("F19").Value = "J" Then
Range("C20").Value = "The Protector - Quietly forceful, original, and sensitive. Tend to stick to things until they are done. Extremely intuitive about people, and concerned for their feelings. Well-developed value systems which they strictly adhere to. Well-respected for their perserverence in doing the right thing. Likely to be individualistic, rather than leading or following. "
Range("C22").Value = "Obi-Wan Kenobi"
Range("C24").Value = "Remus Lupin"
Range("C27").Value = "Galadriel"
Range("C30").Value = ""
End If

'INTJ
If Range("C19").Value = "I" And Range("D19").Value = "N" And Range("E19").Value = "T" And Range("F19").Value = "J" Then
Range("C20").Value = "The Scientist - Independent, original, analytical, and determined. Have an exceptional ability to turn theories into solid plans of action. Highly value knowledge, competence, and structure. Driven to derive meaning from their visions. Long-range thinkers. Have very high standards for their performance, and the performance of others. Natural leaders, but will follow if they trust existing leaders. "
Range("C22").Value = "Palpatine"
Range("C24").Value = "Draco Malfoy"
Range("C27").Value = "Elrond"
Range("C30").Value = ""
End If

'INTP
If Range("C19").Value = "I" And Range("D19").Value = "N" And Range("E19").Value = "T" And Range("F19").Value = "P" Then
Range("C20").Value = "The Thinker - Logical, original, creative thinkers. Can become very excited about theories and ideas. Exceptionally capable and driven to turn theories into clear understandings. Highly value knowledge, competence and logic. Quiet and reserved, hard to get to know well. Individualistic, having no interest in leading or following others. "
Range("C22").Value = "Yoda"
Range("C24").Value = "Hermione Granger"
Range("C27").Value = "Gandalf"
Range("C30").Value = ""
End If

'INFP
If Range("C19").Value = "I" And Range("D19").Value = "N" And Range("E19").Value = "F" And Range("F19").Value = "P" Then
Range("C20").Value = "The Idealist - Quiet, reflective, and idealistic. Interested in serving humanity. Well-developed value system, which they strive to live in accordance with. Extremely loyal. Adaptable and laid-back unless a strongly-held value is threatened. Usually talented writers. Mentally quick, and able to see possibilities. Interested in understanding and helping people. "
Range("C22").Value = "Luke Skywalker"
Range("C24").Value = "Luna Lovegood"
Range("C27").Value = "Frodo"
Range("C30").Value = ""
End If

'ESTP
If Range("C19").Value = "E" And Range("D19").Value = "S" And Range("E19").Value = "T" And Range("F19").Value = "P" Then
Range("C20").Value = "The Doer - Friendly, adaptable, action-oriented. Doers who are focused on immediate results. Living in the here-and-now, they're risk-takers who live fast-paced lifestyles. Impatient with long explanations. Extremely loyal to their peers, but not usually respectful of laws and rules if they get in the way of getting things done. Great people skills."
Range("C22").Value = "Han Solo"
Range("C24").Value = "Ginny Weasley"
Range("C27").Value = "Gimli"
Range("C30").Value = ""
End If

'ESFP
If Range("C19").Value = "E" And Range("D19").Value = "S" And Range("E19").Value = "F" And Range("F19").Value = "P" Then
Range("C20").Value = "Wicket: The Performer - People-oriented and fun-loving, they make things more fun for others by their enjoyment. Living for the moment, they love new experiences. They dislike theory and impersonal analysis. Interested in serving others. Likely to be the center of attention in social situations. Well-developed common sense and practical ability. "
Range("C22").Value = "Wicket"
Range("C24").Value = "Fred and George Weasley"
Range("C27").Value = "Pippin"
Range("C30").Value = ""
End If

'ENFP
If Range("C19").Value = "E" And Range("D19").Value = "N" And Range("E19").Value = "F" And Range("F19").Value = "P" Then
Range("C20").Value = "Qui-Gon Jinn: The Inspirer - Enthusiastic, idealistic, and creative. Able to do almost anything that interests them. Great people skills. Need to live life in accordance with their inner values. Excited by new ideas, but bored with details. Open-minded and flexible, with a broad range of interests and abilities"
Range("C22").Value = "Qui-Gon Jinn"
Range("C24").Value = "Ron Weasley"
Range("C27").Value = "Arwen"
Range("C30").Value = ""
End If

'ENTP
If Range("C19").Value = "E" And Range("D19").Value = "N" And Range("E19").Value = "T" And Range("F19").Value = "P" Then
Range("C20").Value = "R2-D2: The Visionary - Creative, resourceful, and intellectually quick. Good at a broad range of things. Enjoy debating issues, and may be into one-up-manship. They get very excited about new ideas and projects, but may neglect the more routine aspects of life. Generally outspoken and assertive. They enjoy people and are stimulating company. Excellent ability to understand concepts and apply logic to find solutions. "
Range("C22").Value = "R2-D2"
Range("C24").Value = "Sirius Black"
Range("C27").Value = "Merry"
Range("C30").Value = ""
End If

'ESTJ
If Range("C19").Value = "E" And Range("D19").Value = "S" And Range("E19").Value = "T" And Range("F19").Value = "J" Then
Range("C20").Value = "Darth Vader: The Guardian - Practical, traditional, and organized. Likely to be athletic. Not interested in theory or abstraction unless they see the practical application. Have clear visions of the way things should be. Loyal and hard-working. Like to be in charge. Exceptionally capable in organizing and running activities. Good citizens who value security and peaceful living. "
Range("C22").Value = "Darth Vader"
Range("C24").Value = "Minerva McGonagall"
Range("C27").Value = "Boromir"
Range("C30").Value = ""
End If

'ESFJ
If Range("C19").Value = "E" And Range("D19").Value = "S" And Range("E19").Value = "F" And Range("F19").Value = "J" Then
Range("C20").Value = "Jar Jar Binks: The Caregiver - Warm-hearted, popular, and conscientious. Tend to put the needs of others over their own needs. Feel strong sense of responsibility and duty. Value traditions and security. Interested in serving others. Need positive reinforcement to feel good about themselves. Well-developed sense of space and function."
Range("C22").Value = "Jar Jar Binks"
Range("C24").Value = "Lily Evans-Potter"
Range("C27").Value = "Bilbo"
Range("C30").Value = ""
End If

'ENFJ
If Range("C19").Value = "E" And Range("D19").Value = "N" And Range("E19").Value = "F" And Range("F19").Value = "J" Then
Range("C20").Value = "Padme Amidala: The Giver - Popular and sensitive, with outstanding people skills. Externally focused, with real concern for how others think and feel. Usually dislike being alone. They see everything from the human angle, and dislike impersonal analysis. Very effective at managing people issues, and leading group discussions. Interested in serving others, and probably place the needs of others over their own needs. "
Range("C22").Value = "Padme Amidala"
Range("C24").Value = "Albus Dumbledore"
Range("C27").Value = "Faramir"
Range("C30").Value = ""
End If

'ENTJ
If Range("C19").Value = "E" And Range("D19").Value = "N" And Range("E19").Value = "T" And Range("F19").Value = "J" Then
Range("C20").Value = "Leia Organa: The Executive - Assertive and outspoken - they are driven to lead. Excellent ability to understand difficult organizational problems and create solid solutions. Intelligent and well-informed, they usually excel at public speaking. They value knowledge and competence, and usually have little patience with inefficiency or disorganization. "
Range("C22").Value = "Leia Organa"
Range("C24").Value = "James Potter"
Range("C27").Value = "Theoden"
Range("C30").Value = ""
End If

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Is there a better way to create the above program?
Use the Meyer's program to record each team member's personality type. Use personality types to help decide what tasks to assign each team member.

Kolb Learning Styles - see Kolb sheet

see:
http://intro1201.blogspot.com/2015/03/jung-meyers-typology-perrys-scheme.html

Look through the Kolb code.
Try out the Kolb Learning styles test.
Record the learning style of everyone in your group.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Example of Creating a Function:

Insert → Module

Public Function Grade(Number As Variant)

    If Number > 90 Then
    Grade = "A"
    ElseIf Number > 80 Then
    Grade = "B"
    ElseIf Number > 70 Then
    Grade = "C"
    ElseIf Number > 60 Then
    Grade = "D"
    Else
    Grade = "F"
    End If

End Function

Exercise - create a function for curving grades:

Public Function CurvedGrade(Number1 As Variant)

    If Number1 > 85 Then
CurvedGrade = "A"
    ElseIf Number1 > 75 Then
CurvedGrade = "B"
    ElseIf Number1> 65 Then
CurvedGrade = "C"
    ElseIf Number1 > 55 Then
CurvedGrade = "D"
    Else
CurvedGrade = "F"
    End If

End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Units Example:
Here we have a table where the data changes depending on the units that are selected:

Create a pull down menu with different units:
Data→Data Validation→Data Validation→Allow→List→Source

Subroutine that changes the data to reflect the units that are selected:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Worksheet.Name = "Sheet3" Then
    If Target.Address = "$B$5" Then
        If Target = "seconds" Then
            c = 1
        ElseIf Target = "minutes" Then
            c = 60
        Else
            c = 3600
        End If
'        k = WorksheetFunction.Count("$B$6:$B$1000")
        k = 126
        j = 6
        Do Until j = k
            Range("$B$" & j).Value = Range("$B$" & j).Value * Range("$O$6").Value / c
        j = j + 1
        Loop
        Range("$O$6").Value = c
        Range("D5").Value = Range("C5").Value & "/" & Range("B5").Value
        Range("E5").Value = Range("C5").Value & "/" & Range("B5").Value & "^2"


    ElseIf Target.Address = "$C$5" Then
        If Target = "meters" Then
            c = 1
        ElseIf Target = "feet" Then
            c = 3.28084
        End If
'        k = WorksheetFunction.Count("$C$6:$C$1000")
        k = 126
        j = 6
        Do Until j = k
            Range("$C$" & j).Value = Range("$C$" & j).Value * Range("$O$11").Value / c
        j = j + 1
        Loop
        Range("$O$11").Value = c
        Range("D5").Value = Range("C5").Value & "/" & Range("B5").Value
        Range("E5").Value = Range("C5").Value & "/" & Range("B5").Value & "^2"
    End If


End If
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Extra Credit - change the above program to include "miles" under position units.

Pages

Excel VBA