Save Array in memory

[Jorgen Bondesen]

Hi NG

I'm using bellowed macro.



Each time I'm opening my User Form = UF, I must rum this macro for getting
array: data()



Is there a way I can load the Array [data()] in memory, so I only run the
macro when opening the file and Erase data() when closing the file?






Option Explicit

Public Const Delim As String = " ¿ "
Public Const hil As String = "Best regards from Joergen"

'// Array for form
Public data() As Variant

'----------------------------------------------------------
' Procedure : Form
' Date : 20110227
' Author : Joergen Bondesen
' Modifyed by :
' Purpose :
' Note :
'----------------------------------------------------------
'
Sub Form()
Dim sh As Worksheet
Set sh = Sheets(2)

Dim RRange As Range
Set RRange = sh.Range("A1:A" & _
sh.Cells(sh.Rows.Count, 1).End(xlUp).Row)

Dim Countarr As Long
Countarr = 0

Dim cell As Range
For Each cell In RRange
Dim Uniqs As New Collection
On Error Resume Next
Uniqs.Add cell.Value, CStr(cell.Value)
If Err = 0 Then
Countarr = Countarr + 1
ReDim Preserve data(1 To Countarr)
data(Countarr) = cell.Value & Delim _
& cell.Offset(0, 1) & Delim & cell.Offset(0, 2)
End If
On Error GoTo 0
Next cell

Load UF
UF.Show

Set sh = Nothing
Set RRange = Nothing
End Sub

 

[Jim Cone]

You are loading it into memory: the data() array is declared as Public outside of any sub
or function.
So all you have to do is check the bounds of the array to see if has been dimensioned
(filled).
The following will work for your particular circumstance but is not a universal
solution...
'---
Option Explicit
Public Const Delim As String = " ¿ "
Public Const hil As String = "Best regards from Joergen"
Public data() As Variant

Sub Form()
Dim Uniqs As Collection
Dim Countarr As Long
Dim sh As Worksheet
Dim RRange As Range
Dim cell As Range

'Verify array
On Error Resume Next
Countarr = UBound(data(), 1)
On Error GoTo 0

If Countarr < 1 Then ' if zero then fill array
Set sh = Sheets(2)
Set RRange = sh.Range("A1:A" & _
sh.Cells(sh.Rows.Count, 1).End(xlUp).Row)
Set Uniqs = New Collection
For Each cell In RRange
On Error Resume Next
Uniqs.Add cell.Value, CStr(cell.Value)
If Err = 0 Then
Countarr = Countarr + 1
ReDim Preserve data(1 To Countarr)
data(Countarr) = cell.Value & Delim _
& cell.Offset(0, 1) & Delim & cell.Offset(0, 2)
End If
On Error GoTo 0
Next cell
End If

Set cell = Nothing
Set RRange = Nothing
Set sh = Nothing
Set Uniqs = Nothing
UF.Show
End Sub
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
("Lottery Numbers" workbook - in the free folder)




"Jorgen Bondesen" <[email protected]>
wrote in message

Hi NG

I'm using macro below.
Each time I'm opening my User Form = UF, I must run this macro for getting array:
data()
Is there a way I can load the Array [data()] in memory, so I only run the macro when
opening the file and Erase data() when closing the file?

 

[Jorgen Bondesen]

Hi Jim

It works, thanks for your help.

--
Best Regards
Jorgen Bondesen
Denmark, Copenhagen

You are loading it into memory: the data() array is declared as Public
outside of any sub or function.
So all you have to do is check the bounds of the array to see if has been
dimensioned (filled).
The following will work for your particular circumstance but is not a
universal solution...
'---
Option Explicit
Public Const Delim As String = " ¿ "
Public Const hil As String = "Best regards from Joergen"
Public data() As Variant

Sub Form()
Dim Uniqs As Collection
Dim Countarr As Long
Dim sh As Worksheet
Dim RRange As Range
Dim cell As Range

'Verify array
On Error Resume Next
Countarr = UBound(data(), 1)
On Error GoTo 0

If Countarr < 1 Then ' if zero then fill array
Set sh = Sheets(2)
Set RRange = sh.Range("A1:A" & _
sh.Cells(sh.Rows.Count, 1).End(xlUp).Row)
Set Uniqs = New Collection
For Each cell In RRange
On Error Resume Next
Uniqs.Add cell.Value, CStr(cell.Value)
If Err = 0 Then
Countarr = Countarr + 1
ReDim Preserve data(1 To Countarr)
data(Countarr) = cell.Value & Delim _
& cell.Offset(0, 1) & Delim & cell.Offset(0, 2)
End If
On Error GoTo 0
Next cell
End If

Set cell = Nothing
Set RRange = Nothing
Set sh = Nothing
Set Uniqs = Nothing
UF.Show
End Sub
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
("Lottery Numbers" workbook - in the free folder)




"Jorgen Bondesen" <[email protected]>
wrote in message

Hi NG

I'm using macro below.
Each time I'm opening my User Form = UF, I must run this macro for
getting array: data()
Is there a way I can load the Array [data()] in memory, so I only run the
macro when opening the file and Erase data() when closing the file?

 

[GS]

As Jim states, the contents of your array are store in a public var and
so these remain until you 'Erase' the array OR set it to '=Empty'.

It appears that the use of a collection is to ensure your array
contains unique values. If you have no other use for the collection
then maybe this shorter version might work for you since it requires no
cleanup.<g>

Sub Form()
Dim n As Long, sTemp As Variant

On Error Resume Next
n = UBound(vaData)
If Not (Err = 0) Then
Err.Clear
'Fill the array
vaData = Sheets(2).Range("A1:A" _
& Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row).Resize(,
3)
'Filter for unique entries in ColA
For n = LBound(vaData) To UBound(vaData)
If Not InStr(1, sTemp, vaData(n, 1)) > 0 Then
sTemp = sTemp & vaData(n, 1) & Delim _
& vaData(n, 2) & Delim _
& vaData(n, 3) & Delim & vbCrLf
End If
Next

'Reload the array with the unique entries
vaData = Empty
vaData = Split(sTemp, vbCrLf)
End If '//Not (Err = 0)

'Display the userform
UF.Show
End Sub

Here's the results from a 10Row x 3Col range where 1 item in ColA
appears 3x in the data list...

DataA1 ¿ DataB1 ¿ DataC1 ¿
DataA2 ¿ DataB2 ¿ DataC2 ¿
DataA3 ¿ DataB3 ¿ DataC3 ¿
DataA5 ¿ DataB5 ¿ DataC5 ¿
DataA6 ¿ DataB6 ¿ DataC6 ¿
DataA8 ¿ DataB8 ¿ DataC8 ¿
DataA9 ¿ DataB9 ¿ DataC9 ¿
DataA10 ¿ DataB10 ¿ DataC10 ¿

The above was written back to the wks as follows:

Range("E1").Resize(UBound(vaData)) = _
Application.WorksheetFunction.Transpose(vaData)

 

[GS]

Forgot to copy the declares...

Option Explicit

Public Const Delim As String = " ¿ "
Public Const hil As String = "Best regards from Joergen"
Public vaData As Variant

**Note that vaData is NOT declared as an array. This allows Excel to
automatically size it and so stepping through each row and using ReDim
is not necessary.

 

[Jorgen Bondesen]

Hi Garry.

Thanks.
I will have a closer look next weekend.