Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code (2002/SP3)

    I created a VBA code in Excel and upon running it, I get a strange behaviour. When I run the code it calls on a random function. A copy of the code is shown below:

    Sub CreateSheet()
    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.Range("A1")
    Range("1:" & Rows.count).Clear

    rng.Select
    Cells(1, 1).Value = "Analysis Summary"
    Cells(3, 2).Value = "Analysis was run for:"
    Cells(3, 3).Formula = "=EPdiv2_white_mod!R[2000]C[-2]"
    Cells(3, 4).Formula = "Perform analysis summary for the last:"

    End Sub

    I put the code in the Modules section of the VBA Project. Any idea why this happens?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code (2002/SP3)

    What exactly do you mean by "it calls on a random function"?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code (2002/SP3)

    For some odd reason it went to another function on Module 1. When I comment out this function, the code runs well.

    No idea why this happened.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code (2002/SP3)

    I figured out why where this function is called. In the VBA code that I made, I wrote:

    Cells(6, 1).Formula = "=sheetname(EPdiv2_white_mod!R[-5]C,FALSE)"

    where I put the formul "=sheetname(EPdiv2_white_mod!R[-5]C,FALSE)" in cell A6. "sheetname()" is the function name, that the code was going to even before it was called. Is this the write way to call this function?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code (2002/SP3)

    That line wasn't in the code you originally posted.
    If you place a formula in a cell, it will be evaluated immediately even if automatic calculation has been turned off. So if you place a formula that uses the sheetname function in a cell, the sheetname function will be called instantly.
    Was it your intention that cell A6 would contain a formula that uses sheetname(...)?

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code (2002/SP3)

    No, I didn't want the formula to appear in cell A6.

    Also another question. What's the difference between ActiveSheet.Range("A1").Cells(2, 3).Value and Cells(2, 3).Value?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code (2002/SP3)

    So what did you want to do with cell A6?

    Cells(2, 3) and Range("A1").Cells(2, 3) and ActiveSheet.Cells(2, 3) and ActiveSheet.Range("A1").Cells(2, 3) are equivalent: they all refer to cell C2 on the currently active sheet.

    But Range("D4").Cells(2, 3) and ActiveSheet.Range("D4").Cells(2, 3) would refer to cell F5 - you would now starting from D4 as Cells(1, 1), so Cells(2, 3) is one row down and two columns to the right.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code (2002/SP3)

    Hi Hans
    <hr>But Range("D4").Cells(2, 3) and ActiveSheet.Range("D4").Cells(2, 3) would refer to cell F5 - you would now starting from D4 as Cells(1, 1), so Cells(2, 3) is one row down and two columns to the right.
    <hr>
    Thank You! A 300W lamp just illuminated.
    Regards
    Don

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code (2002/SP3)

    And that despite the error in my grammar! <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code (2002/SP3)

    Thanks for your help Hans.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA code (2002/SP3)

    There is one important difference between <code>ActiveSheet.Range("A1").Cells(2, 3).Value</code> and <code>Cells(2, 3).Value</code>. If your code is in a Worksheet code module, then the latter refers to to cell C2 on the sheet containing the code, even if it is not the active sheet. This means that code like this in the module of Sheet1 would fail:
    <pre>Private Sub CommandButton1_Click()
    Sheets("Sheet2").Select
    Range("A1").Select '<-- error: this refers to A1 on Sheet1 which is no longer active.
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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