+ Reply to Thread
Results 1 to 2 of 2

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

Hybrid View

  1. 05-06-2020, 12:37 PM #1
    Zacemon
    Zacemon is offline
    Registered User
    Join Date
    04-30-2020
    Location
    Manchester, UK
    MS-Off Ver
    Professional Plus 2016
    Posts
    1

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

    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:

    Private Sub CB_1_Finished_Click()
Dim lrResults As Long

'_____________write to Output Sheet

lrResults = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Row


Sheets("Results").Cells(lrResults + 1, "A").value = tb_today.Text
Sheets("Results").Cells(lrResults + 1, "B").value = tb_rad_initials.Text
Sheets("Results").Cells(lrResults + 1, "C").value = tb_60_01.Text
Sheets("Results").Cells(lrResults + 1, "D").value = tb_60_02.Text
Sheets("Results").Cells(lrResults + 1, "E").value = tb_60_03.Text

'Calculate Average of above text boxes
Sheets("Results").Cells(lrResults + 1, "F").value = (Val(tb_60_01) + Val(tb_60_02) + Val(tb_60_03)) / 3

Sheets("Results").Cells(lrResults + 1, "G").value = tb_dose_00.Text
Sheets("Results").Cells(lrResults + 1, "H").value = tb_81_01.Text
Sheets("Results").Cells(lrResults + 1, "I").value = tb_81_02.Text
Sheets("Results").Cells(lrResults + 1, "J").value = tb_81_03.Text

'Calculate Average of above text boxes
Sheets("Results").Cells(lrResults + 1, "K").value = (Val(tb_81_01) + Val(tb_81_02) + Val(tb_81_03)) / 3

Sheets("Results").Cells(lrResults + 1, "L").value = tb_dose_01.Text
Sheets("Results").Cells(lrResults + 1, "M").value = tb_com_01.Text
Sheets("Results").Cells(lrResults + 1, "N").value = tb_phy_EXI_01.Text
Sheets("Results").Cells(lrResults + 1, "O").value = cb_uniform_01.Text
Sheets("Results").Cells(lrResults + 1, "P").value = tb_ROI_01.Text
Sheets("Results").Cells(lrResults + 1, "Q").value = tb_com_02.Text
Sheets("Results").Cells(lrResults + 1, "R").value = cb_ring_04.Text
Sheets("Results").Cells(lrResults + 1, "S").value = tb_top.Text
Sheets("Results").Cells(lrResults + 1, "T").value = tb_right.Text
Sheets("Results").Cells(lrResults + 1, "U").value = tb_bottom.Text
Sheets("Results").Cells(lrResults + 1, "V").value = tb_left.Text
Sheets("Results").Cells(lrResults + 1, "W").value = tb_com_03.Text
Sheets("Results").Cells(lrResults + 1, "X").value = tb_LBD_to_IR_01.Text
Sheets("Results").Cells(lrResults + 1, "Y").value = tb_com_04.Text
Sheets("Results").Cells(lrResults + 1, "Z").value = tb_phy_EXI_02.Text
Sheets("Results").Cells(lrResults + 1, "AA").value = tb_cli_EXI_01.Text
Sheets("Results").Cells(lrResults + 1, "AB").value = tb_dose_02.Text
Sheets("Results").Cells(lrResults + 1, "AC").value = cb_lines_vis_01.Text
Sheets("Results").Cells(lrResults + 1, "AD").value = tb_com_05.Text
Sheets("Results").Cells(lrResults + 1, "AE").value = tb_phy_EXI_03.Text
Sheets("Results").Cells(lrResults + 1, "AF").value = tb_cli_EXI_02.Text
Sheets("Results").Cells(lrResults + 1, "AG").value = tb_dose_03.Text
Sheets("Results").Cells(lrResults + 1, "AH").value = cb_disc_vis_01.Text
Sheets("Results").Cells(lrResults + 1, "AI").value = tb_com_06.Text
Sheets("Results").Cells(lrResults + 1, "AJ").value = tb_left_mAs_01.Text
Sheets("Results").Cells(lrResults + 1, "AK").value = tb_left_EXI_01.Text
Sheets("Results").Cells(lrResults + 1, "AL").value = tb_left_dose_01.Text
Sheets("Results").Cells(lrResults + 1, "AM").value = tb_mid_mAs_01.Text
Sheets("Results").Cells(lrResults + 1, "AN").value = tb_mid_EXI_01.Text
Sheets("Results").Cells(lrResults + 1, "AO").value = tb_mid_dose_01.Text
Sheets("Results").Cells(lrResults + 1, "AP").value = tb_right_mAs_01.Text
Sheets("Results").Cells(lrResults + 1, "AQ").value = tb_right_EXI_01.Text
Sheets("Results").Cells(lrResults + 1, "AR").value = tb_right_dose_01.Text
Sheets("Results").Cells(lrResults + 1, "AS").value = tb_com_07.Text
Sheets("Results").Cells(lrResults + 1, "AT").value = tb_phy_EXI_04.Text
Sheets("Results").Cells(lrResults + 1, "AU").value = cb_uniform_02.Text
Sheets("Results").Cells(lrResults + 1, "AV").value = tb_ROI_02.Text
Sheets("Results").Cells(lrResults + 1, "AW").value = tb_com_08.Text
Sheets("Results").Cells(lrResults + 1, "AX").value = tb_LBD_to_IR_02.Text
Sheets("Results").Cells(lrResults + 1, "AY").value = tb_com_09.Text
Sheets("Results").Cells(lrResults + 1, "AZ").value = tb_phy_EXI_05.Text
Sheets("Results").Cells(lrResults + 1, "BA").value = tb_cli_EXI_03.Text
Sheets("Results").Cells(lrResults + 1, "BB").value = tb_dose_04.Text
Sheets("Results").Cells(lrResults + 1, "BC").value = cb_lines_vis_02.Text
Sheets("Results").Cells(lrResults + 1, "BD").value = tb_com_10.Text
Sheets("Results").Cells(lrResults + 1, "BE").value = tb_phy_EXI_06.Text
Sheets("Results").Cells(lrResults + 1, "BF").value = tb_cli_EXI_04.Text
Sheets("Results").Cells(lrResults + 1, "BG").value = tb_dose_05.Text
Sheets("Results").Cells(lrResults + 1, "BH").value = cb_disc_vis_02.Text
Sheets("Results").Cells(lrResults + 1, "BI").value = tb_com_11.Text
Sheets("Results").Cells(lrResults + 1, "BJ").value = tb_left_mAs_02.Text
Sheets("Results").Cells(lrResults + 1, "BK").value = tb_left_EXI_02.Text
Sheets("Results").Cells(lrResults + 1, "BL").value = tb_left_dose_02.Text
Sheets("Results").Cells(lrResults + 1, "BM").value = tb_mid_mAs_02.Text
Sheets("Results").Cells(lrResults + 1, "BN").value = tb_mid_EXI_02.Text
Sheets("Results").Cells(lrResults + 1, "BO").value = tb_mid_dose_02.Text
Sheets("Results").Cells(lrResults + 1, "BP").value = tb_right_mAs_02.Text
Sheets("Results").Cells(lrResults + 1, "BQ").value = tb_right_EXI_02.Text
Sheets("Results").Cells(lrResults + 1, "BR").value = tb_right_dose_02.Text
Sheets("Results").Cells(lrResults + 1, "BS").value = tb_com_12.Text

' add short form date for graph axis
Sheets("Results").Cells(lrResults + 1, "BT").Value = Format(Date, "mmmm yyyy")

Unload Me

End Sub
    Excuse me if my coding technique is poor

    Many thanks

    Zac
    Reply With QuoteRegister To Reply
  2. 05-06-2020, 01:01 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

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

    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
    Entry = 5
A = Application.Transpose(Application.Transpose(Range(Cells(Entry,1),Cells(Entry,72)).Value))
'This creates an Array A(1) to A(72) which is easy to work with

    I would then use the Array to load the Userform Controls

    On Error Resume Next
For Count = 1 to 72
Me.Controls("Textbox" & Count).Value = A(Count)
Me.Controls("Label" & Count).Caption = A(Count)
Me.Controls("Combobox" & Count).Value = A(Count)
Me.Controls("Listbox" & Count).Value = A(Count)
Me.Controls("Checkbox" & Count).Value = A(Count)
Me.Controls("OptionButton" & Count).Value = A(Count)
Me.Controls("SpinButton" & Count).Value = A(Count)
Next
On Error Goto 0

    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

    A = Application.Transpose(Application.Transpose(Range(Cells(Entry,1),Cells(Entry,72)).Value))

On Error Resume Next
For Count = 1 to 72
A(Count) = Me.Controls("Textbox" & Count).Value
A(Count) = Me.Controls("Label" & Count).Caption
A(Count) = Me.Controls("Combobox" & Count).Value
A(Count) = Me.Controls("Listbox" & Count).Value
A(Count) = Me.Controls("Checkbox" & Count).Value
A(Count) = Me.Controls("OptionButton" & Count).Value
A(Count) = Me.Controls("SpinButton" & Count).Value
Next
On Error Goto 0

Entry = 5
Range(Cells(Entry,1),Cells(Entry,72)).Value = A

    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
    Last edited by mehmetcik; 05-06-2020 at 01:24 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. BIG sheet with lots and lots of data to filter...
    By Amatør in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-25-2016, 08:26 AM
  2. Excel Userform novice!!
    By Maggie6360 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-20-2015, 01:53 PM
  3. [SOLVED] Save data from UserForm into Sheet (how to resolve runtime errors in UserForm tutorial)
    By eighty6 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-14-2015, 07:41 PM
  4. How to browse the path for the input files and save the output using a userform
    By priyankaseshadri in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2014, 03:14 PM
  5. Dynamic updating of current userform values based on userform data input or commandbutton
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2012, 06:09 PM
  6. [SOLVED] userform to gather input file and output folder, process reports and save
    By rufuswilson in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2012, 09:26 AM
  7. [SOLVED] VBA novice: new to userform & VB editing...
    By Dexxterr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2006, 09:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1