VBA how to pass a string array to range

Question

The usual issue is to pass range to array, but this time I need to do the opposite. I want to pass various size string array to a range. BR Michał

What is the problem? To determine the size of the range? Well you have the bounds (LBound and UBound)) of the array. — Axel Richter
– Axel Richter, Commented Sep 16, 2016 at 9:26
Do I have to set a range? Can I assing array to range after setting? Like rng = array? — Eswemenasja
– Eswemenasja, Commented Sep 16, 2016 at 9:42

Axel Richter · Accepted Answer · 2016-09-16 09:52:56Z

1

To set the value of a range from an array, we must first determine the size of that range dependent of the arrays size. For this we can use the bounds (LBound and UBound) of the array.

Then, if the array fits into the range, we can simply do range.value = arr.

Example:

Sub test()

 Cells.ClearContents

 ' one dimensional array; base 0
 aSArr = Array("This", "is", "an", "array", "of", "strings")

 Set r1 = Range("C3").Resize(1, 1 + UBound(aSArr))
 r1.Value = aSArr

 Set r2 = Range("C5").Resize(1 + UBound(aSArr), 1)
 r2.Value = Application.Transpose(aSArr)

 ' two dimensional array; base 1
 aSArr = [{"This", "is", "an"; "array", "of", "strings"}]

 Set r3 = Range("E6").Resize(UBound(aSArr, 1), UBound(aSArr, 2))
 r3.Value = aSArr


End Sub

answered Sep 16, 2016 at 9:52

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA how to pass a string array to range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related