And lo on Wed, 12 Mar 2008 14:41:20 -0000, Invisible <voi### [at] devnull> did
  

spake, saying:

> scott wrote:
>>> And this stuff isn't really documented anywhere.
>>  Rubbish!
>>  Try looking up "range" or "cells" or "worksheet" in the object brows
er  

>> and reading the help.
>
> What is the "object browser"?
>
>> Worksheets(1).Cells(1, 1).Value = 24
>>  ActiveSheet.Cells(2, 1).Formula = "=Sum(B1:B5)"
>>  How could it be any more clear what to do?
>
> That seems reasonably clear.

or even MyValue = Worksheets("Results").Range("A1:D5").Value

> Now, if you call a macro with a cell range as its argument, how does  

> that work?

Not sure what you mean, you can pass arguments on in the normal manner  

i.e. Call Macro1(MyRange) from within another macro, or do you mean how 
do  

I pass what you've selected in the worksheet to the macro you're running
?  

Which would be

ActiveWorksheet.ActiveCell or better ActiveWorksheet.Selection

so the date function could be

Sub InsertDate()
On Error Goto Handler
ActiveWorksheet.ActiveCell.Value = Format(Date, "Medium Date")
Exit Sub
Handler: MsgBox ("No cell selected")
End Sub

Depends on what you're exactly trying to do though, if you're trying to 
 

ensure that everytime a sheet is accessed a date/time stamp is appended 
to  

a list on a sheet then

Sub AddStamp()
NoOfEntries = Worksheets("Date").UsedRange.Rows.Count
Worksheets("Date").Cells(NoOfEntries + 1, 1).Value = Format(Date, "Med
ium  

Date")
End Sub

Just stick the word "Date" in A1 on the sheet "Date" then hide the sheet
  

then run it at startup from ThisWorkbook with

Private Sub Workbook_Open()
Call AddStamp()
End Sub

It also has the advantage that UsedRange counts every cell that *has* be
en  

used, so if some sneaky soul just deletes the date (rather then the enti
re  

row) it'll be left blank when the next stamp is placed.

>> Or, as Gilles said, record a macro and then look at the code (and the
  

>> help related to the code generated) - a nice trick for learning how t
o  

>> do lots of things in Excel (like creating charts, changing borders,  

>> sorting etc).
>
> I'm still at the level of not really knowing how to work the VB editor
,  

> and not understanding basic VB syntax constructs...

Recording stuff and picking it apart really helps, just pick the command
  

and hit F1 for a meaning and example.

-- 

Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message