Save lots of input data from Userform - Help with arrays *novice*

**Zacemon** · 05-06-2020, 12:37 PM

Hi Guys,

I have a working database which gathers a lot of values (72 I think) from Userform and saves them into the next empty row of a worksheet. Unfortunately, as there are so many values when I execute the save function suing the command button, it takes well over 20 seconds to save the values to the worksheet. It's almost like it crashes. I have a very good PC with high spec processor/ram but it's extremely slow.
I've read a lot online about using arrays to speed up the saving process. I have past experience in programming with VB (18 years ago) and I managed to get this far but I have not got a clue where to start when it comes to arrays. Can anybody help?

Here is the code that I have for the save funtion:

Please Login or Register  to view this content.

Excuse me if my coding technique is poor

Many thanks

Zac

**mehmetcik** · 05-06-2020, 01:01 PM

Hi Zac.

You say that you are reading and writing 72 items of data to and from excel into a Userform.

I would read a line of data into VBA using

'Read Row 5 into VBA

Please Login or Register  to view this content.

I would then use the Array to load the Userform Controls

Please Login or Register  to view this content.

To write my Data Back to Row 5

I would read my Controls Back into An Array and then write the Array to My Row

Please Login or Register  to view this content.

This only works if you number your controls with the column number that the data is stored in.
However you can have several types of control loaded with the same data.

Eg Label1.Caption = TextBox1.Text = Listbox1.value = Spinbutton1.value = Cells(5,1).value

Save lots of input data from Userform - Help with arrays novice

LinkBack

Thread Tools

Rate This Thread

Display