Excel

Excel Visual Basic #4: Create A Web Favorites List

Part 2: Add Elements to Your Source List (Video)

Part 2: Add Elements to Your Source List (Written Steps)

If you recall, we worked with subroutines in earlier tutorials. We will now work with functions. Functions and subroutines have a lot of similarities and some differences. The largest differences you are concerned with:

  1. functions return some sort of value, subroutines do not
  2. Functions are designated with a “Functions” keyword, Subroutines use “Sub”
  3. Functions work like variables in that they should have a type declared.
  4. A value should be assigned to the Function so that it can be returned

We need a way to reference cells on the “Source List” tab, but not know exactly which cells we need to reference.  We will keep things simple, and just make two functions, one for column A and one for column B. The idea behind these functions is that we will send them a row number and get back a string containing the cell we need when we need it.

Next, we will put some code in our “Add_Fave” subroutine that was auto-created for us by clicking the “Add to Favorites” button. The job of this code is to move text from our text boxes to the “Source List” tab so we can save that text for later.

If you have not already done so, ensure that the file you created in Part 1 is open and you have access to the code. To get to the code, go to the “Developer” tab and on the left-hand side you will see a button labeled “Visual Basic.” Click that button and it will open up your developer panel with the code for the current book (and all Excel books you have open).

image9

1. Create your own dynamic functions

Our two functions:

Private Function DisplayNameRange(rowNum As Integer) As String
     DisplayNameRange = "A" & CStr(rowNum)
 End Function
 Private Function URLRange(rowNum As Integer) As String
     URLRange = "B" & CStr(rowNum)
 End Function

img8

We have named our functions “DisplayNameRange” and “URLRange” and assigned them to a type of String (using “As String”). Both functions require a variable (more appropriately this is called a parameter when used this way), that variable is titled “rowNum” and it is also assigned to a type of Integer.

CStr(rowNum)

The section CStr(rowNum) changes the Integer value to a String so that it can be combined with the “A” or “B” of the column header, making the line “A” & CStr(rowNum) equal to the cell, or A and the integer value we pass to the function as a parameter. We set that equal to the name of our function so that when the function is called, if it is called correctly, it returns a String “A(rowNum).”

2. Add variables to the “Add_Fave” subroutine

We want to hold the values in the text boxes we created in Part 1 in a variable and we also want a way to track which row we are on in the “Source List” so we can pass that value to the dynamic functions we just created.

Find your “Add_Fave_Click()” subroutine in the developers pane.

Private Sub Add_Fave_Click()               

End Sub

Img7

In between “Private Sub Add_Fave_Click()” and “End Sub” Add the following 3 variables (If you are unsure about variables please check out Excel Visual Basic #2: Variables and Decisions):

Dim DisplayName As String 'The display Name for the URL
 Dim UrlLocation As String 'The location you want to navigate to
 Dim rowCounter As Integer 'Counter used to help find the first empty row in the source list

“DisplayName” and “UrlLocation” are set to type String, and “rowCounter” is set to type Integer and they will hold the values from the text boxes. “rowCounter” will be used to keep track of the current needed row on the “Source List” tab.

We also need to set the “rowCounter” variable to the first available row on the “Source List.” If we take a look at the source list file, we will see that the first row available is 2.

image9A.png

Add the following code after your variable declarations.

rowCounter = 2 'First available row on the source list

Your total code at this point should look like the following:

Private Function DisplayNameRange(rowNum As Integer) As String
     DisplayNameRange = "A" & CStr(rowNum)
 End Function
 Private Function URLRange(rowNum As Integer) As String
     URLRange = "B" & CStr(rowNum)
End Function
 'When the Add to Favorites Button is Clicked
 Private Sub Add_Fave_Click()
     Dim DisplayName As String 'The display Name for the URL
     Dim UrlLocation As String 'The location you want to navigate to
      Dim rowCounter As Integer 'Counter used to help find the first empty row in the source list
     rowCounter = 2 'First available row on the source list
End Sub

image10

Now, we need to get the value of the text boxes and assign them to the variables. To do that, we need to know the names of the text boxes. Within the tutorial, we named those two text boxes, “New_DisplayName” and “New_URL.” To get a value from one of them we only need to add “.Value” after it.

Below where you assigned the variable rowCounter to 2, place the following lines:

DisplayName = New_DisplayName.Value
 UrlLocation = New_URL.Value

Your total code should resemble the image:

image11

3. If/Else and Data Verification

The next thing we will do is make sure that the text boxes actually contain something. It is possible to get more advanced in your value verification than what I have below, but we are going to keep it simple and just look for a blank entry.

Still working in the “Add_Fave_Click()” subroutine, we need to create an If…Then…Else Decision structure. These are covered in Excel Visual Basic #2: Variables and Decisions if you would like to go over them again. We are just going to check to make sure our variables “DisplayName” and “UrlLocation” contain something. We don’t care what, it just has to have something in it. We will add the following code:

'Make sure the textboxes contained values
 If DisplayName = "" Or UrlLocation = "" Then
         MsgBox "One of the required values is missing!"
 Else

 End If

If you recall from Excel Visual Basic #1: Getting Started, MsgBox creates our pop-up boxes. So if the application detects a blank entry in the text boxes, it will create a pop-up box warning you that “One of the required values is missing!”

“Add_Fave_Click()” should resemble the image below:

image12.png

4. Find the Next Free Spot in “Source List” and Copy the Values Over

If you are unfamiliar with loops, check out Excel Visual Basic #3: Loops, Combo Boxes, and Saving.

What we need to do now is get the values from those text boxes and place them in our source list. We have a problem though. Each time we run it, we really don’t know how many values are already in the list and we cannot let it overwrite the existing values. The point is to create a dynamic flowing list that we can add to with little thought or effort on the part of the user.

We can do several things, but we are going to go with having a loop check the values to look for the next blank entry. We do this with the below code:

While Not ThisWorkbook.Sheets("SourceList").Range(DisplayNameRange(rowCounter)).Value = ""
      rowCounter = rowCounter + 1
 Wend

The above code uses one of the functions we created earlier, “DisplayNameRange.” This block of code scans through cells in column “A” using “DisplayNameRange” and counts until it reaches a blank cell. Each time the rowCounter variable is increased by 1 and that new value is sent to “DisplayNameRange” with “DisplayNameRange(rowCounter).”

The above loop only ends when it finds a blank. This means you could put in a thousand values (it would start getting slow) and it won’t care. It will just look for the first blank cell, counting up the entire way. We can then use the rowCounter variable and those functions we created earlier to find the next available cell to place our information in.

To do that, we just need to take the values stored in our variables and set them equal to the cell, like the below code:

ThisWorkbook.Sheets("Source List").Range(DisplayNameRange(rowCounter)).Value = DisplayName
ThisWorkbook.Sheets("Source List").Range(URLRange(rowCounter)).Value = UrlLocation

Altogether, your current code should look like the code below:

image13.png

5. Test It!

It should take the values entered in the text box, and with a click of a button, send those values to the “Source” List tab. We will finish the rest in Part 3.

image14

image15.png

 


Next Page: Part 3

Pages: 1 2 3 4 5

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.