scott wrote:

> Get a book?

I think if I ever need to do this for real, that would be the only 
viable option...

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

Post a reply to this message

Gilles Tran wrote:
> 
> I don't understand what you're saying... The entire Office suite uses VBA. 
> Actually that's one big selling point of Office (for businesses): one can 
> automate all the Office applications using the same language and have them 
> interact with each other.

And that's a darn good thing. I've avoided tens or even hundreds of 
hours of clicking around spreadsheets with VBA in past 5 years.

What comes for the missing pre-defined functions, I haven't found an 
"explode" -function so far, so I did need to code one. It wasn't hard - 
macro recorder and surfing the internet told me everything I needed.

> G.
> 


-- 
Eero "Aero" Ahonen
    http://www.zbxt.net
       aer### [at] removethiszbxtnetinvalid

Post a reply to this message

Invisible wrote:
>>> in VBA you'd have to write an explicit FOR loop 
>>
>> Not in Excel. What are you smoking? :-)
> 
> Really? Care to explain that one?

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

-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

Invisible wrote:
> And this stuff isn't really documented anywhere.

Are you familiar with msdn.microsoft.com, the complete documentation for 
everything you program on Windows that comes from Microsoft?

-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

>> And this stuff isn't really documented anywhere.
> 
> Are you familiar with msdn.microsoft.com, the complete documentation for 
> everything you program on Windows that comes from Microsoft?

No. I'm aware it documents the really low-level Win32 API stuff that 
only C programmers understand. I wasn't aware it contains anything 
useful to normal people...

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

Post a reply to this message

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