Excel VBA crashes by changing the value on a cell

Question

i am new here and try to learn VBA Excel but it doesn't work.

I want a function with one paramter to insert by each cell an increased value.

Option Explicit

Function DefineAI(rngToSet As Range) As String
    Dim intCounter As Integer
    Dim cell As Range

    On Error GoTo Fehler
    Let intCounter = 1
    For Each cell In rngToSet.Cel
        'cell.Value = intCounter         'Test 1: It crashes with no error
        Range(cell).Value = intCounter   'Test 2: It crashes with no error
        intCounter = intCounter + 1      'intCounter++ ?? not important yet
    Next

    DefineAI = "test"
    Exit Function
Fehler:
    Debug.Print Err.Description
End Function

It crashes on two different Computers with no Error! Actually it doesn't crash, but just stop.

I have also try with a sub, but the same result.

hmm. some things to change: 1. for each cell in rngtoset 2. cell.value=intcounter 3. omit the let — ZAT
– ZAT, Commented Oct 25, 2014 at 19:02
I have it changed as described above, but it doesn't always work. — Free91
– Free91, Commented Oct 25, 2014 at 19:31

user4039065 · Accepted Answer · 2014-10-25 19:27:42Z

1

You cannot change the value of another cell with a UDF Function and cannot call a Sub from within a UDF Function that changes the value of another cell. If you are looking to run a macro that will perform what you wish then here is some code that runs.

Sub mcr_Call_DefineAI()
    Call mcr_DefineAI(Range("D2:D10"))
End Sub

Private Sub mcr_DefineAI(rngToSet As Range, Optional iSTART As Long = 1)
    Dim i As Long, r As Range

    On Error GoTo Fehler
    i = iSTART
    For Each r In rngToSet
        r = i
        i = i + 1
    Next

    Debug.Print "Test funktionierte!"
    Exit Sub
Fehler:
    Debug.Print Err.Description
End Sub

I've included an optional parameter so you can change the starting number of the increment.

answered Oct 25, 2014 at 19:27

user4039065

Sign up to request clarification or add additional context in comments.

Comments

RubberDuck · Accepted Answer · 2014-10-25 18:59:37Z

1

You've declare cell as a Range, then pass it into a Range. The problem is that Range expects a string representing the address of a cell. So, you get a runtime error.

   Range(cell).Value = intCounter   'Test 2: It crashes with no error

To fix it, just set the value of cell.

cell.Value = intCounter

I also noticed this comment.

 'intCounter++ ?? not important yet

You should know that VBA does not have an increment operator. You have incremented your variable correctly.

intCounter = intCounter + 1

That's just how it's done in VBA. (And yes, it kinda sucks.)

answered Oct 25, 2014 at 18:59

RubberDuck

12.9k5 gold badges56 silver badges99 bronze badges

1 Comment

Free91 Over a year ago

But it doesn't work with cell.Value = intCounter too. The problem was the UDF.., thanks for helping!

Collectives™ on Stack Overflow

Excel VBA crashes by changing the value on a cell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related