CODEWITHSUNDEEP
vbaexcel
DauleDK
DauleDK

Reputation: 3413

Function is unintendedly called, while running a loop in VBA?

I'm trying to make a loop in VBA, but for some reason it won't work because a user defined function is being called. I don't want the loop to call this function, and I don't understand how it is even possible.

Here is the loop: 

Sub DoWhileExample()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = 5
MsgBox i
Next i
End Sub

Using the error finding tools, I can see that on line 4 in the loop, it starts running this user defined function:

Private Function ForrigeVagtsNavn(ArkNavn As String) As String 'Makro der bliver brugt til at trimme navnet på forrige vagt

Application.Volatile

Dim VagtType As String
Dim Dato As Integer

Dato = Left(ArkNavn, InStr(ArkNavn, " ") - 1)
VagtType = Right(ArkNavn, Len(ArkNavn) - InStrRev(ArkNavn, " "))

  If Dato = 16 And VagtType = "M" Then
    ForrigeVagtsNavn = "Overført"

    ElseIf VagtType = "A" Then
    ForrigeVagtsNavn = Dato & " M"

    Else
    ForrigeVagtsNavn = Dato - 1 & " A"

  End If

  End Function

I really hope you can help me, since it's driving me crazy. Thanks in advance.

Upvotes: 1

Views: 1241

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub DoWhileExample()
    Dim i As Integer
    sav = Application.Calculation
    Application.Calculation = xlCalculationManual
    For i = 1 To 10
        Cells(i, 1).Value = 5
        MsgBox i
    Next i
    Application.Calculation = sav
End Sub

Upvotes: 1

Related Questions