Some Newbie questions
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Some Newbie questions

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    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


  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:
    Code:
    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

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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


  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com