Textboxs to Array to an array

[Chuck6475]

My application has performance issues.

One of the UserForms is a series of textboxes into which the user enters information. The data is screened and then transferred to a large array of similar data. The user's ID is the row of the large array.

The inputted (via textboxes) data has two logical groupings and are actually stored in the large array in two different sections.

Q. I'm currently moving the data to the large array via VBA loops. It seems slow. How can I do it faster?

To turn the textboxes into an array themselves I thought I'd use the array function in the hopes it would be quicker than looping through Controls.

Logically I would think I can convert the textboxes to an array, identify the proper start location for a "paste" using Offset(User ID) and Copy the data over. I'm having trouble converting my logic to reality.

 

[Norie]

You can't convert the textboxes to arrays.

All you can do is put their values in an array(s) and for that you would need to loop.

 

[Chuck6475]

Hi,
Current code below seems very slow.

ssidx = Sheets("rawscoring").Range("Select_PlyrIndex").Value + 1
     FrameProgress.Visible = True

 i = 0
 j = 0
 
 Application.ScreenUpdating = False
 
 ' Controls stores the backnine textboxes backwards so the scores are entered 18 to 10
 
 
 ' Back nine scores
            For Each ctl In Self_Scoring.Controls
    
                    If Left(ctl.Name, 4) = "Hole" Then
                        i = i + 1
                        DataRR.Cells(ssidx, 26 - i).Value = ctl.Value
                        End If
                         Next ctl
                         
                         PctDone = 0.33
                         Call UpdateProgress(PctDone)
                         
 ' Front nine scores
                         
  i = 0
            For Each ctl In Self_Scoring.Controls
    
                    If Left(ctl.Name, 4) = "FHol" Then
                        i = i + 1
                        DataRR.Cells(ssidx, i + 6).Value = ctl.Value
                        End If
                        
                         Next ctl
 i = 0
 
                          PctDone = 0.5
                         Call UpdateProgress(PctDone)

Because I had a fixed number of textboxes I thought I could use the Array function as follows hoping it would be quicker than looping through control for "textbox":

Dim grouptext as variant
grouptext = array(textbox1,textbox2,textbox3, ............)

and then use Offset property to "paste" the data into the large array.

Bad idea?

 

[Norie]

You could use an array like that but I don't think it would speed things up.

Where would you be using Offset?

I think the problem might be that you are looping through every control.

 

[Teeroy]

You could try setting Application.Calculation = xlCalculationManual at the beginning of your code and returning it to Application.Calculation = xlCalculationAutomatic at the end. This would stop the sheet recalculating every time you paste in a value.
Or you build the entire array as you suggested and assign the array values directly to a range on the sheet.

 

[Chuck6475]

Teeroy, thanks for the suggestion. I'm currently using

 For Each wsht In MyArray
          With wsht
              .EnableCalculation = False
        End With
     Next wsht

Where MyArray has all of the worksheet that are not needed during this phase of data gathering. I turn them ON/OFF as needed. I had used Application.calculation = xlcalculationmanual previously for another issue, probably incorrectly and didn't like the results so I steered away from it.


Norie,

Relative to the OFFSET and/or copy - paste.

I'm personally still trying to get my hands around Ranges and set them etc. but the plan was to set the range for the destination of a copy / paste of the Function Array to the large array. If that didn't work as planned then to try to use Offset to set the destination of the copy/paste of the Function Array to the large array. The hope being this eliminates the loops through the controls and iterative loop to get the data into the large array.

As you can tell, it doesn't work yet.

 

[Teeroy]

What is the full name of one of your controls? Does it include the hole number? If so you could use that as the array index to build an output array.

 

[Chuck6475]

Using the Function Array to store all of the textboxes, given there are a limited number, seems to work fine. I did disable calculations around the code to insert the data from the Function Array to the large central array. This appeared to help more than I expected it would, so thanks for suggesting it.