I have been trying to get an array full of formulas to insert into a range on excel spreadsheat; however the formulas end up showing as text and not evaluating. I have tried sever different commands and none seem to work. I currently am outputing one cell's formula at a time but this is painfully slow and i was hoping to improve speed by storing them in an array and puting out the whole array of formulas at one time. Is this possible? and if it is how can it be done. range.formula = array does not seem to work.
I am actually doing this is access, and I created a test procedure to try and get it to work here is the code of my test procedure. It is simply code for a form that has one button on it. could be cut and paste into VB if you wish.
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Private Sub Button1_Click()
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel.Worksheet
Dim range As Excel.range
'Create an array.
Dim saRet(5, 5) As String
' Create a new instance of Excel and start a new workbook.
Set objApp = New Excel.Application
Set objBooks = objApp.Workbooks
Set objBook = objBooks.Add
Set objSheets = objBook.Worksheets
Set objSheet = objSheets(1)
Set range = objSheet.range("A1", "E5")
Set range = range.Resize(5, 5)
For iRow = 0 To 5
For iCol = 0 To 5
If iRow = 0 Then
saRet(iRow, iCol) = iCol
saRet(iRow, iCol) = "=SUM(" & Chr(65 + iCol) & iRow & ":" & Chr(65 + iCol) & iRow & ")"
'Set the range value to the array.
range.Formula = saRet
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserControl = True
'Clean up a little.
Set range = Nothing
Set objSheet = Nothing
Set objSheets = Nothing
Set objBooks = Nothing