Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

VBA Excel - formuła zaczyna działać po zmianie wartości w komórkach

Zapis 02 Lut 2018 21:31 279 5
  • #1 02 Lut 2018 21:31
    Zapis
    Poziom 2  

    Witam serdecznie,

    Napisałem funkcję która sumuje i wymnaża mi w określony sposób komórki z określonego zakresu. Wszystko działa i liczy poprawnie, ale.

    Gdy wpiszę formułę(Zakres A1:B3) i kliknę enter. Wynik zawsze pojawia się 0. Jednak gdy zmienię wartość w którejś z komórek z zakresu (A1:B3) formuła pokazuje oczekiwany wynik. W którym miejscu robię błąd.?

    Code:
     Function Cost(rate As Range, Arg1 As Range, Employes As Integer) As Double
    
       
        Dim k As Integer
        Dim l As Integer
        i = Arg1.Row
        r = rate.Row
        t = rate.Column
       
        Application.ScreenUpdating = False
        Application.Calculation = xlManual
       
        For k = 1 To Employes Step 1
            j = Arg1.Column
            Cost = Cost + (Cells(i, j) + Cells(i, j + 6)) * Cells(r, t) * 1.5
            For l = 1 To 5 Step 1
                Cost = Cost + Cells(i, j + l) * Cells(r, t)
            Next l
            i = i + 1
            r = r + 1
        Next k
        Application.Calculation = xlAutomatic
        Calculate
        Application.ScreenUpdating = True
    End Function

    0 5
  • #2 03 Lut 2018 08:10
    adamas_nt
    Moderator Programowanie

    Zamień miejscami:
    Calculate
    Application.ScreenUpdating = True
    A jeśli to nie pomoże, na końcu funkcji wpisz cost=cost

    0
  • #3 03 Lut 2018 13:24
    lanzul
    Poziom 26  

    Zmienne: i, j, r, t są gdzieś zadeklarowane poza funkcją ? Inne deklarujesz.
    Po co w ogóle to wyłączanie ekranów ?
    Po co 'j = Arg1.Column' znajduje się wewnątrz pętli, skoro nigdzie nie zwiększasz licznika 'j' i jest on cały czas stały ?
    Jeśli funkcja operuje na zakresach wielokomórkowych, to gdzie w niej masz odniesienie do tego, bo nigdzie tu nie ma odniesienia do ilości komórek większej jak jedna, nigdzie nie ma ani 'Rows.Count', ani 'Columns.Count', ani ParamArray ? Załącz może jakiś drobny przykład dla zobrazowania ?

    1
  • #4 04 Lut 2018 18:55
    Zapis
    Poziom 2  

    Dzięki wielkie za naprowadzenie lanzul ;)

    Code:
    Function Totalcost(zakres As Variant, rate As Variant) As Double
    
    Dim i, j As Integer

        For i = 1 To zakres.Rows.Count
            j = 1
            Totalcost = Totalcost + ((zakres.Cells(i, j) + zakres.Cells(i, j + zakres.Columns.Count - 1)) * 1.5) * rate.Cells(i, 1)
            For j = 1 To zakres.Columns.Count - 2
                Totalcost = Totalcost + (zakres.Cells(i, j + 1).Value) * rate.Cells(i, 1)
            Next j
        Next i
       
    End Function


    Jednak napotkałem jeszcze jeden problem.
    Otóż. Mam powtarzające się kolumny 1,2,3,4,5,6,7,1,2,3,4,5,6,7
    i tak się ciągną w prawą stronę.Sprawa polega na tym aby zsumować wartości z kolumn 2,3,4,5,6,2,3,4,5,6. i w osobnej 1,7,1,7,1,7. Wiem, że mógłbym najzwyczajniej w świecie użyć wbudowanej funkcji sumy dla wielu zakresów. Jednak jest to dosyć czasochłonne przy dużej ilości kolumn. Tak samo zmiana wyglądu formularza nie wchodzi w grę.
    Kod do kolumn 1,7 wygląda następująco i działa.
    Code:
    Function TotalHours2(zakres As Variant) As Double
    
    Dim i, j As Integer

        For i = 1 To zakres.Rows.Count
            For j = 1 To zakres.Columns.Count Step 7
                TotalHours2 = TotalHours2 + zakres.Cells(i, j) + zakres.Cells(i, j + 6)
            Next j
        Next i
       
    End Function

    Więc analogicznie starałem się zrobić dla opcji 2,3,4,5,6 dodając jeszcze jednego fora do środka.
    Code:
    Function TotalHours1(zakres As Variant) As Double
    
    Dim i, j, k As Integer

        For i = 1 To zakres.Rows.Count Step 1
            For j = 1 To zakres.Columns.Count Step 7
                For k = 1 To k = 5 Step 1
                    TotalHours1 = TotalHours1 + zakres.Cells(i, j + k)
                Next k
            Next j
        Next i
       
    End Function

    Jednak ta funkcja kompletnie nie działa.

    Code:
    Function TotalHours1(zakres As Variant) As Double
    
    Dim i, j, k As Integer

        For i = 1 To zakres.Rows.Count Step 1
            For j = 1 To zakres.Columns.Count Step 7
                    TotalHours1 = TotalHours1 + zakres.Cells(i, j + 1) + zakres.Cells(i, j + 2) + zakres.Cells(i, j + 3) + zakres.Cells(i, j + 4) + zakres.Cells(i, j + 5)
            Next j
        Next i
       
    End Function


    Omijając For wewnętrzny(k) Wszystko działa jak należy. W którym miejscu popełniam błąd ?
    __________________________________________________________________

    I Jeszcze jedna rzecz z którą kompletnie nie mogę sobie poradzić.
    Chciałbym makro które zmieni nazwę arkusza na występujące w pewnej komórce.
    A następnie,aby zapisało ten arkusz osobno pod nazwą tego arkusza.
    Mam pewien działający, ale raz działa raz nie. + jeżeli w folderze występuje plik o takiej samej nazwie wyłącza cały program bez żadnego zapisu. Najlepiej aby włączany był z przycisku ale na pojedynczo zapisanym arkuszu ten przycisk musiałby zniknąć. Czy jest ktoś w stanie pomóc, podrzucić jakich formuł powinienem użyć lub tym podobne. Z góry dzięki :)

    0
  • #5 04 Lut 2018 22:42
    lanzul
    Poziom 26  

    Rozważania "na sucho" ... mało kto je lubi ... łatwo się pomylić i narobić "głupot", przydałby się jakikolwiek załącznik pod te wzory ... ale spróbujmy ...

    Kod nr 1:

    1. "zakres As Variant, rate As Variant"
    Co to jest 'zakres' i 'rate' ? Jeśli są to zakresy kmórek, to po co 'Variant' a nie 'Range' ?
    Nie jesteś pewien z czym masz do czynienia ?
    Przecież parę linijek dalej stosujesz przypisania obiektowe dla tych zmiennych -'zakres.Cells(i, j)'

    2. "Dim i, j As Integer" - "i" jako 'Variant',
    a z kodu wynika że spokojnie można zmniejszyć rangę tegoż "i", nawet może do 'Integer', jeśli nie operujesz na całych arkuszach, bo inaczej to 'Long' ... ale lepiej będzie 'Long'.

    3. W pętli 'For...Next' zmuszasz cały czas biednego kompusia do wyliczania 'zakres.Rows.Count' oraz 'zakres.Columns.Count' z każdym obrotem tejże pętli, po co ?
    Zmniejszasz szybkość/efektywność działania funkcji.
    Zadeklaruj zmienne na wiersze i kolumny przed pętlą, niech już będą "znane" kodowi zawczasu.

    4. Traktujesz nazwę funkcji jak zmienną - 'Totalcost = Totalcost + ((zakres.Cells(i, j) +...'
    Może i nie ma w tym wielkiej "herezji", ale według "wszystkich zaleceń" istotą funkcji jest to, że występuje w niej polecenie przypisujące do nazwy funkcji pewną wartość, stającą się wynikiem funkcji.
    Wartość ta zwracana jest do nazwy funkcji, a jej wyliczeniem zajmuje(-ą) się jakieś polecenie (-a) wewnątrz "ciałka funkcyjnego" ... :) ... a nie sama nazwa funkcji.

    5. Po pierwszym 'For...Next' umieszczasz 'j = 1'
    Po co, skoro parę linijek później znowu przypisujesz temuż 'j' ponownie tę samą wartość wyjściową - 'For j = 1 To ...' -, a wcześniej nie ulegała ona zmianie ?
    W wyrażeniu:

    Kod: vba
    Zaloguj się, aby zobaczyć kod
    (tutaj już zmieniłem 'Totalcost' na 'totalus')
    wystarczy wpisać zamiast 'j' wartość '1', przecież i tak to stosujesz - zapis 'rate.Cells(i, 1)'.
    W ponownym obrocie pętli 'j' i tak zresetuje się do '1'

    6. Pętla wewnętrzna: 'For j = 1 To zakres.Columns.Count - 2'
    A co będzie jeśli zdarzy się zakres jedno- lub dwu-kolumnowy ?
    Pętla w ogóle się nie wykona ... a wtedy co, może nic, bo może w ogóle takie coś nie zaistnieje, a może coś jednak ? Trudno orzec, sam musisz ocenić.

    7. Zapis 'zakres.Cells(i, j)' stosujesz nagminnie i konsekwentnie, więc rozumiesz jego znaczenie, nie ma się tu nad czym rozwodzić.






    Kod nr 2:

    1. Pętle
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    Piszesz, że wykonujesz dla kolumn: 1, 7 ... ale:
    Przy 'j=1' wyliczenia dotyczą kolumn, rzeczywiście, 1 i 7, natomiast jeśli zdarzy się drugi obrót pętli (wynikający z 'zakres.Columns.Count'), czyli 'j=8' już kolumn: 8 i 14 i suma niepotrzebnie się zwiększy ... zdarzy się coś takiego, czy nie ?
    Może wystarczy tylko jedna ?
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    O tym, których kolumn to będzie dotyczyć i tak będzie decydować zmienna 'zakres'. Od niej dopiero będą odliczane kolumny: 1 i 7 na prawo.


    Kod 3:

    Wypróbuj ten poniżej, wywołując funkcję z podprocedury 'Sub' klawiszem 'F8', np.:
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    i obserwuj jak przemieszcza się zaznaczenie komórek.
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    No i na koniec wywodu ... nie zawsze musisz używać tylu pętli, czy pętli w ogóle - przy dających się "ogarnąć" zakresach wystraczy coś takiego:
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    Dla układu danych jak na obrazku.
    VBA Excel - formuła zaczyna działać po zmianie wartości w komórkachFunkcj..jpg Download (16.13 kB)

    0
  • #6 04 Lut 2018 23:12
    lanzul
    Poziom 26  

    Zapis napisał:

    1. ... makro które zmieni nazwę arkusza na występujące w pewnej komórce.
    2. ... następnie ... zapisało ten arkusz osobno pod nazwą tego arkusza.
    3. ... + jeżeli w folderze występuje plik o takiej samej nazwie wyłącza cały program bez żadnego zapisu ...

    1.
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    2-3.
    Kod: vba
    Zaloguj się, aby zobaczyć kod

    0