Informatyka programowanie – Politechnika Świętokrzyska Kielce
str. 1
Visual Basic dla Aplikacji w Excelu
Ć
wiczenie 1-2
spłata pożyczki
I.
Excel - tabela spłat pożyczki
1.
Wprowadzić nazwy kolejnych arkuszy: Tytuł, Tabela, Wykres.
2.
W arkuszu tabela nazwać komórki:
C1 - pożyczka - do wprowadzania kwoty pożyczki,
C2 - procent - do wprowadzania wysokości oprocentowania,
C20 - nazwa - do wprowadzanie nazwiska pożyczkobiorcy.
3.
Zbudować tabelę obliczającą rozkład spłat pożyczki tak jak na rysunku;
wiersz 4 - nagłówki tabeli
komórka B5 formuła =C1
komórka C5 formuła =C1-11*C6
komórka B6 formuła =F5, skopiować do końca tabeli
komórka C6 formuła =ZAOKR($C$1/12;0), skopiować do końca tabeli
komórka D5 formuła =ZAOKR(B5*$C$2/12;2), skopiować do końca tabeli
komórka E5 formuła =C5+D5, skopiować do końca tabeli
komórka F5 formuła =B5-C5, skopiować do końca tabeli
komórki C17,D17,E17 suma wartości w odpowiednich kolumnach tabeli.
4.
Sporządzić wykres umieszczony w oddzielnym arkuszu o nazwie wykres.
Na wykresie przedstawić wielkość raty kapitałowej i odsetek
II. Visual Basic - obsługa tabeli spłat kredytu
1.
Wstawić arkusz modułu (domyślna nazwa moduł1), sprawdzić czy jest wymuszenie deklaracji zmiennych Option Explicit, jeśli nie ma to
wpisać.
2.
Napisać procedurę wprowadzania kwoty pożyczki
Sub obl_pozyczke
treść procedury
End Sub
Informacje pomocnicze:
Komentarz ignorowany podczas wykonywania poprzedzamy znakiem ’
dim - deklaracja zmiennych (string - tekst, single - liczby rzeczywiste, integer - liczby całkowite)
w zadaniu stosowana funkcja InputBox, (pole dialogu)
InputBox(prompt [,title][,default][,xpos][,ypos][,help])
Informatyka programowanie – Politechnika Świętokrzyska Kielce
str. 2
prompt - tekst w polu dialogowym -konieczne, pozostałe nie są konieczne
title - tytuł pola dialogowego
default - tekst w polu edycji przy wywołaniu funkcji
xpos,ypos - położenie pola dialogowego na ekranie
help - odwołanie do pliku pomocy (nie da się tego zrobić bez specjalnych narzędzi)
PRZYKŁAD:
InputBox(prompt:=”wpisz coś”, Title:=”wpisywanie”)
InputBox (”wpisz nazwisko”)
wynikiem funkcji InputBox jest tekst. Aby go zamienić na liczbę (o ile się da) stosujemy funkcję CSng() lub CInt, np. CSng(stawka), gdzie
stawka jest tekstem przedstawiającym liczbę rzeczywistą.
Pułapka błędów - w przypadku błędu program nie zawiesza się tylko omija część procedury
On Error GoTo etykieta
instrukcje opuszczane przy błędzie
etykieta:
Wybór obiektów:
Wybór arkusza - Worksheets(”tabela”).Select, Sheets("tabela").Select
Wybór komórki - Range(”A5”).Select, Range(”pożyczka”).select
Wpisywanie liczb lub tekstu do komórek
Range(”a5”).Value = 27
Worksheets(”tabela”).Range(„”A7”).Formula = ”tekst”
3.
Napisać pozostałe procedury:
oprocentowanie (wprowadzanie wysokości oprocentowania)
nowe_dane (usunięcie starych danych)
nazwa_pozyczkobiorcy (wpisywanie nazwiska pożyczkobiorcy)
tabela (przejście do tabeli)
strona_tytułowa (przejście do strony tytułowej)
wykres (przejście do strony z wykresem)
4.
Umieścić przyciski i powiązać z nimi procedury. Sprawdzać kolejno działanie każdej procedury.
5.
Napisać procedury: auto_open, koniec_pracy. Procedurę koniec_pracy powiązać z przyciskiem. ZAPISAĆ skoroszyt na dysku (nadać nazwę)
Zamknąć skoroszyt wykorzystując przycisk i ponownie otworzyć.
6.
Uzupełnić stronę tytułową, wprowadzić ozdobny tekst KALKULACJA POŻYCZKI (WordArt). Pod utworzony obiekt podpiąć procedurę
umożliwiającą przejście do arkusza Tabela. W arkuszu Wykres dodać przyciski umożliwiające przejście do pozostałych dwóch arkuszy.
7.
Utworzyć procedurę drukowanie nagrywając zapis czynności (zaznaczyć obszar zawierający kalkulację kredytu, wybrać opcję drukowania,
ustalić parametry: drukowanie zaznaczonego fragmentu, orientacja pozioma, podgląd wydruku). Powiązać procedurę z przyciskiem.
III Rozbudowa aplikacji
Rozbudować aplikacje przez dodanie tabeli z systemem spłat kredytu z zachowaniem stałej wysokości spłaty (nie jak poprzednio za stałą ratą
kapitałową).
Wysokość pojedynczej płatności obliczamy ze wzoru;
(
)
(
)
płatność
POŻYCZKA
i 1 i
1 i
1
n
n
=
+
+
−
gdzie; i - stopa procentowa za jeden okres płatności, n - liczba okresów płatności
Option Explicit
'SPŁATA PO
Ż
YCZKI
Sub obl_po
ż
yczke()
'Wprowadzanie kwoty po
ż
yczki
Dim kwota As String
Worksheets("tabela").Select
Range("po
ż
yczka").Select
On Error GoTo koniec
kwota = InputBox("Wpisz kwot
ę
po
ż
yczki")
ActiveCell.Value = CSng(kwota)
koniec:
End Sub
Sub oprocentowanie()
'Wprowadzanie oprocentowania
Dim stawka As String
uzupełnij samodzielnie procedur
ę
pami
ę
taj
ą
c o tym,
ż
e podan
ą
warto
ść
oprocentowania
nale
ż
y podzieli
ć
przez 100
End Sub
Sub nazwa_pozyckobiorcy()
Dim nazwisko As String
Worksheets("tabela").Select
Informatyka programowanie – Politechnika Świętokrzyska Kielce
str. 3
Range("nazwa").Select
'Nast
ę
pna instrukcja demonstruje kontynuacje zapisu w kolejnej linii
nazwisko = InputBox(Prompt:="Wpisz nazwisko i imi
ę
pozyczkobiorcy", _
Title:="Po
ż
yczkobiorca")
If nazwisko <> "" Then ActiveCell.Formula = nazwisko
Range("a1").Select
End Sub
Sub nowe_dane()
'Nowe dane, usuni
ę
cie starych
Range("po
ż
yczka").Value = 0
Range("procent").Value = 0
Range("nazwa").Value = ""
End Sub
Sub tabela()
Przej
ś
cie do arkusz Tabela, nast
ę
pnie do komórki A1
End Sub
Sub strona_tytulowa()
Przej
ś
cie do arkusz Tytuł, nast
ę
pnie do komórki A1
End Sub
Sub wykres()
Przej
ś
cie do arkusza Wykres
End Sub
Sub auto_open()
Worksheets("tabela").Select
nowe_dane
Worksheets("tytuł").Select
Range("a1").Select
End Sub
Sub koniec_pracy()
ActiveWorkbook.Close
End Sub