Assign default value to array

Hi gagsd,
it is not what I had in mind.
This solution means looping and that I wanted to avoid.
I was looking for a more simple way.
Similar to erasing the array...

Kr  Eric

I can see the error in my question, should have been without looping thru.

so you want to fill the array without looping?

Hello Eric,

The only thing I can think of is to assign that value to a range, and then do an array transfer.  For 100,000
elements, obviously, this works only for Excel 2007.

Not sure you're saving any time, though...

Regards,

Patrick

Dim ObjAss As Variant
 
Worksheets.Add
 
'every cell in range will equal 3240
Range("a1:a100001") = 3240 
 
'array transfer
ObjAss = Range("a1:a100001").Value
 
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Hi Patrick,

this might do the job.
If I do not get anything better I will accept this...

Here I go again :0-)

This should save you time as well. I am buidling on mathewpatrick's solution.

Dim ObjAss As Variant
 
Worksheets.Add
 
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks
 
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
 
Range("A1:A100001") = 3240 
 
ObjAss = Range("a1:a100001").Value
 
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState
 
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

That will give a 2D array , ie (1 to 100001, 1 to 1)

You will need to loop.  < this is where someone else pops up with a comment making me sound silly>

Dave,

>> That will give a 2D array , ie (1 to 100001, 1 to 1)

Yeah, I know.  I was cheating slightly :)

Patrick

How about (2007 only of course):

Dim var
var = Application.Transpose(Application.Evaluate("if(row(A1:A100000),3264,0)"))
                                              

1:
2:

rorya said:
>>Dim var
>>var = Application.Transpose(Application.Evaluate("if(row(A1:A100000),3264,0)"))

<dumbfounded>

Rory,

That was one of the more brilliant tricks I've seen in a long time!

Patrick

Unfortunately, I only tested in 2003 and then expanded the range when posting. Seems to fail with a type mismatch for me in 2007 using 100000 rows. It's OK with 65536 though.

Hi How would I use this in combination with the ObjAss(100000) object?

You can't - see my follow-up post above. To be honest, you might as well either loop or when using the array, assume your default value if the array value is empty.

A thing of beauty regardless Rory  .....

Thanks, Dave! It's bugging me now though... :)

Patrick,

Nice! It comes out as Strings though - don't know if that's an issue here?

Rory

Rory,

Guess we'll find out :)

Patrick

:)

Hi Rory, Patrick , brett, Dave and others working on this

Strings schould not be a problem, data will be written in a flatfile afterwarts.

KR

Eric

Then Patrick's solution should work. :)

Hi Rory

In Patrick's solutuion I miss the array?
should have been

dim ObjAss(100000) as variant
when I use this system says: can't assign to array

KR

Eric

Ok sorry about previous message...

Array comes out nice super

Eric,

I'm glad that worked out.  I was curious about performance, so I wrote the sub below to test my approach
vs simply looping through the array to populate it.

As I suspected, my approach was much, *MUCH* slower.  The looping approach is consistently coming
in at under 1 second to do 100 of these, and the Split approach at 26 seconds to do 100 of them.

And cheers to Rory for a showing us a *very* original approach to doing this!

Patrick

Sub Test()
    
    Dim TestCounter As Long
    Dim StartAt As Date
    Dim EndAt As Date
    Dim Counter As Long
    Dim UsingArray(100001) As Variant
    Dim UsingVariant As Variant
    Dim TestStr As String
    
    Const NumTests As Long = 100
    
    StartAt = Now
    
    For TestCounter = 1 To NumTests
        For Counter = 1 To 100001
            UsingArray(Counter) = 3240
        Next
    Next
    
    EndAt = Now
    
    Debug.Print "Using loop: " & DateDiff("s", StartAt, EndAt) & " seconds"
    
    StartAt = Now
    
    For TestCounter = 1 To NumTests
        TestStr = String(100001, "x")
        TestStr = Mid(Replace(TestStr, "x", ":3240"), 2)
        UsingVariant = Split(TestStr, ":")
    Next
    
    EndAt = Now
    
    Debug.Print "Using Split: " & DateDiff("s", StartAt, EndAt) & " seconds"
    
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37: