CODEWITHSUNDEEP
excelvba
Thomas
Thomas

Reputation: 305

Excel keeps freezing when running this code

I am running a function from a command button(ActiveX) and when I run it, excel freezes every other time or so. Does anyone see why?

The code copies a range from an empty formatted sheet, and inserts it into a worksheet where the command button is placed(to insert a new month there).

Function nyMndFunction(navnArk As String)
Dim gammelMnd As String
Dim nyMnd As String
Dim wstEnt As Worksheet
Dim wstMal As Worksheet
Dim insertRange As Range

Set wstMal = Worksheets("Mal")
Set wstEnt = Worksheets(navnArk)

wstMal.Range(wstMal.Cells(1, 1), wstMal.Cells(41, 11)).Copy

gammelMnd = wstEnt.Cells(4, 2).Value

Select Case gammelMnd
    Case "JANUAR"
        nyMnd = "FEBRUAR"
    Case "FEBRUAR"
        nyMnd = "MARS"
    Case "MARS"
        nyMnd = "APRIL"
    Case "APRIL"
        nyMnd = "MAI"
    Case "MAI"
        nyMnd = "JUNI"
    Case "JUNI"
        nyMnd = "JULI"
    Case "JULI"
        nyMnd = "AUGUST"
    Case "AUGUST"
        nyMnd = "SEPTEMBER"
    Case "SEPTEMBER"
        nyMnd = "OKTOBER"
    Case "OKTOBER"
        nyMnd = "NOVEMBER"
    Case "NOVEMBER"
        nyMnd = "DESEMBER"
    Case "DESEMBER"
        nyMnd = "JANUAR"
End Select

wstEnt.Range("B4").Insert Shift:=xlDown
wstEnt.Cells(4, 2).Value = nyMnd
wstEnt.Cells(3, 3).Select
End Function

and then I call it with this in 8 different worksheets

Private Sub cmd_NyMndBravida_Click()
    Dim navnArk As String

    navnArk = ActiveSheet.Name
    nyMndFunction (navnArk)

End Sub

Upvotes: 1

Views: 69

Answers (1)

user3598756
user3598756

Reputation: 29421

I believe it's for the CutCopyMode mode kept active after wstEnt.Range("B4").Insert Shift:=xlDown

so insert Application.CutCopyMode = False statement just after that line:

Function nyMndFunction(navnArk As String)
    Dim gammelMnd As String, monthNames As String

    ...

    wstEnt.Range("B4").Insert Shift:=xlDown

    Application.CutCopyMode = False '<-- statement to be inserted

    wstEnt.Cells(4, 2).value = nyMnd
    wstEnt.Cells(3, 3).Select
End Function

Furthermore you may want to consider the following shortened down code:

code attached to the button

Private Sub cmd_NyMndBravida_Click()

    nyMndFunction ActiveSheet '<--| just pass the worksheet itself, without having to evaluate its name here and then evaluate it back to the worksheet object in 'nyMndFunction'

End Sub

nyMndSub

since it doesn't return anything it's a Sub, so let's write it and name it as such!

Option Explicit

Sub nyMndSub(wstEnt As Worksheet)
    Dim monthNames As String, nyMnd As String
    Dim iMonth As Long
    Dim wstMal As Worksheet

    Set wstMal = Worksheets("Mal")

    monthNames = "JANUAR,FEBRUAR,MARS,APRIL,MAI,JUNI,JULI,AUGUST,SEPTEMBER,OKTOBER,NOVEMBER,DESEMBER" '<--| month names list string

    iMonth = InStr(monthNames, wstEnt.Cells(4, 2).value) '<--| look for the cell content in the month names list
    If iMonth > 0 Then '<--| if found...
        iMonth = Len(Left(monthNames, iMonth)) - Len(Replace(Left(monthNames, iMonth), ",", "")) + 1 '<--| get its "position" inside the list by counting the delimiter occurrences before it and skip to the "next" one
        If iMonth = 12 Then iMonth = 0 '<--| if the "next position" is outside the 12-elements month names list then skip back to the first element
        nyMnd = Split(monthNames, ",")(iMonth) '<--| get the month names in the finally selected "position"

        wstMal.Range(wstMal.Cells(1, 1), wstMal.Cells(41, 11)).Copy '<-- do the copy juts when needed
        With wstEnt
            .Range("B4").Insert Shift:=xlDown
            Application.CutCopyMode = False '<--| exit cutcopymode as soon as possible, i.e. after the clipboard content hs been exploited and no longer needed
            .Cells(4, 2).value = nyMnd
            .Cells(3, 3).Select
        End With
    End If
End Sub

where it's used the [Split()]/https://msdn.microsoft.com/en-us/library/office/gg278528.aspx) function to return an array out of a string with delimiters

as you can see from the linked documentation, it return a zero-based array so that the above code cope with it when dealing with iMonth index

Upvotes: 2

Related Questions