Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Write an Excel function to write data to a worksheet by lancish
Posted: 12 Aug 04

Suppose you want to write a function that will return an ARRAY of values to be written to a designated area of a worksheet. Excel does not (seem to) permit you to directly write to individual cells, one by one (which I tried: see my Thread707-892318). The short answer is to use an ARRAY FORMULA.

For example, I have a RETIREMENT_FUND function that takes in a host of parameters about expected incomes, expenses, mortgage payoffs, etc., and it computes, month by month, the value of a starting fund up to some years in the future. I wanted to print out the yearly summaries for 13 components of the fund. I dimensioned an internal array for up to 100 years of data, Dim FUND(1 to 100, 1 to 13) As Variant, and I accumulated monthly results for a year and then wrote them to the next row of the array.

Here's the key: Once the function was written, I highlighted the part of the active worksheet in which I wanted to position my 100x13 FUND array, then typed in =RETIREMENT_FUND(...) with its 30-odd cell-reference arguments, then pressed Ctrl+Shift+Enter, which causes the function to be entered in all 1300 cells as an "ARRAY FORMULA." As you change parameters, the function will write out different values to the array space.

You can, of course, write anything to the worksheet -- results of a game (e.g., battleship), a calendar, compiled text, etc.

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close