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

>> 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

Actually I meant "how do I make it so I can write =MyMacro(A4:C9) in a 
cell formula and have it actually work properly?"

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

>>> 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
> 
> Actually I meant "how do I make it so I can write =MyMacro(A4:C9) in a 
> cell formula and have it actually work properly?"
> 

Ah, so you want to convert your macro into a formula function? I would 
look at the manual for you... but I uninstalled M$ Office a while ago :)

Post a reply to this message

And lo on Thu, 13 Mar 2008 12:23:47 -0000, Invisible <voi### [at] devnull> did
  

spake, saying:

>>> 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 mann
er  

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

>> 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
>
> Actually I meant "how do I make it so I can write =MyMacro(A4:C9) in
 a  

> cell formula and have it actually work properly?"

You can't with macros you need to use a function instead so add a module
  

to your worksheet and try

Public Function Temperature(Degrees As Double, ConvertType)
     If ConvertType = "C" Then
     Temperature = (Degrees - 32) * (5 / 9)
     Else
         If ConvertType = "F" Then
         Temperature = (Degrees / 5 / 9) + 32
         Else
         Temperature = CVErr(xlErrNA)
     End If
End Function

Now go to your worksheet and enter =Temperature(32, "C") and you'll ge
t a 0

-- 

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

Darren New wrote:

> Yeah. I don't need a loop to call SUM(A1:A10) either. :-)

Right. So I picked a bad example. :-P

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message