Excel

Excel Visual Basic #4: Create A Web Favorites List

Homework

1. Create a way to remove an entry

If you make a mistake and you need to correct it, you don’t want to manually do it. You want to be able to click a button and have it done for you. You already have a combo box with the names of everything in the list. It seems to me you only need another button and a subroutine to carry out the work. You probably want to completely eliminate the line and have the others automatically moved up. I will give you one line of code, with that and all the code discussed here, you should be able to create an addition to this project that will remove a line at the click of a button.

Your helper code is:

Rows(rowCounter).EntireRow.Delete

 

For more information on removing rows in Excel using VBA, visit the Delete Entire Rows and Columns page.

2. Improve the URL Validation

If you can prevent having to remove a line by helping to ensure that your URLs are valid when you enter them, that would be even better. There are several ways you can go about this, I will lead you with a hint on the easiest I can think of, using InStr().

Back in part 2, we created a simple check to look for blanks. That line is below:

If DisplayName = "" Or UrlLocation = "" Then

The InStr() function is a prebuilt function that returns a boolean value – True or False. You give it a string, you tell it what you are looking for in that string. It either finds it (True) or it doesn’t (False).

For example:

InStr("Hello World", "Hello")

The above code would return True. It is checking the string “Hello World” for the existence of the string “Hello” and since it finds it, True is the boolean value it will return

InStr("Hello World", "My String")

The above code would return False. “My String” is not contained inside of “Hello World” and as a result, False is the boolean value it will return.

Using ‘InStr(UrlLocation, "https://")'we could help to make sure that every URL contains https:// before we let it put the URL in the list. We could equally do it with “.com” or “.org” or anything we want to make sure is included in that entry.

For more information on the InStr function, please visit the InStr() Function page.

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.