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.

Welcome to the Board!

Can you post your code?

Smitty

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
Else
saRet(iRow, iCol) = "=SUM(" & Chr(65 + iCol) & iRow & ":" & Chr(65 + iCol) & iRow & ")"
End If

Next iCol
Next iRow

'Set the range value to the array.
range.Formula = saRet
objBook.SaveAs "c:\test.xls"
'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
End Sub

Just incase anyone wants to know i figured out my problem. It waws that i simply had to declare the array as a varient instead of a string