Ada beberapa cara untuk menginput
nilai hasil evaluasi pembelajaran salah satunya memanfaatkan visual basic
(Vba) yang terdapat pada microsoft excel. Ketika kita ingin membuat
aplikasi imputan nilai dengan membuat tombol (shape) input data otomatis.
Berikut ini adalah scrip vba untuk membuat button input nilai pad microsoft
excel.
Sub insertData()
Dim jumlahData As Integer
Dim nomor1, nis1, nisn1, nama1, sex1, spirt1, sos1, pnr1, pnk1, pp1, knr1,
knk1, kp1 As String
Sheets("Sheet2").Select
nomor1 = Range("A12").Text
nis1 = Range("B12").Text
nisn1 = Range("C12").Text
nama1 = Range("D12").Text
sex1 = Range("E12").Text
spirt1 = Range("F12").Text
sos1 = Range("G12").Text
pnr1 = Range("H12").Text
pnk1 = Range("I12").Text
pp1 = Range("J12").Text
knr1 = Range("K12").Text
knk1 = Range("L12").Text
kp1 = Range("M12").Text 'batas ambil data row 1
Dim nomor2, nis2, nisn2, nama2, sex2, spirt2, sos2, pnr2, pnk2, pp2, knr2,
knk2, kp2 As String
nomor2 = Range("A13").Text
nis2 = Range("B13").Text
nisn2 = Range("C13").Text
nama2 = Range("D13").Text
sex2 = Range("E13").Text
spirt2 = Range("F13").Text
sos2 = Range("G13").Text
pnr2 = Range("H13").Text
pnk2 = Range("I13").Text
pp2 = Range("J13").Text
knr2 = Range("K13").Text
knk2 = Range("L13").Text
kp2 = Range("M13").Text 'batas ambil data row 2
Dim nomor3, nis3, nisn3, nama3, sex3, spirt3, sos3, pnr3, pnk3, pp3, knr3,
knk3, kp3 As String
nomor3 = Range("A14").Text
nis3 = Range("B14").Text
nisn3 = Range("C14").Text
nama3 = Range("D14").Text
sex3 = Range("E14").Text
spirt3 = Range("F14").Text
sos3 = Range("G14").Text
pnr3 = Range("H14").Text
pnk3 = Range("I14").Text
pp3 = Range("J14").Text
knr3 = Range("K14").Text
knk3 = Range("L14").Text
kp3 = Range("M14").Text 'batas ambil data row 3
Dim nomor4, nis4, nisn4, nama4, sex4, spirt4, sos4, pnr4, pnk4, pp4, knr4,
knk4, kp4 As String
nomor4 = Range("A15").Text
nis4 = Range("B15").Text
nisn4 = Range("C15").Text
nama4 = Range("D15").Text
sex4 = Range("E15").Text
spirt4 = Range("F15").Text
sos4 = Range("G15").Text
pnr4 = Range("H15").Text
pnk4 = Range("I15").Text
pp4 = Range("J15").Text
knr4 = Range("K15").Text
knk4 = Range("L15").Text
kp4 = Range("M15").Text 'batas ambil data row 4
Dim nomor5, nis5, nisn5, nama5, sex5, spirt5, sos5, pnr5, pnk5, pp5, knr5,
knk5, kp5 As String
nomor5 = Range("A16").Text
nis5 = Range("B16").Text
nisn5 = Range("C16").Text
nama5 = Range("D16").Text
sex5 = Range("E16").Text
spirt5 = Range("F16").Text
sos5 = Range("G16").Text
pnr5 = Range("H16").Text
pnk5 = Range("I16").Text
pp5 = Range("J16").Text
knr5 = Range("K16").Text
knk5 = Range("L16").Text
kp5 = Range("M16").Text 'batas ambil data row 5
Dim nomor6, nis6, nisn6, nama6, sex6, spirt6, sos6, pnr6, pnk6, pp6, knr6,
knk6, kp6 As String
nomor6 = Range("A17").Text
nis6 = Range("B17").Text
nisn6 = Range("C17").Text
nama6 = Range("D17").Text
sex6 = Range("E17").Text
spirt6 = Range("F17").Text
sos6 = Range("G17").Text
pnr6 = Range("H17").Text
pnk6 = Range("I17").Text
pp6 = Range("J17").Text
knr6 = Range("K17").Text
knk6 = Range("L17").Text
kp6 = Range("M17").Text 'batas ambil data row 6
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor1
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis1
ActiveCell.FormulaR1C1 = nis1
Range("C" & jumlahData + 3).Select 'input nisn1
ActiveCell.FormulaR1C1 = nisn1
Range("D" & jumlahData + 3).Select 'input nama1
ActiveCell.FormulaR1C1 = nama1
Range("E" & jumlahData + 3).Select 'input sex1
ActiveCell.FormulaR1C1 = sex1
Range("F" & jumlahData + 3).Select 'input spirt1
ActiveCell.FormulaR1C1 = spirt1
Range("G" & jumlahData + 3).Select 'input sos1
ActiveCell.FormulaR1C1 = sos1
Range("H" & jumlahData + 3).Select 'input pnr1
ActiveCell.FormulaR1C1 = pnr1
Range("I" & jumlahData + 3).Select 'input pnk1
ActiveCell.FormulaR1C1 = pnk1
Range("J" & jumlahData + 3).Select 'input pp1
ActiveCell.FormulaR1C1 = pp1
Range("K" & jumlahData + 3).Select 'input knr1
ActiveCell.FormulaR1C1 = knr1
Range("L" & jumlahData + 3).Select 'input knk1
ActiveCell.FormulaR1C1 = knk1
Range("M" & jumlahData + 3).Select 'input kp1
ActiveCell.FormulaR1C1 = kp1
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor2
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis2
ActiveCell.FormulaR1C1 = nis2
Range("C" & jumlahData + 3).Select 'input nisn2
ActiveCell.FormulaR1C1 = nisn2
Range("D" & jumlahData + 3).Select 'input nama2
ActiveCell.FormulaR1C1 = nama2
Range("E" & jumlahData + 3).Select 'input sex2
ActiveCell.FormulaR1C1 = sex2
Range("F" & jumlahData + 3).Select 'input spirt2
ActiveCell.FormulaR1C1 = spirt2
Range("G" & jumlahData + 3).Select 'input sos2
ActiveCell.FormulaR1C1 = sos2
Range("H" & jumlahData + 3).Select 'input pnr2
ActiveCell.FormulaR1C1 = pnr2
Range("I" & jumlahData + 3).Select 'input pnk2
ActiveCell.FormulaR1C1 = pnk2
Range("J" & jumlahData + 3).Select 'input pp2
ActiveCell.FormulaR1C1 = pp2
Range("K" & jumlahData + 3).Select 'input knr2
ActiveCell.FormulaR1C1 = knr2
Range("L" & jumlahData + 3).Select 'input knk2
ActiveCell.FormulaR1C1 = knk2
Range("M" & jumlahData + 3).Select 'input kp2
ActiveCell.FormulaR1C1 = kp2
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor3
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis3
ActiveCell.FormulaR1C1 = nis3
Range("C" & jumlahData + 3).Select 'input nisn3
ActiveCell.FormulaR1C1 = nisn3
Range("D" & jumlahData + 3).Select 'input nama3
ActiveCell.FormulaR1C1 = nama3
Range("E" & jumlahData + 3).Select 'input sex3
ActiveCell.FormulaR1C1 = sex3
Range("F" & jumlahData + 3).Select 'input spirt3
ActiveCell.FormulaR1C1 = spirt3
Range("G" & jumlahData + 3).Select 'input sos3
ActiveCell.FormulaR1C1 = sos3
Range("H" & jumlahData + 3).Select 'input pnr3
ActiveCell.FormulaR1C1 = pnr2
Range("I" & jumlahData + 3).Select 'input pnk2
ActiveCell.FormulaR1C1 = pnk3
Range("J" & jumlahData + 3).Select 'input pp3
ActiveCell.FormulaR1C1 = pp3
Range("K" & jumlahData + 3).Select 'input knr3
ActiveCell.FormulaR1C1 = knr3
Range("L" & jumlahData + 3).Select 'input knk3
ActiveCell.FormulaR1C1 = knk3
Range("M" & jumlahData + 3).Select 'input kp3
ActiveCell.FormulaR1C1 = kp3
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor4
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis4
ActiveCell.FormulaR1C1 = nis4
Range("C" & jumlahData + 3).Select 'input nisn4
ActiveCell.FormulaR1C1 = nisn4
Range("D" & jumlahData + 3).Select 'input nama4
ActiveCell.FormulaR1C1 = nama4
Range("E" & jumlahData + 3).Select 'input sex4
ActiveCell.FormulaR1C1 = sex4
Range("F" & jumlahData + 3).Select 'input spirt4
ActiveCell.FormulaR1C1 = spirt4
Range("G" & jumlahData + 3).Select 'input sos4
ActiveCell.FormulaR1C1 = sos4
Range("H" & jumlahData + 3).Select 'input pnr4
ActiveCell.FormulaR1C1 = pnr4
Range("I" & jumlahData + 3).Select 'input pnk4
ActiveCell.FormulaR1C1 = pnk4
Range("J" & jumlahData + 3).Select 'input pp4
ActiveCell.FormulaR1C1 = pp4
Range("K" & jumlahData + 3).Select 'input knr4
ActiveCell.FormulaR1C1 = knr4
Range("L" & jumlahData + 3).Select 'input knk4
ActiveCell.FormulaR1C1 = knk4
Range("M" & jumlahData + 3).Select 'input kp4
ActiveCell.FormulaR1C1 = kp4
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor5
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis5
ActiveCell.FormulaR1C1 = nis5
Range("C" & jumlahData + 3).Select 'input nisn5
ActiveCell.FormulaR1C1 = nisn5
Range("D" & jumlahData + 3).Select 'input nama5
ActiveCell.FormulaR1C1 = nama5
Range("E" & jumlahData + 3).Select 'input sex5
ActiveCell.FormulaR1C1 = sex5
Range("F" & jumlahData + 3).Select 'input spirt5
ActiveCell.FormulaR1C1 = spirt5
Range("G" & jumlahData + 3).Select 'input sos5
ActiveCell.FormulaR1C1 = sos5
Range("H" & jumlahData + 3).Select 'input pnr5
ActiveCell.FormulaR1C1 = pnr5
Range("I" & jumlahData + 3).Select 'input pnk5
ActiveCell.FormulaR1C1 = pnk5
Range("J" & jumlahData + 3).Select 'input pp5
ActiveCell.FormulaR1C1 = pp5
Range("K" & jumlahData + 3).Select 'input knr5
ActiveCell.FormulaR1C1 = knr5
Range("L" & jumlahData + 3).Select 'input knk5
ActiveCell.FormulaR1C1 = knk5
Range("M" & jumlahData + 3).Select 'input kp5
ActiveCell.FormulaR1C1 = kp5
Sheets("Sheet11").Select
jumlahData = Range("N1").Value
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
Selection.Copy
Rows(jumlahData + 2 & ":" & jumlahData +
2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & jumlahData + 3).Select 'input nomor6
ActiveCell.FormulaR1C1 = jumlahData + 1
Range("B" & jumlahData + 3).Select 'input nis6
ActiveCell.FormulaR1C1 = nis6
Range("C" & jumlahData + 3).Select 'input nisn6
ActiveCell.FormulaR1C1 = nisn6
Range("D" & jumlahData + 3).Select 'input nama6
ActiveCell.FormulaR1C1 = nama6
Range("E" & jumlahData + 3).Select 'input sex6
ActiveCell.FormulaR1C1 = sex6
Range("F" & jumlahData + 3).Select 'input spirt6
ActiveCell.FormulaR1C1 = spirt6
Range("G" & jumlahData + 3).Select 'input sos6
ActiveCell.FormulaR1C1 = sos6
Range("H" & jumlahData + 3).Select 'input pnr6
ActiveCell.FormulaR1C1 = pnr6
Range("I" & jumlahData + 3).Select 'input pnk6
ActiveCell.FormulaR1C1 = pnk6
Range("J" & jumlahData + 3).Select 'input pp6
ActiveCell.FormulaR1C1 = pp6
Range("K" & jumlahData + 3).Select 'input knr6
ActiveCell.FormulaR1C1 = knr6
Range("L" & jumlahData + 3).Select 'input knk6
ActiveCell.FormulaR1C1 = knk6
Range("M" & jumlahData + 3).Select 'input kp6
ActiveCell.FormulaR1C1 = kp6
Sheets("Sheet11").Select
'pesan berhasil
MsgBox "Input Data Berhasil !", vbInformation,
"Terimakasih !"
End Sub
Contoh bisa anda gunakan Aplikasi Input Nilai Otomatis microsoft excel vba
