Populate array

  • Thread starter Thread starter Jake Marx
  • Start date Start date
J

Jake Marx

Hi Beto,

You could do something like this:

Sub test()
Dim Names As Variant

Names = Array("N1", "N2", "N3")

Sheets("Sheet1").Range("A1:C1").Value = Names
End Sub

BTW, you should probably use a different variable name than Names, as Names
is the name of a collection of Name objects. In this case, you may want to
use vNames or avNames (v for Variant or av for "array of Variants").

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Hi,
Can I populate an array in a similar way than this?

Dim Names(1 to 3) as String
Names=Array("N1","N2","N3")

I like something like that instead of:
Names(1)="N1": Names(2)="N2": Names(3)="N3"

BTW, which one would bethe best way to fill column headings that aren't
in any Workbook, just from VBA?

Regards,
 
Jake said:
Hi Beto,

You could do something like this:

Sub test()
Dim Names As Variant

Names = Array("N1", "N2", "N3")

Sheets("Sheet1").Range("A1:C1").Value = Names
End Sub
Click to expand...

Worked beatifully... Is there a way to assign comlumn widths in a
similar way?

As you may find out I'm formatting a sheet for a report. I thought of
using a template sheet, but I'm doing this because is harder and helps
me to learn more VBA.

Regards,
 
Beto,

Is this what you want

Option Base 1

Sub ColumnWidths()

Dim aryWidths
Dim i As Long

aryWidths = Array(10, 15, 22, 34)

For i = LBound(aryWidths) To UBound(aryWidths)
Columns(i).ColumnWidth = aryWidths(i)
Next i

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Beto,

That works, but I added the bits about lower and upper array bounds as you
said you wanted to learn more VBA <G>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Beto said:
Worked beatifully... Is there a way to assign comlumn widths in a
similar way?
Click to expand...

Nevermind, after a lot of trial & error I came up with this:

Dim vColumnWidths as Variant

vColumnWidths = Array(10, 20, 30)
Range("A1:C1").ColumnWidth = vColumnWidths

Regards,
 
Bob said:
Beto,

That works, but I added the bits about lower and upper array bounds as you
said you wanted to learn more VBA <G>.
Click to expand...

Thanks, Is always better to know more ways to do the same things.

Regards,
 
Back
Top