Практика MS Excel

17.1.5. Код формы frm_In

Листинг 17.3 содержит код формы frm_In.

Private Sub cmd_Exit_Click()
    frm_In.Hide
    'Скрывая frm_In мы автоматически
    'переходим к frm_Main
End Sub

Private Sub cmd_Rec_Click()
    'Адрес строки для записи
    Dim num_Address
    'Вычисляем номер строки для записи
    num_Address = ActiveSheet.Range("B1") + _
        ActiveSheet.Range("B2")
    'Записываем номер в первую ячейку строки
    ActiveSheet.Cells(num_Address, 1) = _
        ActiveSheet.Range("B1")
    'Запишем дату во вторую ячейку
    ActiveSheet.Cells(num_Address, 2) = _
        Date
    'В третьей ячейке - тип операции
    ActiveSheet.Cells(num_Address, 3) = _
        cbo_Type.Value
    'В четвертой - сумма
    ActiveSheet.Cells(num_Address, 4) = _
        Val(txt_Sum)
    'В пятой - примечание
    ActiveSheet.Cells(num_Address, 5) = _
        txt_Info
    'Запишем новый номер строки
    ActiveSheet.Range("B1") = _
        ActiveSheet.Range("B1") + 1
    'Сбросим все установки на форме
    Initial
End Sub

Private Sub UserForm_Activate()
    'При активации формы
    'инициализируем элементы управления
    Initial
End Sub

Sub Initial()
    'Инициализация элементов управления
    lbl_Date = Date
    lbl_RecNum = ActiveSheet.Range("B1")
    cbo_Type.Clear
    cbo_Type.AddItem "Доход"
    cbo_Type.AddItem "Расход"
    cbo_Type.Value = "Доход"
    txt_Info = ""
    txt_Sum = ""
End Sub

Рассмотрим код формы frm_Out

17.1.6. Код формы frm_Out

Листинг 17.4 содержит код формы frm_Out. Обратите внимание на пользовательскую процедуру Load_Data(). Мы передаем ей параметр num_Index - номер строки, который должен быть отображен. Работа обработчиков нажатия на кнопки перемещения и обработчика, выполняющегося при выборе даты на календаре сводится к вычислению нужного номера строки и вызову этой процедуры.

Private Sub UserForm_Initialize()
    'Загружаем первую строку
    Load_Data (1)
End Sub

Private Sub cmd_Backward_Click()
    'Предыдущая строка
    If Val(lbl_RecNum) > 1 Then
        Load_Data (Val(lbl_RecNum) - 1)
    End If
End Sub

Private Sub cmd_Exit_Click()
    frm_Out.Hide
End Sub

Private Sub cmd_First_Click()
    'Загружаем первую строку
    Load_Data (1)
End Sub

Private Sub cmd_Forward_Click()
    'Следующая строка
    If Val(lbl_RecNum) < ActiveSheet.Range("B1") Then
        Load_Data (Val(lbl_RecNum) + 1)
    End If
End Sub

Private Sub cmd_Last_Click()
    'Загружаем последнюю строку
    Load_Data (ActiveSheet.Range("B1") - 1)
End Sub

Private Sub cld_First_Click()
    'Просматриваем таблицу,
    'находим первую запись
    'за выбранную дату и выводим эту запись
    For i = 1 To ActiveSheet.Range("B1") - 1
        If ActiveSheet.Cells _
        (i + ActiveSheet.Range("B2"), 2) = _
        cld_First.Value Then
            Load_Data (i)
            Exit For
        End If
    Next i
End Sub

Private Sub cmd_Rec_Click()
    'Адрес строки для записи
    Dim num_Address
    'Вычисляем номер строки для записи
    num_Address = Val(lbl_RecNum + _
        ActiveSheet.Range("B2"))
    'Так как мы разрешили модифицировать
    'лишь сумму и примечание - запишем их
    'в текущую строку
    'Запишем сумму
    ActiveSheet.Cells(num_Address, 4) = _
        Val(txt_Sum)
    'Запишем примечание
    ActiveSheet.Cells(num_Address, 5) = _
        txt_Info
End Sub

Sub Load_Data(num_Index As Integer)
    'Принимает номер строки и выводит
    'Данные из этой строки
    'Адрес строки для чтения
    Dim num_Address
    'Вычисляем номер строки для чтения
    num_Address = num_Index + _
        ActiveSheet.Range("B2")
    'Выводим номер записи
    lbl_RecNum = _
        ActiveSheet.Cells(num_Address, 1)
    'Выводим дату
    lbl_Date = _
        ActiveSheet.Cells(num_Address, 2)
    'Выводим тип операции
    lbl_Type = _
        ActiveSheet.Cells(num_Address, 3)
    'Выводим сумму
    txt_Sum = _
        ActiveSheet.Cells(num_Address, 4)
    'Выводим примечание
    txt_Info = _
        ActiveSheet.Cells(num_Address, 5)
End Sub

Теперь рассмотрим код модуля формы frm_Balance

17.1.7. Код формы frm_Balance

В листинге 17.5. вы можете найти код модуля формы frm_Balance. Здесь мы вычисляем баланс доходов и расходов по всей таблице. Вычисления ведутся в коде обработчика события Activate.

Private Sub cmd_OK_Click()
    frm_Balance.Hide
End Sub

Private Sub UserForm_Activate()
    'Адрес строки
    Dim num_Address
    'Переменная для хранения суммы доходов
    Dim num_Earn
    'Переменная для хранения суммы расходов
    Dim num_Spend
    For i = 1 To ActiveSheet.Range("B1") - 1
        num_Address = i + ActiveSheet.Range("B2")
        'Если в строке хранится значение дохода
        'добавим его в num_Earn
        If ActiveSheet.Cells(num_Address, 3) = "Доход" _
            Then
                num_Earn = num_Earn + _
                    ActiveSheet.Cells(num_Address, 4)
            End If
        'Если в строке хранится значение расхода
        'добавим его в num_Spend
        If ActiveSheet.Cells(num_Address, 3) = "Расход" _
            Then
                num_Spend = num_Spend + _
                    ActiveSheet.Cells(num_Address, 4)
            End If
    Next i
    lbl_Balance = num_Earn - num_Spend
    If num_Earn > num_Spend Then _
        lbl_Msg = "Доходы больше расходов."
    If num_Earn = num_Spend Then _
        lbl_Msg = "Доходы равны расходам."
    If num_Earn < num_Spend Then _
        lbl_Msg = "Доходы меньше расходов."
End Sub

17.2. Задача об обмене значениями

17-02-Обмен значений.xlsm - пример к п. 17.2.

17.2.1. Условие

Произвести обмен значениями двух переменных без использования третьей

17.2.2. Решение

Предположим, что имеются 2 переменные (А и В), содержащие числа. Для обмена значениями этих переменных достаточно произвести следующие действия:

Для решения задачи будем считать, что число A записано в ячейку B2, число В - в ячейку C2. Подпишем соответствующим образом эти ячейки и разместим на рабочем листе кнопку с именем cmd_Change и надписью Обменять А и В (рис. 17.6.)

В листинге 17.6. вы можете найти программный код для решения задачи, размещенный в обработчике события Click для кнопки cmd_Change

'Сохраняем сумму ячеек в B2
    ActiveSheet.Range("B2") = _
        ActiveSheet.Range("B2") + _
        ActiveSheet.Range("C2")
    'Разность сохраняем в С2
    ActiveSheet.Range("C2") = _
        ActiveSheet.Range("B2") - _
        ActiveSheet.Range("C2")
    'И еще раз разность в B2
    ActiveSheet.Range("B2") = _
        ActiveSheet.Range("B2") - _
        ActiveSheet.Range("C2")

17.3. Перевод чисел из одной системы счисления в другую

17-03-Системы счисления.xlsm - пример к п. 17.3.

17.3.1. Условие

Перевести заданное пользователем целое число A из одной системы счисления ( P ) в другую ( Q ). P и Q могут изменяться от 2 до 10.

17.3.2. Решение

MS Excel содержит функции для преобразования чисел между различными системами счисления, однако здесь мы создадим универсальное решение. Вы сможете встроить этот код в свои проекты. При необходимости вы сможете доработать его так, чтобы он смог работать с системами счисления, основание которых превышает 10.

В данном случае наиболее очевидным является перевод введенного числа сначала из системы счисления с основанием P в систему с основанием 10, а потом уже из системы с основанием 10 в систему с основанием Q.

Перевод в десятичную систему счисления осуществляется в два этапа:

Первую цифру числа в системе счисления с основанием Q нужно умножить на Q в нулевой степени. Полученное число прибавить к произведению второй цифры числа, умноженную на Q в 1-й степени и так далее. Например:

Перевод из двоичной системы в десятичную:

1101 =1*2^3+1*2^2+0*2^1+1*2^0=8+4+0+1=13

Перевод из пятеричной системы в десятичную:

1042=1*5^3+0*5^2+4*5^1+2*5^0=125+0+20+2=147

Перевод в систему счисления с основанием Q из системы с основанием 10 осуществляется путем накапливания остатков от деления этого числа на Q с последующим изменением этого числа целочисленным делением его на Q до тех пор, пока переводимое число не станет равным 0.

Для решения этой задачи нам понадобится форма, содержащая следующие элементы управления (табл. 17.6.). У текстовых полей свойство AutoSize установлено в True.

На рис. 17.7. вы можете увидеть форму программы.

В листинге 17.7. вы можете найти код события Click для кнопки cmd_OK.

'Для хранения основания системы P
    Dim num_P
    'Для хранения основания системы Q
    Dim num_Q
    'Для хранения числа в 10-й системе
    Dim num_10
    'Для хранения очередного числа в
    'системе счисления Q
    Dim num_S
    num_P = Val(txt_P)
    num_Q = Val(txt_Q)
    'Переводим введенное число из
    'системы счисления P
    'в десятичную систему
    For i = 1 To Len(txt_A)
        num_10 = num_10 + Val(Mid(txt_A, i, 1)) * _
            num_P ^ (Len(txt_A) - i)
    Next i
    'Переводим число из десятичной системы
    'в систему с основанием Q
    txt_B = ""
    While num_10 <> 0
        'Остаток от деления запишем в str_S
        num_S = num_10 Mod num_Q
        'Запишем очередное число в
        'окно для вывода результата
        txt_B = Mid(Str(num_S), 2, 1) + txt_B
        'Запишем в num_10 результат
        'целочисленного деления
        'num_10 на основание системы
        'счисления Q
        num_10 = num_10 \ num_Q
    Wend

17.4. Выводы

В этой лекции мы рассмотрели несколько практических примеров решения задач для MS Excel.