PDA

View Full Version : Best use of memory



Rayman
09-09-2011, 08:54 PM
For best use of memory and speed of macro what is the best coding?

This:

Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Set rngSource = Worksheets("Sheet1").Range("A2:C5")
Set lbtarget = Me.ListBox1
With lbtarget
.List = rngSource.Cells.Value
Set rngSource = Nothing
Set lbtarget = Nothing
End With


Or this

Dim rngSource As Range
Set rngSource = Worksheets("Sheet1").Range("A2:C5")
With Me.ListBox1
.List = rngSource.Cells.Value
Set rngSource = Nothing
End With


Thanks to all for reply.

Kenneth Hobs
09-09-2011, 09:30 PM
ListBox1.List = Worksheets("Sheet1").Range("A2:C5")Value

justdriving
09-10-2011, 03:41 AM
Whenever we work on VBA, to release memory, it is very important to include before End Sub

Set rngSource = Nothing

Set <variable1> = Nothing

Set <variableN> = Nothing

Rayman
09-10-2011, 04:12 AM
ListBox1.List = Worksheets("Sheet1").Range("A2:C5")Value

Thanks Kennet, here my code in your way:

Set sht = Worksheets("Elenchi")
cmbPersonale.List = sht.Range("B2:B" & sht.Cells(Rows.Count, 2).End(xlUp).Row).Value
CmbMacchine.List = sht.Range("D2:D" & sht.Cells(Rows.Count, 4).End(xlUp).Row).Value
CmbMateriali.List = sht.Range("H2:I" & sht.Cells(Rows.Count, 8).End(xlUp).Row).Value
CmbProduzione.List = sht.Range("K2:K" & sht.Cells(Rows.Count, 11).End(xlUp).Row).Value

Set sht = Nothing

Bob Phillips
09-10-2011, 05:00 AM
Whenever we work on VBA, to release memory, it is very important to include before End Sub


Set rngSource = Nothing

Set <variable1> = Nothing

Set <variableN> = Nothing


Read post#9 from Matt Curland, who knows a thing or two about garbage collection in VB, in this thread http://bit.ly/Curland

Rayman
09-10-2011, 07:54 AM
Read post#9 from Matt Curland, who knows a thing or two about garbage collection in VB, in this thread http://bit.ly/Curland

Interesting article xld,

thanks for the link