Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code for UDF (Excel)

    Hi,
    I have an invisible worksheet with data and a visible worksheet. I have put the name range using code to some ranges of data in the invisible worksheet, e.g: stock1, stock2. Now, i want the covariance of stock1 and stock2 to appear in the visible sheet (say cell A1) when a command button is pressed. How can i do that. I have a problem to do code for reference to cavariance in one worksheet and the output appears in another. Pls help me ;(

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: code for UDF (Excel)

    I may not understand your question, but =covar(Stock1,Stock2) works for me. I have attached my sample workbook. To unhide Sheet2, use the Format, Sheet, Unhide. HTH -- Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: code for UDF (Excel)

    Just in case it was the button that you needed help with, see revised attached workbook. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Feb 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for UDF (Excel)

    Thank you for the help...but i'having a problem with the code to be used in a standard module. I cant use 'me' keyword in the standard module (which i put the rest of my codes). here's my codes,
    Range("A1").Select
    Range(ActiveCell.Offset(1, 2), ActiveCell.Offset(0, 2).End(xlDown)).Name = "price"
    ActiveCell.End(xlDown).Select
    Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(0, 2).End(xlUp)).Name = "lag"
    Me.Cells(1, 1).FormulaR1C1 = "=COVAR(price,lag)"

    I have 20 similar codes.....so, i need them to be in the std module. What should i do to replace 'me' keyword? Sorry, if u think this is a silly question. [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for UDF (Excel)

    Does this do what you want:

    <pre> ActiveSheet.Range(Range("C2"), Range("C1").End(xlDown)).Name = "price"
    With Range("A1").End(xlDown)
    ActiveSheet.Range(.Offset(-1, 2), .Offset(0, 2).End(xlUp)).Name = "lag"
    End With
    ActiveSheet.Cells(1, 1).FormulaR1C1 = "=COVAR(price,lag)"
    </pre>

    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: code for UDF (Excel)

    Ah, I see. If your sheet is the active sheet then Legare's code is what your want. If it is not the active sheet then replace the me with Worksheets("Blotz"), where Blotz is the name of your sheet. In any case, avoid the ActiveCell & Select in your code, it just confuses and slows down the code.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for UDF (Excel)

    I didn't know you could confuse your code. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Is it anything like confusing a geek?
    <IMG SRC=http://www.noded.com/archives/images/DSC_2753.jpg>

    Alan

Posting Permissions

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