Example: Using string functions in VBA

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

127 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

127 notes

Cours 2 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

"Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet. Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference. Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

À partir de la leçon

Working with strings and .txt files

Week 2 deals with text strings and text (.txt) files. You will learn about the string functions in Excel and VBA. You will gain practice with string functions as you learn how to create functions in VBA that utilize string functions. You will learn how to extract strings, such as email addresses, from a spreadsheet of mixed string formats. You will also learn how to write information from Excel to .txt files and also to import information in .txt files to Excel. The module is concluded with a quiz and Assignment 2.

How to use string functions in Excel8:11

Example: How to create email addresses from Last Name, First Name format7:13

How to use string functions in VBA8:57

Example: Using string functions in VBA4:57

Example: Extracting email addresses from mixed string formats8:54

Example: VBA array function for separating strings into component parts8:34

Exporting data from Excel to .txt files6:49

Importing data from .txt files8:50

Importing data from tab-delimited .txt files2:15

Example: Morse coder9:27

Example: Morse decoder8:44

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

Okay, I want to show you another example, where we're going to ask the user for

a sentence.

It's going to be one big string and then we're going to spilt that string into

a vector of words using the split function in VVA.

First thing I'm going to do is dim sentence as a string.

We're going to obtain the sentence string in an input box, please enter a sentence.

I'm dimming A as an unknown sized vector as a string.

A then is going to be equal to, we're going to use the split function.

So we're going to split the sentence, and the second argument here is the delimiter.

So how are we going to decide how to split up this sentence into different elements

of our vector A?

And in that case, we're going to use just a space.

So if I run through this, just as it is right now, I could type in something.

Hello, my name is Charlie.

I press Enter, and then that's stored as the sentence you see down

here in the locals window, and we use the split function

to split that up into elements of A.

Now, you can't use by default there's always going to be

a zero element even if you put option base one at the top.

So you're always going to have a 0th element of A using

the split function in VBA.

So maybe just to show you some things that we can do with this.

I could iterate through and we can display one word at a time.

So I've added a for loop, For i, I dimmed i as an integer.

For i equals 0, remember, using the split function always

gives you a base 0 vector A even if you put option base 1 up here.

So i equals 0 to upper bound.

There's a function upper bound of A.

And we're going to just message box the element,

the ith element of A, using message box A (i).

So now when I run through this, going to enter in a sentence.

And then one at a time, we're going to message box each of the words that were

separated by the delimiter that we used, which was a space.

So we keep going.

Hi, my name is Charlie.

And finally just another example of how it can use the join function,

A is going to be a vector that has all of the things that we want to join.

We know that the previous lines work, so I'm just going to put a breakpoint there,

I'm going to run this.

Put in a sentence there and

then we output the components that are separated by spaces.

And then the last step then is we join that into one continuous sentence.

So that's how you can use the Split and Join functions in VBA.

The last thing I want to show you is how you could take strings that are in

a column, a selection on the spreadsheet, and combine those into a single word.

So this is we're going to make a selection.

First thing I'm going to do is Dim B As Object.

We're going to make msg As Variant.

It doesn't have to be strings.

We could have numbers if we wanted to.

i and nr As Integers.

I'm going to set b equal to this selection.

I am also going to use Option Base 1.

I'm going to count the numbers of rows using selection.rows.count.

Enter to a for loop, we're iterating from 1 to nr.

And then we're going to build our message,

which is going to be a previous message starting with blank.

One at a time we're going to add in the Ith element of b,

which is going to be just the rows of our selection.

So let's go ahead and run this,

we have our selection I'm going to press 8 we create b.

So b is an object, we count the number of rows and then we iterate through.

So the first letter of message is G,

which is in our thing here, which is the first row.

We can continue doing this and building up our message.

So now I take the second, third, fourth and so on.

And we take all the letters in our selection and

combine them into our message string.

And if I wanted to I could put that in a message box or

perhaps I would output that into a cell on the spreadsheet.

Whatever you are trying to do in that particular application.

So this is how you can build a single string from strings

that are in a column vector on the Excel spreadsheet.

And just to note, they don't have to be single letters.

You could have words here or numbers and what not.

And they would all be combined into a single string.

Thanks for watching.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play