VBA code (2002/SP3)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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(…)?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Hi Hans
[indent]

---

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.

---

[/indent]
Thank You! A 300W lamp just illuminated.

Reply | Quote

And that despite the error in my grammar!

Reply | Quote

Thanks for your help Hans.

Reply | Quote

There is one important difference between ActiveSheet.Range("A1").Cells(2, 3).Value and Cells(2, 3).Value. 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:

Private Sub CommandButton1_Click()
   Sheets("Sheet2").Select
   Range("A1").Select '<-- error: this refers to A1 on Sheet1 which is no longer active.
End Sub

Reply | Quote