CODEWITHSUNDEEP
vb.netexcel
Keilan
Keilan

Reputation: 331

Excel commands are not evaluated when setting the value of a range

I am trying to use a multidimensional array of strings to fill an excel sheet and have run into trouble when inserting formulas. The following small case illustrates the problem:

Dim tA(3, 3) As String
tA(1, 1) = "=HYPERLINK(""www.stackoverflow.com"")"
tA(1, 2) = "=HYPERLINK(""www.stackexchange.com"")"
tA(2, 1) = "=2+5"
tA(2, 2) = "Normal Text"
worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).Value = tA

When this code is evaluated, the excel sheet does not automatically convert the hyperlinks or evaluate the formulas. That is, when I look at Excel, the cell at (2,1) will display "=2+5" instead of "7" as desired. Is there a way to force excel to evaluate all cells to see if a command is present?

I should note that if I insert a command into an individual cell then it is evaluated properly, but that isn't an option as I have many thousands of cells and filling them one at a time is several orders of magnitude slower.

Upvotes: 1

Views: 368

Answers (2)

John Bustos
John Bustos

Reputation: 19574

... This is a bit of a hack, but I've used it successfully in the past...

Replace the "=" in your formulas with another character you won't use (I usually use "#", but you can use anything) and then use replace.

Your code would look as follows:

Dim tA(3, 3) As String
tA(1, 1) = "#HYPERLINK(""www.stackoverflow.com"")"
tA(1, 2) = "#HYPERLINK(""www.stackexchange.com"")"
tA(2, 1) = "#2+5"
tA(2, 2) = "Normal Text"
Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(4, 4)).Value= tA
Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(4, 4)).Replace "#", "="

Hope this helps

Upvotes: 1

nutsch
nutsch

Reputation: 5962

You get that because the formulas get transferred as values, add the following line to your code and you should be good to go:

worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).formular1c1= worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).formular1c1

full code would be

Dim tA(3, 3) As String
tA(1, 1) = "=HYPERLINK(""www.stackoverflow.com"")"
tA(1, 2) = "=HYPERLINK(""www.stackexchange.com"")"
tA(2, 1) = "=2+5"
tA(2, 2) = "Normal Text"
worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).Value = tA
worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).formular1c1= worksheet.Range(worksheet.Cells(1, 1), worksheet.Cells(4, 4)).formular1c1

Upvotes: 4

Related Questions