Код «не отвечает» в excel 2013. преобразовать в формулы / новый код

У меня есть код ниже, который работает в Excel 2010.

Я обновился до 2013 года и теперь не отвечаю.

Я попытался заменить этот код 16 формулами EE2: ET6141. Я обнаружил, что это неэффективно, требует много времени и использует много памяти.

формула для замены моего кода:

IF(AND(OR($BM:$BM="value1",$BM:$BM="value2",$BM:$BM="value3"),$BN:$BN<>"Excel",$BN:$BN<>"ITS"),$AT:$AT*(IFNA(VLOOKUP(EE$1&$S:$S,EQ_Shocks!E:F,2,FALSE),VLOOKUP(EE$1&"OTHERS",EQ_Shocks!E:F,2,FALSE))-1),"")

Логика примерно такая:
для каждой строки, где A1 = Stackoverflow, BM = значение1 или значение2 или значение3 и BN не в Excel и не в ITS. затем получите значение из листа EQ_shocks.

код

В этой строке появляется сообщение об отсутствии ответа. If thisEqShocks(1, 1) = "#EMPTY" Then. Я установил точку останова на end if, и на выполнение этого большого оператора if требуется много времени или нет ответа.

Я также заметил, что в цикле for, For thisScen = 1 To UBound(stressScenMapping, 1), требуется много времени, чтобы ответить через точку останова Next thisScen. Я бы сказал, что можно с уверенностью сказать, что это эта часть кода.

Public Sub oldcode() 
    Application.ScreenUpdating = False

    Dim i As Long, thisScen As Long, nRows As Long, nCols As Long
    Dim stressWS As Worksheet

    Set stressWS = Worksheets("EQ_Shocks")
    Unprotect_Tab ("EQ_Shocks")
    nRows = lastWSrow(stressWS)
    nCols = lastWScol(stressWS)

    Dim readcols() As Long
    ReDim readcols(1 To nCols)

    For i = 1 To nCols
        readcols(i) = i
    Next i

    Dim eqShocks() As Variant
    eqShocks = colsFromWStoArr(stressWS, readcols, False)

    'read in database columns
    Dim dataWs As Worksheet
    Set dataWs = Worksheets("database")

    nRows = lastRow(dataWs)
    nCols = lastCol(dataWs)

    Dim dataCols() As Variant
    Dim riskSourceCol As Long
    riskSourceCol = getWScolNum("RiskSource", dataWs)

    ReDim readcols(1 To 4)
    readcols(1) = getWScolNum("RiskReportProductType", dataWs)
    readcols(2) = getWScolNum("Fair Value (USD)", dataWs)
    readcols(3) = getWScolNum("Source Currency of the CUSIP that is denominated in", dataWs)
    readcols(4) = riskSourceCol

    dataCols = colsFromWStoArr(dataWs, readcols, True)

    'read in scenario mappings
    Dim mappingWS As Worksheet
    Set mappingWS = Worksheets("mapping_ScenNames")

    Dim stressScenMapping() As Variant
    ReDim readcols(1 To 2): readcols(1) = 1: readcols(2) = 2
    stressScenMapping = colsFromWStoArr(mappingWS, readcols, False, 2) 'include two extra columns to hold column number for IR and CR shocks

    For i = 1 To UBound(stressScenMapping, 1)
        stressScenMapping(i, 3) = getWScolNum(stressScenMapping(i, 2), dataWs)
        If stressScenMapping(i, 2) <> "NA" And stressScenMapping(i, 3) = 0 Then
            MsgBox ("Could not find " & stressScenMapping(i, 2) & " column in database")
            Exit Sub
        End If
    Next i

    ReDim readcols(1 To 4): readcols(1) = 1: readcols(2) = 2: readcols(3) = 3: readcols(4) = 4
    stressScenMapping = filterOut(stressScenMapping, 2, "NA", readcols)

    'calculate stress and write to database
    Dim thisEqShocks() As Variant

    Dim keepcols() As Long
    ReDim keepcols(1 To UBound(eqShocks, 2))
    For i = 1 To UBound(keepcols)
        keepcols(i) = i
    Next i

    Dim thisCurrRow As Long

    For thisScen = 1 To UBound(stressScenMapping, 1)

        thisEqShocks = filterIn(eqShocks, 2, stressScenMapping(thisScen, 1), keepcols)

        If thisEqShocks(1, 1) = "#EMPTY" Then
            For i = 2 To nRows
                If dataCols(i, 4) <> "Excel" And dataCols(i, 4) <> "OBI" And (dataCols(i, 1) = "value1" Or dataCols(i, 1) = "value2") Then
                    dataWs.Cells(i, stressScenMapping(thisScen, 3)).Value = "No shock found"
                End If
            Next i
        Else                                     'calculate shocks
            Call quicksort(thisEqShocks, 3, 1, UBound(thisEqShocks, 1))
            For i = 2 To nRows
                If dataCols(i, 4) <> "Excel" And dataCols(i, 4) <> "ITS" And (dataCols(i, 1) = "value1" Or dataCols(i, 1) = "value2" Or dataCols(i, 1) = "value3") Then
                    thisCurrRow = findInArrCol(dataCols(i, 3), 3, thisEqShocks)
                    If thisCurrRow = 0 Then      'could not find currency so use generic shock
                        thisCurrRow = findInArrCol("OTHERS", 3, thisEqShocks)
                    End If
                    If thisCurrRow = 0 Then
                        dataWs.Cells(i, stressScenMapping(thisScen, 3)).Value = "No shock found"
                    Else
                        dataWs.Cells(i, stressScenMapping(thisScen, 3)).Value = Replace(dataCols(i, 2), "-", 0) * (thisEqShocks(thisCurrRow, 4) - 1)
                    End If
                End If
            Next i
        End If

    Next thisScen
    Application.ScreenUpdating = True
End Sub

добавление функции быстрой сортировки

Sub quicksort(ByRef arr() As Variant, ByVal sortCol As Long, ByVal left As Long, ByVal right As Long)

    If right > left Then
        Dim pivotIndex As Long
        pivotIndex = left + Int((right - left) / 2)

        Dim pivotIndexNew As Long
        pivotIndexNew = partition(arr, sortCol, left, right, pivotIndex)
        Call quicksort(arr, sortCol, left, pivotIndexNew - 1)
        Call quicksort(arr, sortCol, pivotIndexNew + 1, right)
    End If

End Sub

Не могли бы вы, возможно, использовать F8 через свой код и сообщить нам, когда он зависает? Это много домашнего кода (как и множество UDF), чтобы проследить этот вопрос

Marcucciboy2 10.08.2018 16:05

@ Marcucciboy2, это цикл if, If thisEqShocks(1, 1) = "#EMPTY" Then или даже цикл for. For thisScen = 1 To UBound(stressScenMapping, 1)

excelguy 10.08.2018 16:14

Вы пытаетесь сравнить его со строковым значением "#Empty"? «#» может работать как подстановочный знак, представляющий любую цифру 0-9.

Mistella 10.08.2018 16:20

@Mistella, не думаю, что бы вы порекомендовали здесь делать? Кроме того, в Excel 2010 этот код работает за секунды, поэтому на самом деле его функциональность требует изменения не столько, сколько дополнительной оптимизации.

excelguy 10.08.2018 16:28

Ваша цель - проверить эту точную строку или проверить, что ячейка пуста? Я не часто использую формулы, но не думаю, что раньше видел одно решение #EMPTY.

Marcucciboy2 10.08.2018 17:22

Я видел #REF!, #VALUE!, #DIV/0!, #NAME!, #NULL! ... но я не думаю, что "пустой" - все же, возможно, я просто не видел его, в каком случае, может быть, это? поможет excelfunctions.net/vba-iserror-function.html

Marcucciboy2 10.08.2018 17:24

Кроме того, когда формула ячейки приводит к значению ошибки, я не уверен, что содержимое интерпретируется как строка. Однако существует функция VBA IsEmpty() (?), Которая может работать аналогично.

Mistella 10.08.2018 17:27

@ Marcucciboy2 Я тоже не думаю, что ошибка #empty возможна. Этот код ищет строку #EMPTY.

excelguy 10.08.2018 17:52

@Mistella ладно разобрался, ищет строку #EMPTY

excelguy 10.08.2018 17:55

#EMPTY, вероятно, является заполнителем, чтобы сделать его очевидным для пользователя, поэтому это должна быть строка, а не константа xl или ошибка рабочего листа. Это не должно быть проблемой, если OP не ожидал другого поведения или не допустил глупую ошибку. Одна вещь, которую я заметил, в обоих местах, где он, как сообщается, зависает, есть вызовы внешних функций / процедур. Можем ли мы быть уверены, что проблема не в этом. Я могу представить, как обновление изменяет то, что мы не можем сразу заметить, возможно, захочется экспортировать проект и перенести его в чистую книгу.

ProfoundlyOblivious 15.08.2018 05:01

У меня есть функция quicksort, я ее тоже опубликую. но когда я просматриваю код, он обычно там не висит и много раз ссылается на эту функцию.

excelguy 15.08.2018 15:09

любая помощь для меня?

excelguy 17.08.2018 16:16

У вас есть два отдельных «For i = 2 To nRows». Считаете ли вы, что оба работают очень медленно или только один из них, и если да, то какой?

George Birbilis 18.08.2018 01:02

Думали ли вы проверить различия в ссылочных библиотеках между версиями Excel - примерно так: excel.tips.net/… - обычно при загрузке вы получаете сообщение об ошибке «Не могу найти проект или библиотеку», но в некоторых случаях я видел, что библиотека то же самое от одной версии Excel к другой, но изменение поведения отрицательно влияет на код.

tremor 18.08.2018 21:25

@tremor, спасибо. Попробую. Довольно неприятно, что это работает идеально в 2010 и 2013 годах, оно просто зависает. Это определенно не мой компьютер, так как у него довольно хорошая производительность. И у меня нет возможности вернуться к 2010 году, обновить или исправить мой 2013 год.

excelguy 20.08.2018 03:10

@tremor вы можете что-нибудь порекомендовать для авто справочного кода 2010 и 2013 годов?

excelguy 20.08.2018 03:16
4
16
240
0

Другие вопросы по теме