a few simple newbies questions

[Marco Braun]

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

 

[Chip Pearson]

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

 

[Ricky M. Medley]

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

 

[Marco Braun]

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

 

[Chip Pearson]

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