ReDim Preserve

Loading...

来自 University of Colorado Boulder 的课程

Excel/VBA for Creative Problem Solving, Part 2

82 个评分

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

82 个评分

课程 2（共 3 门，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.

从本节课中

Arrays and Array Functions

In Week 1, you will learn all about arrays and array functions. You will learn how to iterate through arrays using nested For...Next loops and For Each...Next statements; you will learn how to import data to VBA from Excel and export data to Excel from VBA; you will learn how to create user-defined array functions; and you will learn about the ReDim Preserve statement for resizable arrays. Week 1 is concluded with a quiz, which unlocks Assignment 1.

Introduction to arrays9:17

Local arrays in VBA5:12

Importing/Exporting arrays from/to Excel8:50

Using arrays in subroutines and functions8:53

User-defined array functions6:35

Example 1: SortVector array function and ksmallest8:56

Example 2: Extracting diagonal elements from a square matrix4:29

Example 3: Residuals of simple linear regression8:42

ReDim Preserve8:29

Example: ReDim Preserve8:44

与讲师见面

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

In this screencast, I am going to talk about the ReDim Preserve Statement.

You have learned about how to use the ReDim statement when

you do not know the size of an array or a vector to begin with,

you somehow get the size and then you have to ReDim an array.

We always have to dim the vector or array in the dim statements at the top,

even if you do not know the size.

So, in this screencast,

I am going to show you how we can preserve

the elements of an array and then ReDim the size.

So, in the example I am going to show you,

we are going to make a shopping list in this first part.

It is going to ask you if you want to add an item to the list,

so we can put in something like cookies,

we could add something else like milk,

we can add, let us do a third item,

maybe some bread and then we are done.

So, would you like to- so we are going to say no and then it will

export your shopping list to the cells of the spreadsheet.

So, to begin with,

it is sort of a variable size this vector.

So, we are creating a vector here of strings in this case, it is a variable size.

We do not know the size to begin with but inside the sub routine,

we are adding items as we go along,

so we have to resize which would mean ReDim,

but we also have to preserve what is remaining in this vector.

So I am going to show you how to do that in this screencast.

The first thing you do are dim our variables.

We are going to need to dim I.

This variable ANS, short for answer and I will

explain in a minute what that is and our vector or list

vector which I am going to refer to as L. I am also

going to use option based one which is typical when we work with arrays.

Now, because we do not know how many times the user is going to want

to press Okay to add an item to the shopping list,

we are going to put this in a do loop and the first thing inside do loop,

we are going to ask the user if they want to add an item to the shopping list.

So, this is something a little bit different from what you guys have seen but I am

saying answer is equal to message box.

Would you like to add an item to the shopping list? VB, Yes or No.

So, that is Visual Basic yes or no.

This puts a Yes or No button on the message box but it is also,

because we have this on the right side of an equality with this variable answer.

It is going to take the yes or no button, whatever they press.

Yes, it is going to give a return value to this answer variable.

So, yes returns an integer value of six and

No provides an integer value or return value of seven.

And if they click No, then we want to just exit the loop.

So, if answer equals seven,

corresponding to the No button,

then we are going to exit the do and we are going to be done.

Otherwise, if we stay inside the loop,

that means the user wants to add an item and

what I am going to do is just increment i by one,

so now we have the first item in the shopping list.

And then we would ReDim, now we know the size of L,

we would ReDim L as a variant,

which is now just going to be a vector of

one component and then we can use the input box,

so we can use an input box to obtain the new items.

The first element will then be placed in the first element of L and then we loop back.

We ask the user again,

would you like to add an element.

If they select Yes,

that is a return value of six,

so the answer will be six which is not seven,

will stay inside the loop,

I will be bumped up to two.

We will ReDim L as size two now,

however this is where we run into the problem.

Whenever you ReDim, you replace all the elements in that vector with zero.

If you do not want to do that,

and in this case we want to preserve the first element that the user input,

after ReDim, I can do ReDim preserve.

So, we are going to preserve the previous elements but we are going to change the size.

So, now we have added I equals I plus one.

Now, the size is two but we are going to preserve the first element.

Then we are going to get the second element because now I is equal to two.

We are going to get L of two in an input box and we are going to keep going.

So, using this ReDim preserve,

we can resize L. In other words,

we can increase the size by one,

during each iteration of this loop but we will

preserve the previous components of this vector.

So, I have added one more line here,

range A_one to A and then I am concatenating that with I,

for example if they wanted to add four items,

then I would be equal to four,

which would mean in range A_one to A_four we are going to transpose

L vectors by default are row vectors in VBA,

but we want to transpose that for a column,

to make a column vector.

So, we would place L into Range A_one to A_four.

So, let us go ahead and see if this works.

We press F eight,

we open up the message box,

would you like to add an item to the shopping list? And we say Yes.

And you notice down here,

the return value from the message box function here is a six.

It would be a seven for No.

So, answer is not equal to six.

So, then we increase the size,

we ReDim preserve, we create L now size one,

is a vector and to get the first element of L,

we get that in a input box.

So I will say something like cookies and then we

loop and it asks them again would you like to add an item,

you say Yes, the return value for Yes is a six.

So we stay inside the loop,

I is bumped up to two.

Now, we have ReDim preserved L,

which means we resized it to now two,

but we have preserved the previous element in position one.

And then we get that in the second input box and we loop back up,

if we want to add another item,

we stay inside the loop.

We bump up to three, we resize L,

keeping the previous two elements in our vector,

we get the third element and we keep going.

And this time I will select No.

You see that the return value for No is a seven.

So, answer equals seven.

We exit two, and then in range A_one to A_three,

we are going to transpose L and we place that onto the spreadsheet.

So, this is how we can use the ReDim preserve to

kind of keep previous elements in the vector or an array.

Just a word of caution,

you can only use ReDim preserve on the last element of an array.

So, if you were making a two by two array and you wanted to resize it to a three by two,

and preserve everything in there, you can't do that.

As an example, I have deemed A here as unknown size but then I ReDim to a two by two,

I define the elements of that two by two array,

and now I want to ReDim and I want to resize the array,

I want to add a column but I want to preserve all the elements.

So, if we run through this,

we see that we create our A array and open up the first row and the second row,

and then I am going to ReDim preserve.

I am going to resize this to be a two by three,

two rows and three columns,

and when you open up A,

now we have the three columns.

And if I wanted to then I could say A_one comma three equals something,

A two comma three equals something,

but that is how you can ReDim preserve a two by two array.

However, if I wanted to add a row instead of adding a column,

I wanted to add a row,

you can't do this because with the ReDim preserve you can

only change the size of the last index in an array.

So, that would be the column index here.

So, you see when we run through this,

it doesn't like that, it does not work.

So, that gives you an idea of how to use the ReDim preserve statement in VBA.

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2018 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取