Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Unanswered: Insert formulas from VB array..

    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.
    Last edited by REGINALDM; 04-05-04 at 09:03.

  2. #2
    Join Date
    Dec 2003
    San Diego, CA
    Welcome to the Board!

    Can you post your code?


  3. #3
    Join Date
    Apr 2004
    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 & ")"
    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

  4. #4
    Join Date
    Apr 2004
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts