Click here to reset your password. You MUST have a valid e-mail address so that you may receive the instructions to complete the reset.

If this is your first visit, be sure to check out the Board FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.

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 ?

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 ?

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:

Code:

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.

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)