Fastest way to temporarily store results for further analysis

[monkeymuffin]

Hi, I'm using a macro to loop through a data set and achieve about 100 or so results of 5 numbers each. I'm inserting a space on a new sheet, copying the results to the page and
returning to the loop. Once the loop is finished I take the list of variable amounts of results and copy it to an analysis page where it quickly imposes conditions on each result. The analysis is quick
but retrieving the data is slow. I'm fairly new to VBA so I assume there's a much better and faster way to do this. The data is temporary, I do not need to reference it later.

Can I store each result as a variable or something and process it seperately without slowing down the program by all the inserting and copying to different sheets? What's the standard way of handling temporary results?

thanks for any help...

[Kenneth Hobs]

Depends on what you need. Are you saving formats or just values? If the latter, arrays are a good way to go.

Sub ken()
  Dim a(1 To 10) As Variant, i As Integer
  For i = 1 To 10
    If i Mod 2 = 1 Then
      a(i) = i
      Else
        a(i) = Chr(i + 96) ' chr(97)="a"
    End If
  Next i
  MsgBox Join(a(), vbLf)
  Range("A1").Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
End Sub

[monkeymuffin]

[QUOTE=Kenneth Hobs;297815]Depends on what you need. Are you saving formats or just values? If the latter, arrays are a good way to go.

Thanks, I'll try this out and try and determine what's going on in that code. I'm saving just values so I'll look into arrays as well. The problem I see coming though is I get a different number of results on each pass of this part of the program. Sometimes it's a list of 100, 5 number sets and sometimes it's around 80ish. It's new ground for me as I've always stored results on worksheets but I need speed now, so I really want the results as variables, I guess.

[Kenneth Hobs]

Variable results are fine as-long-as there is logic to it. e.g. Range("A2:E200") where 199 rows of data are poked into an array based on the 200 coming from the last cell in column A that has data.

A short example file tends to be the best way to get help.

[SamT]

Ken, Straighten me out, would you?

I have the impression that the first dimension of an array corresponds to Rows, but if I understand your example correctly, it really corresponds to Columns. :

[Kenneth Hobs]

That would be right. It matters little whichever way you think of it. For this case, a one dimensional array is really a vector. The transpose function is used to make it into a one column vector if you want to think of it in that way. I know some people are adamant about rows vs columns in arrays but I am not. As-long-as it is understood, you are good to go.

For this case, I could have done a two dimensional array and not used transpose.

In a 1 to 50 by 1 to 6 array that I did for myself earlier today, I think of the 1 to 6 as columns and 1 to 50 as rows. The reason being that the ReDim Preserve only allows the 2nd dimension to be be changed.

I hope that helps.

[SamT]

What is really stupid of me is that I correctly use a lot of 1d arrays to move data back and forth between Data Input forms and tables. I guess I just freaked when I saw "Transpose."

BTW, I saw an example of useing a 2d array, but only filling the first "Column," then restricting the Paste operation to a single column as a way to get around Transpose's ~6K Limit.

[Kenneth Hobs]

You are smarter than me. I am just a shade tree programmer. I did not know about the transpose limit. I have had occasion to use transpose twice on an array. There is a way to use Index that is better than 2 transposes.

Arrays in PerfectScript, WordPerfect's macro scripting language, is more powerful than any language that I know of. Sad that it stopped development so many years ago. Even so, arrays in it are limited to 64 kb now. They used to be limited to 32 kb. These sorts of limits can be based on the computer's bit system.

Here is the way without transpose.

Sub ken()
    Dim a(1 To 10, 1 To 1) As Variant, i As Integer
    For i = 1 To 10
      a(i, 1) = Chr(i + 96) ' chr(97)="a"
    Next i
    Range("A1").Resize(UBound(a)).Value = a()
End Sub

[SamT]

@ Ken

Neat. A 1d array with 2 vectors.

You are smarter than me. I am just a shade tree programmer

[SamT]

@ MM

Sub Test()
'Assumes that there is no data to protect in Range("A1:E200")
Const MaxNumRecords As Long = 200 'Prevents Redim Preserve
Const NumFields As Long = 5
Dim DataArray(1 To MaxNumRecord, 1 To NumFields) As Variant
Dim i As Long, F As Long

'For F = 1 To NumFields
'Get first Data record
'DataArray(i, F) = one piece of data
'
'Next F
'i = i + 1
'Next piece of data

Range("A1").Resize(MaxNumRecords, NumFields) = DataArray


End Sub

[monkeymuffin]

Thanks for the sample code. I have been able to incorporate my first array into my program and it works! It shaves a bit of time off the processing but not as much as I'd hoped.

One thing I was wondering, I'll be reusing this array hundreds of times in my program as I array results, analyze them and then array new results, analyze them and repeat. Should I ReDIM the array each time to clear it or how does assigning new values to the slots in the array work with memory and such?

oh, and I guess, since I resize it after I get the results, what happens if I get more results on the second pass that require a bigger array and try and plug them in to the array that's been resized to the previous less results?

[Kenneth Hobs]

The procedures in this or the methods in it can help sometimes too. http://vbaexpress.com/kb/getarticle.php?kb_id=1035

You can put the array into a Public variable and it will last for that session. You can Erase an array from memory if needed. Redim or Redim Preserve can be handy too.

Writing and Reading data from an external file in the background can be handy too.

[Paul_Hossler]

Not sure that the 6K TRANSPOSE() limit is still in later versions


http://social.msdn.microsoft.com/For...ions-on-arrays

Earlier versions of Excel had some limits on on the number of cells you could assign from a VBA array (passing the results from the VBA array to the worksheet). How have these changed in later versions of Excel?

I know that there seems to be a limit in VBA when using worksheetfunction.transpose of 2^16 elements in the array. This is an increase from the previous 5,000 or so

Paul

[SamT]

First declare it in the Module's Declarations are above all Subs, then in any sub that needs to use it.

In a sub, to initialize for first use or to clear for next use

ReDim DataArray(1 To MaxNumRecord, 1 To NumFields)

Why resize it? You said you get 80 to 100 value sets so I set the Constant MaxNumRecords to 200. Make it twice the size of the most value sets you expect to ever find.

You can have different Constant values in different Subs, all with the same names.

[snb]

@ Ken

Neat. A 1d array with 2 vectors.

Nothing 1D about:

Sub M_snb()
 Dim sn(1 To 10, 1 To 1) As Variant
 msgbox "first dimension:" & ubound(sn,1) & vblf & "second dimension: " & ubound(sn,2)
End Sub

@Paul

in 2010

Sub M_snb()
    Stop

    sn = Split(Replace(String(65535, "|"), "|", "aa|bb"), "|")
    st = Application.Transpose(sn)

    sn = Split(Replace(String(65536, "|"), "|", "aa|bb"), "|")
    st = Application.Transpose(sn)
Ebnd Sub

[Kenneth Hobs]

I am not sure what your are saying snb. I guess you are just confirming that the transpose limit is 2^16=65536 as Paul said.

[snb]

@KH

I wasn't sure what Paul was saying, nor from where his quote derived.
In Excel 2010 the code I posted illustrates/confirms the 2^16 boundary. (don't know the limits in 2007 or 2013).

[Paul_Hossler]

@snb and @KH


The link that I had in my #13 with the quote points to the MSDN source which has nice description and the history of the TRANSPOSE() limits, as well as several functions to demonstrate the limits

http://social.msdn.microsoft.com/For...ions-on-arrays


I thought that providing the link and the pertinent text as a quote would be a compromise between 1) just a piece of code or comment that was too brief with no explanation, or 2) copying / pasting a long thread from the MSDN forums.

Any one who might be interested could visit the link to see the rest of the discussions and the sample programs that people had written to demonstrate the 2^16 limit

As an aside, according to the link, there seems to be a limit difference between UDFs and Subs

Paul

[Aflatoon]

For a larger array you could use something like this:
[vba]Function TransposeIt(vData)
Dim lBound2 As Long
If TypeName(vData) = "Range" Then vData = vData.Value
If IsArray(vData) Then
' test for 1D array
On Error Resume Next
lBound2 = UBound(vData, 2)
On Error GoTo 0
' create MSForms.ListBox
With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
.Column = vData
If lBound2 = 0 Then
' for 2D, returning the Column will transpose and return 2D array
TransposeIt = .Column
Else
' for 2D array just return the List
TransposeIt = .List
End If
End With
End If
End Function

[/vba]

[SamT]

?!?!?!?!?

("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")