Hello out there,

I am just getting into seriously editing my makros in VBA and I
encounter a few things I'd like to know and which must be easy answers
for those who know.

1. if I pass an object, say a worksheet to a sub-routine will it be
passed by value or will it be passed by reference. What I want to know:
If I make changes on that passed worksheet will they be there in the
routine that called and passed the sub-routine? My guess: pass by reference.

2. Where is a good place to look up all I need to know about functions
and subs, about objects, their methods and attribs in VBA? Where can I
read about returned values and arguments? I can't believe the object
catalog is the best I can get....

3. How do I place a formula in a cell?


Thanks so far
Marco

Marco,

1) By default, everything is passed by reference. With objects,
if you specify ByVal, the address of the object is passed by
value. If you specify ByRef, the address of the object is passed
by reference. In both cases, you change the properties of the
object. The difference is that with ByVal, you can't assign the
parameter to a difference object and have that change take effect
in the calling procedure.

2) The VBA help files are fairly good.

3) Use the Formula or FormulaLocal property of the Range to place
a formula in a cell. E.g.,

Range("A1").Formula = "=SUM(B1:B10)"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Marco Braun" wrote in message
...
Hello out there,

I am just getting into seriously editing my makros in VBA and I
encounter a few things I'd like to know and which must be easy
answers
for those who know.

1. if I pass an object, say a worksheet to a sub-routine will
it be
passed by value or will it be passed by reference. What I want
to know:
If I make changes on that passed worksheet will they be there
in the
routine that called and passed the sub-routine? My guess: pass
by reference.

2. Where is a good place to look up all I need to know about
functions
and subs, about objects, their methods and attribs in VBA?
Where can I
read about returned values and arguments? I can't believe the
object
catalog is the best I can get....

3. How do I place a formula in a cell?


Thanks so far
Marco

1. in order for the sub not to modify the variable you must use byval..
ex.
Sub DontChangeMe(ByVal <variable as <type)

It doesn't matter what the name of the subs variable is, thus the point of
subs and not hardcoding regularly used routines in main code.


2. see vba help files per the application you want to learn. I've come a
long way myself.

2. see vba help files per the application you want to learn...just joking.
ThisWorkbook.Worksheets("MY worksheet").Cells(6, 1).Formula =
"=sum(A1:A5)"
NOTE:
[6,1] = Row 6, Column 1


ricky

Hey Chip,

thank you for your quick reply...

3) Use the Formula or FormulaLocal property of the Range to place
a formula in a cell. E.g.,

Range("A1").Formula = "=SUM(B1:B10)"


this works fine, but what are the other Formulas:

FormulaR1C1
Formula1
Formula2
FormulaLocal

???
wonders on
Marco

Marco,

The FormulaR1C1 property is used for entering formulas in R1C1
style notation rather than A1 style notation. FormulaLocal is
used to enter a formula in the locale language (e.g., German or
French). Formula1 and Formula2 are used to create Data Validation
or Conditional Formatting conditions in a cell.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Marco Braun" wrote in message
...
Hey Chip,

thank you for your quick reply...

3) Use the Formula or FormulaLocal property of the Range to
place
a formula in a cell. E.g.,

Range("A1").Formula = "=SUM(B1:B10)"


this works fine, but what are the other Formulas:

FormulaR1C1
Formula1
Formula2
FormulaLocal

???
wonders on
Marco