CODEWITHSUNDEEP
excelvba
tbischel
tbischel

Reputation: 6477

how to write to a cell from VBA

seems like a dumb question even for a VBA newbie, but I can't figure out how to write to a cell in VBA... I have a function in "Module1" that looks like this:

Function Foo(bar As Boolean)
  Range("A1").Value = 1
  Foo = ...
End Function

Then say I set Cell A2's formula to:

=Foo(true)

The call itself works if I take out the range setting line... in that case setting the calling cell to the foo value. But I'd like this to eventually write a ton of cells at once rather than have a different function call for each cell. What am I doing wrong???

If this code should work as is... are there settings in Excel 2007 that might be blocking editing a cell, or something like that?

Upvotes: 3

Views: 9814

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

To create a custom function in VBA enter the following in a Module

Public Function MyCalc(ByRef r as Range) as Double
    MyCalc = r.Value^2
End Function

or

Public Function MyCalc(ByVal x as Double) as Double
    MyCalc = x^2
End Function

and use it on a worksheet as =MyCalc(A2) which returns and sets the appropriate value. Choose if you want to pass a value, or a range depending on what you want to do.

Upvotes: 0

Richard Fawcett
Richard Fawcett

Reputation: 2809

According to this page, it's not possible for an Excel user defined function to alter other parts of the Workbook. Looks like you may have to try another way.

Upvotes: 2

Related Questions