Some Newbie questions

[stevenmcheerful]

Hello - I'm trying to create some templates that I want to be able to add data to from an ASP app. I'm struggling with a couple of issues:

- How do you (or *can* you) enter code in
the formula window ? i.e., I want to COUNTA() and SUM() some columns that will be variable width, but the Formula entry will not accept VBA like Range("A1").End and the like ...

- I gather that you cannot create a VBA Function (add-in) that returns a Range ?
(that would be really helpful)

- I have created some user-defined Functions in VBA, but they seem limited to returning a numerical result. So I guess in order to do things like choose a Range, such as A1.End(xlDown) it needs to be in a Macro.

I can write macros, but I want them to fire off based on the data in my columns - just like cell formulas. How do I make that happen ?

and last, is there a web site or specific book I ought to pick up to cover these things ?

Thank-You

[Al Chara]

Try the following:

Sub EnterFormula()
Dim rng As String
rng = Range(Range("A1"), Range("A1").End(xlDown)).Address(rowabsolute:=False, columnabsolute:=False)
Range("B1").Formula = "=MAX(" & rng & ")"
End Sub

Barrie as some good links:
http://ca.geocities.com/b_davidso/
Try some books here:
http://www.mrexcel.com/book.shtml

[stevenmcheerful]

Thanks - it works well

How though, do I use something like
that to behave like a formula, in that
I want that Sub to run whenever the
spreadsheet is calculated - my Excel
subs and Functions will be executed
in a batch mode - the user will never
see the spreadsheet.

The spreadsheet is a means to to getting
the numbers calculated and the Charted
with an Excel chart. The chart is then
exported to a .GIF file.

In other words, is there a RUN() command
or something else that is automatic and
does not require a button or some other
interactivity ?

thanks again

[Al Chara]

Sure its called an event. In excel, hit Alt+F11, that will bring you to VBA. Then look to the left for the project explorer. Double click "ThisWorkbook". At the top you will see a drop down list "(General)", click on it and select "Workbook". Then on the drop down list to the right, select "SheetCalculate" You should see the following:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Insert your code in between Private Sub and End Sub. "Sh" is a variable name that represents the worksheet that is being calculated.

[stevenmcheerful]

Events - alright I will look into it

Don't know why I hadn't run into that before

Thanks for your assistance and the
link / book suggestion

[stevenmcheerful]

One last question please:

I'm doing OK with EnterFormula

I will be entering data into excel from
a program (ASP actually) so therefore
I will never know the lenght of my primary
data column (i.e. how many rows) ahead
of time.

so if A2:A50 is some column of data numbers,
(the next instantiation of the program may
bring a column of data A2:A385, that is
to say A2:A? )
how do I restrict all of the formulas from
B2:T? from calculating all the way down
to T65536 ?

what I am doing is this (so far) in
SheetCalculate I am finding the last row
in A (call it 200) and then setting it
to g_numRows in this sort of way:

If (Range("N2").Value = "") Then
For i = 3 To g_numrows
s = "N" & i
Range("N2").Copy Destination:=Range(s)
Next i
End If

is this what ppl are doing ? or is there
a better way (I suspect there is)

- steve