Creating and Using Templates

In my past Excel projects, I’ve had to show pages with the same layout but different content. I could have created a separate page for each item I needed to show, but what if you have to show more pages than 10 pages? It’s tedious to create 10+ separate pages of data, so instead, this tutorial will teach you how to create a template that you can fill with data depending on what item was selected.

1) Create three tabs: Home, DataSheet and TemplateSheet

Think of using templates like a room with many doors. The room’s contents change depending on which door you enter from, not unlike the Room of Requirement in Harry Potter.

The Home tab is where you’ll store the “doors”, and the TemplateSheet is the “room”. The DataSheet is the connection between these two and it determines the “room’s” contents.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2) Add Data

Right, now we need some data in the DataSheet.

Let’s keep it simple and use numbers to words like this:

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3) Create The Doors

Okay, let’s go back to the Home tab and create some buttons which will be our doors.

Just use some basic shapes like mine:

IMPORTANT: Rename your shapes to “1”, “2”, “3”, “4” and “5” respectively. To do this, click on a shape, and change the name here:

REMEMBER TO PRESS “ENTER” AFTER YOU CHANGE THE NAME, OR ELSE THE NAME WON’T CHANGE.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

4) Let’s code!

Open up your VB Editor (Alt + F11) and double click ThisWorkbook.

Type this in:

Application.Caller is a read-only value that gets the name of the object that was clicked. In this case, our doors. So when you click on Door 1, the name is “1”, so that is what the code will look for in DataSheet.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

5) Assign the macro to all the buttons.

Yep. Just do that and you’re done! Have fun.

Try changing the values in the DataSheet and the names of the corresponding shapes. Add more doors, change the template! Go experiment and try different things. 😀

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Like this tutorial? Subscribe and/or share this with your friends!

If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Dictionaries in Excel VBA

A Dictionary is a Collection object, like an array, with keys and values.

In one of our projects, we found that using Dictionaries was more flexible than using arrays, so this blog post is going to teach you how to create and handle dictionaries in VBA.

1. Access to Dictionary Use

First, you’re gonna have to gain access to use dictionaries, or VBA will throw you an error.

So, follow these steps:

1. In the VBA Editor, go to Tools->References

2. Check “Microsoft Scripting Runtime”

3. Click “OK”

And that’s it. You can start making and using dictionaries now.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2. Create a Dictionary

We’ll use a function for this, as we need to be able to return the dictionary as a value. In this example, I’ll be making a dictionary that changes column letters into numbers.

 Visual Basic |  copy code |? 
  1. Function DictionaryMaker() as Dictionary 'this indicates that the value
  2. 'returned will be in a dictionary, as opposed to integers or strings
  3.     
  4.     'creates the actual dictionary to be filled
  5.     Dim ColumnSwitch as New Dictionary
  6.     ColumnSwitch.Add 1, "A" 'the .Add function adds values to the dictionary
  7.     ColumnSwitch.Add 2, "B"
  8.     ColumnSwitch.Add 3, "C"
  9.     ColumnSwitch.Add 4, "D"
  10.     Set DictionaryMaker = ColumnSwitch
  11. End Function

Let’s break it down a little. As stated in the comments, “Dim ColumnSwitch as New Dictionary” is the line that creates the actual dictionary to be filled.

The syntax for adding values to the dictionary is as follows:

[Dictionary Name].Add [Key], [Value]

The last bit “Set DictionaryMaker = ColumnSwitch” is to assign the dictionary to the function so that it can be used in another subroutine or function.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3. Retrieve values from the Dictionary

So now we’re gonna need to retrieve and use those values that we stored in DictionaryMaker.

 Visual Basic |  copy code |? 
  1. Sub Example()
  2.     MsgBox DictionaryMaker.Item(1) 'code to retrieve the value
  3. End Sub

So this subroutine will display “A” in it’s message box. The code to retrieve the exact value using the key is this:

[Dictionary Name].Item([Key])

Since my key is an integer, I can just put a number. If the key was a string, then remember to include the double inverted commas!

So in this way, if I’d changed the code to “MsgBox DictionaryMaker.Item(2)” then it would have displayed “B”. Go ahead and try it!

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Like this tutorial? Subscribe and/or share this with your friends!

If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Extract Tags

Recently we answered a question on StackOverflow Link to the Stack Overflow question

I have a column containing text (column A in Screeshot), which has some tags (text surrounded by < >), I would like to find all these tags in a cell and copy it to the neighbouring cell (Column B in screenshot), basicaly I want to make a list of tags in column A.

Here’s the code from the answer, complete with comments. Note that it is NOT exactly the same as the one in the video.

 Visual Basic |  copy code |? 
  1. Sub ExtractTags()
  2. Dim ColA As Integer
  3. Dim ColB As Integer
  4. Dim Row As Integer
  5. Dim Content As String
  6. Dim Tags As String
  7. Dim CurrentTag As String
  8. Dim OpenTag As Integer
  9. Dim CloseTag As Integer
  10. Dim NumOfTags As Integer
  11. ColA = 1 'this marks column A
  12. ColB = 2 'this marks column B
  13. Row = 2 'this marks the Row, which we'll increment 1 by 1
  14.         'to make the code go thru each row
  15. Do
  16.     'extracts the content for manipulation
  17.     Content = Sheets("Sheet1").Cells(Row, ColA).Value
  18.     If InStr(1, Content, "<", vbBinaryCompare) Then 'This checks to see if  
  19.                         'there are any tags at all. If there are, we go in
  20.         Position = 0 'starting position of the search
  21.         NumOfTags = 0 'keeps track of multiple tags in a single cell
  22.         Do
  23.             'each time this part loops, it cuts out the first tag and
  24.             'all the content before it so that the code can hit the
  25.             'first instance of "<" of the remaining content of the cell
  26.             Position = InStr(Position + 1, Content, "<", vbBinaryCompare)
  27.             NumOfTags = NumOfTags + 1 'since we have a tag,
  28.                                       'increment the counter by 1
  29.             
  30.             'beginning of tag
  31.             OpenTag = InStr(Position, Content, "<", vbTextCompare)
  32.             
  33.             'end of the tag
  34.             CloseTag = InStr(Position, Content, ">", vbTextCompare) - 1
  35.         
  36.             'cuts out the content after the tag
  37.             CurrentTag = Left(Content, CloseTag)
  38.             'cuts out the content before the tag
  39.             CurrentTag = Right(CurrentTag, Len(CurrentTag) - OpenTag)
  40.             If NumOfTags = 1 Then 'check to see if we've already got tags
  41.                 Tags = CurrentTag 'if this is the first tag, just put it in
  42.             Else
  43.                 Tags = Tags & ", " & CurrentTag 'for the second tag onward,
  44.                                                 'add a comma to separate tags
  45.             End If
  46.         'this is the checker to see if there are anymore tags in the content
  47.         Loop Until InStr(Position + 1, Content, "<", vbBinaryCompare) = False
  48.         'input all the tags into column B
  49.         Sheets("Sheet1").Cells(Row, ColB).Value = Tags
  50.     End If
  51.     Row = Row + 1 'move on to the next row
  52. Loop Until Content = "" 'if the next row is empty, we stop
  53. End Sub

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Locking and Unlocking sheets with VBA

In one of my old school projects, I had to create a system on Excel that had some level of security. I decided to use usernames and passwords for my system, and somehow had to store and protect those pieces of data on one of the sheets. I knew I’d have to be able to manipulate the data on the fly, but I didn’t want to leave it exposed to the users. I finally found out how to lock and unlock sheets using VBA code, so that’s what I’ll be sharing with you today.

1. Create the code
The actual lines of code to lock and unlock sheets are:

 Visual Basic |  copy code |? 
  1. Sheets("Sheet1").Protect Password:="password"
  2. Sheets("Sheet1").Unprotect Password:="password"

That’s simple enough. I turned them into separate subs since I didn’t need to return a value, so that I didn’t have to copy and paste the lines. I could just call them. Here are the actual subs.

 Visual Basic |  copy code |? 
  1. Sub UnlockSheet()
  2.     If Sheets("Sheet1").ProtectContents = True Then
  3.         Sheets("Sheet1").Unprotect Password:="password"
  4.     End If
  5. End Sub

 Visual Basic |  copy code |? 
  1. Sub LockSheet()
  2.     Sheets("Sheet1").Protect Password:="password"
  3. End Sub

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2. Call the subs
So now that we’ve got them, lets use them!

 Visual Basic |  copy code |? 
  1. Sub SampleCode()
  2.     Call UnlockSheet
  3.     'some codes that manipulate the data
  4.     'eg add new user
  5.     'or change password
  6.     Call LockSheet

Now this was really useful, and allowed me to create more functions such as adding new users to my system, or helping existing users to change their password.

The next time you need to be able to manipulate data in a protected sheet, use this!

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Just a little FYI, I had what I called my “5 Levels of security”, and I’m pretty proud of it because I created everything on my own. My 5 levels were:
1. I hid the sheet
2. Locked the sheet
3. Made the font the same colour as the background so that it was essentially invisible
4. Made each user have a password
5. Hashed the password

P.S. I got an A for this project. 😀

Userform Part 2 (Coding a Userform)

Hi! Welcome back to part 2 of creating a userform. If you haven’t seen part 1, do so now, because this is a direct continuation of that.

1. Calling the Userform
If you want to attach this userform to a button on the page (which I recommend you do) here’s how to do it:

Create a new sub, but make sure the name is different from the Userform’s name, or else it will confuse the program and it won’t work.

 Visual Basic |  copy code |? 
  1. Sub AddNewUser()
  2.     NewUser.Show 'bring out the userform
  3. End Sub

Alright, let’s move on to the actual coding!

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2. Coding the userform
In the Project window, right click the NewUser userform and click View Code.

We’re gonna code this in 3 parts: When the userform appears, when the OK button is clicked, and when the Cancel Button is clicked.

Let’s start with the easiest: the Cancel Button. What we want to happen is, when the Cancel button is clicked, close the userform and don’t input anything. It’s so simple it only requires 1 line of code.

 Visual Basic |  copy code |? 
  1. Private Sub CancelButton_Click()
  2.     Unload Me
  3. End Sub

You don’t actually have to type the whole thing out. You can double click the Cancel Button on the userform and then all that’s left is to type “Unload Me”.

Next, the OK Button.

We want to check the fields first, to make sure they’re all filled up, if not we can’t enter the data into the database. So let’s code that first.

 Visual Basic |  copy code |? 
  1. Private Sub OKButton_Click()
  2.     If NameInput.Value = "" Or YearInput = "" Then
  3.         MsgBox "Please fill in all the fields"
  4.         Exit Sub
  5.     End If
  6.     
  7. End Sub

Now obviously we don’t need to check for the Day, Month and Gender, as there are already default values. Remember, we’re just checking to make sure all the fields are filled.

After that, we’ll need to find the next empty row, so we’ll make a new variable and a do loop to find the next blank row.

 Visual Basic |  copy code |? 
  1. Private Sub OKButton_Click()
  2.     Dim Row As Integer
  3.     
  4.     Row = 1
  5.     If NameInput.Value = "" Or YearInput = "" Then
  6.         MsgBox "Please fill in all the fields"
  7.         Exit Sub
  8.     End If
  9.     
  10.     Do
  11.         Row = Row + 1
  12.     Loop Until Sheets("Sheet1").Cells(Row, 1).Value = ""
  13. End Sub

Now that we’ve found an empty row, let’s go ahead and put in the values.

 Visual Basic |  copy code |? 
  1. Private Sub OKButton_Click()
  2.     Dim Row As Integer
  3.     Dim NameCol As Integer
  4.     Dim DOBCol As Integer
  5.     Dim GenderCol As Integer
  6.     
  7.     Row = 1
  8.     NameCol = 1
  9.     DOBCol = 2
  10.     GenderCol = 3
  11.     
  12.     If NameInput.Value = "" Or YearInput = "" Then
  13.         MsgBox "Please fill in all the fields"
  14.         Exit Sub
  15.     End If
  16.     
  17.     Do
  18.         Row = Row + 1
  19.     Loop Until Sheets("Sheet1").Cells(Row, NameCol).Value = ""
  20.     
  21.     Sheets("Sheet1").Cells(Row, NameCol).Value = NameInput
  22.     Sheets("Sheet1").Cells(Row, DOBCol).Value = DayInput & "/" & MonthInput & "/" & YearInput
  23.     
  24.     If MaleInput.Value = True Then
  25.         Sheets("Sheet1").Cells(Row, GenderCol).Value = "Male"
  26.     ElseIf FemaleInput.Value = True Then
  27.         Sheets("Sheet1").Cells(Row, GenderCol).Value = "Female"
  28.     End If
  29.     
  30.     Unload Me
  31. End Sub

Notice I created 3 more variables for the columns. The way I put in each value is different as well. For the Name, it’s pretty straightforward, just simple assigning will do. For the date, I glued together DayInput, MonthInput and YearInput by a slash. Now you can change the slash to a dash or fullstop, that’s up to you. For the Gender values, I used an If/Else statement to find which value was true, and then input the correct value accordingly. This will get longer if you have more than 2 options.

But wait! We still have one last problem: No values in the dropdown buttons for Day and Month.

This is part 3: When the userform appears.

The code we need is called AddItem. Basically, DayInput.AddItem “02” will add the value 02 to the dropdown list of DayInput. It seems silly to repeat this line 31 times, so let’s take a short cut using a do loop.

 Visual Basic |  copy code |? 
  1. Sub Userform_Initialize()
  2.     Dim DayCount As Integer
  3.     Dim MonthCount As Integer
  4.     
  5.     DayCount = 1
  6.     MonthCount = 1
  7.     
  8.     Do
  9.         DayInput.AddItem "0" & DayCount
  10.         DayCount = DayCount + 1
  11.         
  12.         MonthInput.AddItem "0" & MonthCount
  13.         MonthCount = MonthCount + 1
  14.     Loop Until DayCount = 10
  15.     
  16.     Do
  17.         DayInput.AddItem DayCount
  18.         DayCount = DayCount + 1
  19.     Loop Until DayCount = 32
  20.     
  21.     Do
  22.         MonthInput.AddItem MonthCount
  23.         MonthCount = MonthCount + 1
  24.     Loop Until MonthCount = 13    
  25. End Sub

Note the 3 different do loops here.

The first loop adds the days and months together as strings with a zero in front, because if you add them as integers, the zero won’t appear.

The second loop adds the rest of the days as integers, since it’s simpler to do so. Same for the third loop and the last 2 months.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3. Have fun!
Congratulations! You’ve created you’re very own userform. Go have fun changing it suit your own needs. 😀

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Userform Part 1 (Creating a Userform)

When you’re doing a project that requires user input, a userform is a very simple and flexible tool to use. You can customize to it take in multiple pieces of data at once, even if they’re all different types.

So, how do we make one? We’ll be creating a userform that adds a new “user” to a “database” for this tutorial. The database is just one of the sheets.

1. Summon the userform
Right click on the Project window of your VBA editor, then mouse over Insert, and select Userform. Like this:

You should get something like this:

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2. Rename the userform
So since we need this userform to add a new user, we should choose a sensible name, like “New User”.

In the Properties window (press F4 if it’s not there), find the field called “(Name)” and just click on the adjacent field to it to rename the userform. Note that you cannot have any spaces or punctuation in the name.

To change the title of the userform when it pops up, find the field called “Caption” and change that. In this field, you can use any characters, because this is for display purposes, not identification.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3. Add Fields and Labels
Right. Now that we’ve got that, we can start adding fields and labels to the userform. We need to create the fields in the database first, so I’ll use “Name”, “D.O.B.” and “Gender” just to show you the different types of functions we can use. Let’s put these headers in the sheet before we continue.

Okay, now that’s done, we know what fields we want in the userform.

Let’s add a title first. From the Toolbox window (View–>Toolbox), drag the Label (top row, 2nd from left) to the center of the userform like this:

To rename it, just click on it. You can also go to the Properties window and change the “Caption” field again. It’s a little small now, so let’s increase the font size. Find the “Font” field and click the ellipses button (…) that appears beside it.

From here, you can select the Font, Style, Size and Effects. I’m just going to increase the font size to 22. Click OK. You’ll notice that the words are now too big for the field, so expand it. I like to expand the field to stretch across the entire userform left to right, then centralize the content so that it’s perfectly in the center, but that’s just my quirk. If you want to make it centralized like me, scroll down to find the “TextAlign” field, and fiddle with that.

*Optional* Rename the label in the “(Name)” field to TitleLabel.

Let’s add the other labels.

Everything is the same as the Title Label except that the font sizes for “Name”, “D.O.B” and “Gender” are 20 instead of 22. I’ve also renamed them to “NameLabel”, “DOBLabel” and “GenderLabel” respectively. It’s my habit to make sure everything is properly defined, because I don’t like having to refer to them as “Label1”, “Label2” and “Label3” in the codes. It gets confusing.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

4. Add a text box, dropdown buttons and radio buttons
Now we can add the parts that will take in the actual data.

I’ll be using TextBox for the Name, 2 dropdown buttons and a TextBox for the D.O.B, 1 dropdown button each for Day and Month, TextBox for the year, and 2 radio buttons for the Gender. You can use a dropdown for the Gender too, if you like.

To make the TextBox for the Name:

  • Drag it from the Toolbox (it’s the 3rd button from the left on the top row)
  • Increase the font size to 18, but again, it’s entirely up to you to customize
  • Drag out the corners to increase the width to the end of the userform
  • Align it nicely with it’s label
  • Rename this “NameInput”

For this tutorial, it’s compulsory to rename the input boxes to something sensible, or it will confuse you when you start coding. I recommend you rename it to the same as my names, so that it will be easier to follow.

Moving on to the D.O.B:

  • Drag the ComboBox from the Toolbox window (it’s in the top row, last button)
  • Adjust the height and width and rename it to DayInput
  • Do the same for the second one, naming it MonthInput
  • Make another TextBox for YearInput and adjust everything so that it fits nicely

You can click the ComboBoxes and type “01” for each of them to set them as the default value.

Last part: the Gender radio buttons. Here, it’s called an Option Button, so:

  • Drag it onto the userform beside the Gender label
  • Again, you can increase the font size
  • Do one for Male and one for Female.
  • Find the Value field for Male and change it to True. Leave the Female option False

Once everything is done, it should look like this:

Stop and do this now.

If this step doesn’t work for you,Tell Us!

5. Command Buttons
The last thing you want to add to the userform is some command buttons. I like the standard stuff like “OK” and “Cancel”, so let’s put those in.

To put in a command button:

  • Stretch the Userform down a little to make more space for the buttons
  • Drag the Command Button from the Toolbox (3rd row, 2nd button) to the userform
  • Change the font size, the “Caption” to “OK” and rename to “OKButton”
  • Do the same for the “Cancel” button

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Stay tuned for part 2 next week!

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Inserting new Rows and Columns by VBA Code

Sometimes, you need to be able to create new rows or columns as your code a working. This may occur when you need to update a particular section of Excel, without affecting the subsequent section.

So, how do we do it?

The actual line of code is simple:

 Visual Basic |  copy code |? 
  1. Sheets("Sheet1").Range("1:1").Insert 'insert a row

 Visual Basic |  copy code |? 
  1. Sheets("Sheet1").Range("A:A").Insert 'insert a column

Lets break this down.

Sheets(“Sheet1”) –> This identifies the sheet in which you want to insert the row/column into

.Range(“1:1”) –> This identifies the row/column you want to insert from. Note that Excel will insert a row/column BEFORE the indicated row/column. The Range function allows you to use Letters to identify columns, so you don’t have to use numbers like in the Cells function. (Click here to learn more about this)

.Insert –> This is the actual command to insert a new row or column. Simple and straightforward.

You can actually determine the number of rows or columns you want to insert. Just change the character after the colon to add in the appropriate number of rows/columns. Remember that excel counts the first character as 1 insert already, so be aware of how many row/columns you’re adding!

Case in point:

 Visual Basic |  copy code |? 
  1. Sheets("Sheet1").Range("3:5").Insert

This code will add 3 (not 2!) rows starting from row 3.

And this code

 Visual Basic |  copy code |? 
  1. Sheets("Sheet1").Range("D:G").Insert

will add 4 (D, E, F, G) columns starting from column D.

And that’s it! Have fun inserting those rows and columns!

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Retrieving Values

With any program, you’re gonna need values. Whether these are set values hard coded into your project or user inputs, more often than not you’re gonna need to store, retrieve and manipulate these data. So, how do we do that in Excel?

1. Hard coding values
So the simplest way to store a value is to hard code it right?

 Visual Basic |  copy code |? 
  1. Function Add()
  2.     Dim a as Integer 'the variable a is now defined and set to store integer values
  3.     a = 1 'the variable a is now set as the number 1
  4. End Function

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Right, that was simple enough. Now obviously this is not a complete code. The point here is that the number 1 is assigned to the variable a, and this is a set value. This means that if you want to change the value of a, you need to come to the code again to change it. Not very effective when you want to run the code multiple times with differing values for a, right?

Well, lets move on to the next part.

2. Retrieving values from input boxes
Right. Lets create a subroutine that takes in some user input.

 Visual Basic |  copy code |? 
  1. Sub Intro()
  2.     Dim Name as string 'We'll use this to capture the name of the user from an input box
  3.     Name = InputBox (Prompt:="What is your name?") 'Asks the user for his/her name
  4.     MsgBox "Hello, " & Name 'greets the user
  5. End Sub

See, it’s simple! Just one line of code, and you can start having your program interact with the user.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3. Retrieving values from cells in sheets
Often you’ll need to be able to capture data from cells in one or more sheets. Here’s the example code before I explain in detail.

 Visual Basic |  copy code |? 
  1. Sub DataRetriever()
  2.     Dim CellContent As String 'as usual, we need a variable
  3.     CellContent = Sheets("Sheet1").Cells(1, 1).Value 'this line captures the data in a cell
  4.     
  5.     MsgBox CellContent 'display the captured data
  6. End Sub

Right, lets break it down. Notice the full stops breaking “Sheets(“Sheet1″).Cells(1, 1).Value” into 3 pieces? Here’s what each piece means.

“Sheets(“Sheet1″)” – This identifies which sheet you want to collect the data from. The “Sheet1” in the brackets is actually the name of the sheet itself. I could change the name of the sheet to, for example, “DataSheet”, and all I’d have to do is change “Sheets(“Sheet1″)” to “Sheets(“DataSheet”)”. Do note that there are some symbols you can’t use in naming a sheet. The reason is explained in a previous blog post here.

“.Cells(1, 1)” – This part identifies the exact cell you wish to get the data from. The syntax goes like this: Cells(ROW, COLUMN). So the first number identifies the row, and the second number identifies the column. Now, it’s easy to get the row number, just read it off the side. The column gets a little trickier, especially when you have to deal with 30 plus columns. Each letter corresponds to a number, i.e. A=1, B=2. After column Z, it becomes AA, AB, AC and so on. Its a simple continuation (i.e. AA=27, AB=28), but tedious to “decipher”.

“.Value” – This tells the code what you want from the cell, or what you want to do to the cell. “Value” indicates content, but other things such as “Copy”, “Paste”, “Select”, or “ClearContent” will each do different things.
Note: Since “.Value” is content, you can assign it to a variable, but the other commands cannot be assigned because they are actions.

This is more complicated than the first 2 ways of retrieving data, but much more useful.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

4. Passing values TO subs and functions
In order to pass variables to subs and functions, the receiving sub/function will have to determine which values are to be passed. Since both receiving subs and functions work the same, I’ll use a Sub as an example.

 Visual Basic |  copy code |? 
  1. Sub Welcome()
  2.     Dim a As String 'variables
  3.     Dim b As String
  4.     Dim WelcomeMsg as string
  5.     a = "hi" 'assigning values
  6.     b = "wassup"
  7.     
  8.     Call Salutation(WelcomeMsg, a) 'call the sub
  9. End Sub

 Visual Basic |  copy code |? 
  1. Sub Salutation(WelcomeMsg, a)
  2.     WelcomeMsg = a & b
  3. End Sub

Okay so in this example, I created 2 subs Welcome and Salutation, and 2 variables of “Hi” and “Wassup”. Now this code will not work for variable b, because it is not identified. If you try this now, the code will execute, but WelcomeMsg will display only “hi”. So to fix this, we have to identify variable b in Salutation like this:

 Visual Basic |  copy code |? 
  1. Sub Salutation(WelcomeMsg, a, b) 'now b is identified
  2.     WelcomeMsg = a & b
  3. End Sub

And we also have to make sure Welcome passes variable b, like this:

 Visual Basic |  copy code |? 
  1. Sub Welcome()
  2.     Dim a As String 'variables
  3.     Dim b As String
  4.     Dim WelcomeMsg as string
  5.     
  6.     a = "hi" 'assigning values
  7.     b = "wassup"
  8.     
  9.     Call Salutation(WelcomeMsg, a, b) 'now b will be passed to Salutation
  10.     MsgBox WelcomeMsg
  11. End Sub

And NOW this code will work.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

5. Passing values FROM subs and functions
Alright, lets discuss how to pass values FROM subs and functions. Actually, for subs, you can look at Welcome, and see how the variables a and b were passed to Salutation. That pretty much sums it up for subs.

So I’ll show you the function now. I’ll be using the same sub as before, but modified a little.

 Visual Basic |  copy code |? 
  1. Sub Welcome()
  2.     Dim a As String 'variables
  3.     Dim b As String
  4.     
  5.     a = "hi" 'assigning values
  6.     b = "wassup"
  7.     
  8.     MsgBox Greeting (a, b)
  9. End Sub

 Visual Basic |  copy code |? 
  1. Function Greeting(a, b)
  2.     Greeting = a & b
  3. End Function

Okay these pair of codes need to pass data to each other. For Greeting to pass the result back to Welcome, it itself needs to become the variable.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

6. Public variables
Alright! Last part: Public Variables. When you declare a variable public, it means any sub or function can use it and you don’t have to pass it back and forth. Lets modify the previous example again.

 Visual Basic |  copy code |? 
  1. Public a As String

 Visual Basic |  copy code |? 
  1. Sub Welcome()
  2.     Dim b As String
  3.     
  4.     a = "hi" 'assigning values
  5.     b = "wassup"
  6.     
  7.     MsgBox Greeting
  8. End Sub

 Visual Basic |  copy code |? 
  1. Function Greeting
  2.     Greeting = a & b
  3. End Function

So what have I done? I’ve pulled variable a out of Welcome, and declared it public. I’ve also removed the required values of Greeting, so now the codes are not passing information to each other. Now when I run the code, only “hi” is displayed. This is because variable b is not public. That means variable b can only be used within Welcome. However, since a is public, any and all subs and functions can affect it. I can assign it a value in one sub, and use it in another function, like in the example.

Go ahead, try moving variable b to public and watch the magic happen!

Stop and do this now.

If this step doesn’t work for you,Tell Us!

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Calling Functions and Subroutines

If you’ve ever done an Excel VBA project, you’ll probably have done calling of functions and/or subroutines. This is an excellent tool to use to make your project really stable. Being able to create and call functions allows you to reuse those functions anywhere in the project without you having to rewrite them. This also allows you to make changes to the function in just one place, rather than having to change several places that all make use of the same function.

So this is an example of how to do it.

1. Create the subroutine that will call the function
CallFunctionExample is the subroutine that will call the function

 Visual Basic |  copy code |? 
  1. Sub CallFunctionExample()
  2.     Dim a as Integer 'setting the variables to be used
  3.     Dim b as Integer
  4.     Dim Sum as Integer
  5.     a = 1
  6.     b = 2
  7.     Sum = Add(a,b)
  8.     
  9.     MsgBox Sum
  10. End Sub

Stop and do this now.

If this step doesn’t work for you,Tell Us!

2. Now make the function itself
This is the Add(a,b) function

 Visual Basic |  copy code |? 
  1. Function Add(a,b)
  2.     Add = a + b
  3. End function

Refer to the Function tutorial for how functions are structured and how they work.

Now the point of separating the adding part is so that it allows you to use it in another part of the subroutine, or even in a completely different sub.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

3. Now you can run CallFunctionExample
Press F5 and see it work!

Stop and do this now.

If this step doesn’t work for you,Tell Us!

4. Calling other Subs
Sometime you want to link 2 subs together, or put one sub into another, but don’t want to create one giant sub. So, this is how you do it:

 Visual Basic |  copy code |? 
  1. Sub CallSubExample()
  2.     Dim name as string
  3.     name = InputBox (Prompt:="What is your name?")
  4.     Call SubAnswer
  5. End Sub

Stop and do this now.

If this step doesn’t work for you,Tell Us!

5. Make the other Sub

 Visual Basic |  copy code |? 
  1. Sub SubAnswer(name)
  2.     
  3.     MsgBox "Hello, " & name
  4. End Sub

Note that I do not need to redefine the variable ‘name’ in SubAnswer. This is because it uses the exact same variable as in CallSubExample.

Stop and do this now.

If this step doesn’t work for you,Tell Us!

6. Now you can run CallSubExample
Again, press F5 and watch it.

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!

Difference between Subroutines and Functions (Part 2)

Hey guys, welcome to part 2! If you haven’t seen part 1, click here.

Righto! Here’s the second difference:
Functions can be used to return a value, but subroutines generally can’t. I know in Part 1 I said that each cannot do the other’s job, but in this case, I mean that its actually easier and more efficient for a function to do the job of returning a value.

Let me show you:

I created this simple project to showcase this specific difference between a Subroutine and Function.

And here they are for you to try it yourself:

 Visual Basic |  copy code |? 
  1. Sub HelloWorld()
  2.     Dim a As Integer 'variable to store integer 1
  3.     Dim b As Integer 'variable to store integer 2
  4.     Dim Answer As Integer 'variable to store the answer from the Sub
  5.     
  6.     a = 1 'assigning the value of 1 to variable a
  7.     b = 2 'assigning the value of 2 to variable b
  8.     
  9.     Call AdderSub(a, b, Answer) 'this line means: "Give these variables to AdderSub, he'll know what to do"
  10.     
  11.     MsgBox Answer 'Display the answer from AdderSub
  12.     MsgBox AdderFunction(a, b) 'Display the answer from AdderFunction
  13. End Sub

 Visual Basic |  copy code |? 
  1. Sub AdderSub(FirstNumber, SecondNumber, Result) 'This part receives the variables
  2.     Result = FirstNumber + SecondNumber 'Code to add the integers
  3.     
  4. End Sub

 Visual Basic |  copy code |? 
  1. Function AdderFunction(FirstNumber, SecondNumber) 'This part receives the variables
  2.     AdderFunction = FirstNumber + SecondNumber 'Code to add the integers
  3. End Function

So, as you can see here, I’ve created AdderSub and AdderFunctions, which both basically do the same thing: they add together 2 integers. You’ll also notice that the first Sub has a Call function, which I’ll cover in another tutorial.

Now, the HelloWorld Sub will be able to display both answers, but you can see that when I use the function, I shave away 1 variable. Now this may not seem like much, but imagine a project with many subs and functions, and you’ll see how many extra variables you’d have to use if you just stick to using subroutines.

Also, you save 1 more line of code in HelloWorld when you use the function instead of the sub. This is because the function itself stores the result, and can therefore be displayed or used immediately.

So that’s it! That’s the difference between Subroutines and Functions! Make sure to use each one appropriately, so that your project is efficient and streamlined.

Like this tutorial? Subscribe and/or share this with your friends!
If this has helped you, or if you’re interested to learn more, subscribe to us, and you’ll be the first to know when something happens, be it a new post or a major event. You can also ask us questions or suggest topics for us to blog about. Who knows? You could be responsible for our next blog post!