Some Newbie questions

stevenmcheerful

New Member
Joined
Apr 16, 2002
Messages
20
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top