VBA create an array which contains formulas

Question

I would like to know if there a way to create an array which contains only formulas (not value).

for e.g.

dim myArr (1) as string
myArr(0) = "=Sum(A1:C3)"
myArr(1) = "=Sum(B1:D3)" 
Range("E1:E2") = myArr

The result is cells have above formulas as a string. is there any straightforward method of storing formulas and applying to ranges?.

thanks

GSerg · Accepted Answer · 2017-09-22 07:11:23Z

2

You need to declare the array as 2D (because 1D array is a single row for Excel, and you have multiple rows), and you need to declare it as Variant.

Dim myArr(1 To 2, 1 To 1) As Variant
myArr(1, 1) = "=Sum(A1:C3)"
myArr(2, 1) = "=Sum(B1:D3)"
Range("E1:E2").Formula = myArr

answered Sep 22, 2017 at 7:11

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

Collectives™ on Stack Overflow

VBA create an array which contains formulas

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