Penggunaan Array dan Statement FOR … NEXT Untuk Efisiensi Scripting VBA

lpmp

Artikel ini merupakan saran untuk peserta lomba multimedia LPMP Jateng 2014, penulis menjumpai penulisan script yang sama digunakan berulang kali dalam satu prosedur program.  Dalam hal ini, penulis menyarankan untuk menggunakan statement FOR … NEXT (VBA) untuk menyingkat script yang digunakan. Sebagai contoh , perhatikan gambar (slide 2) berikut ini

gbr_artikel

Obyek-obyek TextBox pada slide 2 adalah Slide2.TextBox1, Slide2.TextBox2, Slide2.TextBox3 dan Slide2.TextBox4.

Shapes untuk simbol benar memiliki nama : benar1, benar2, benar3 dan benar4

Shapes untuk simbol salah memiliki nama : salah1, salah2, salah3 dan salah4

Dan kunci jawaban soal adalah “Buenos Aires”, “Havana”, “Lima” dan “Roma”

 

Script (Prosedur) untuk tombol “CLEAR”  (tanpa statement FOR … NEXT) :

Public Sub hapus()

Slide2.TextBox1.Text = “”

Slide2.TextBox2.Text = “”

Slide2.TextBox3.Text = “”

Slide2.TextBox4.Text = “”

ActivePresentation.Slides(2).Shapes(“benar1″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah1″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“benar2″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah2″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“benar3″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah3″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“benar4″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah4″).Visible = msoFalse

End Sub

 

Script (Prosedur) untuk tombol “PERIKSA” (tanpa statement FOR … NEXT) :

Public Sub Periksa()

‘soal no.1

If Slide2.TextBox1.Text = “Buenos Aires” Then

ActivePresentation.Slides(2).Shapes(“benar1″).Visible = msoTrue

ActivePresentation.Slides(2).Shapes(“salah1″).Visible = msoFalse

Else

ActivePresentation.Slides(2).Shapes(“benar1″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah1″).Visible = msoTrue

End If

‘soal no.2

If Slide2.TextBox2.Text = “Havana” Then

ActivePresentation.Slides(2).Shapes(“benar2″).Visible = msoTrue

ActivePresentation.Slides(2).Shapes(“salah2″).Visible = msoFalse

Else

ActivePresentation.Slides(2).Shapes(“benar2″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah2″).Visible = msoTrue

End If

‘soal no.3

If Slide2.TextBox3.Text = “Lima” Then

ActivePresentation.Slides(2).Shapes(“benar3″).Visible = msoTrue

ActivePresentation.Slides(2).Shapes(“salah3″).Visible = msoFalse

Else

ActivePresentation.Slides(2).Shapes(“benar3″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah3″).Visible = msoTrue

End If

‘soal no.4

If Slide2.TextBox4.Text = “Roma” Then

ActivePresentation.Slides(2).Shapes(“benar4″).Visible = msoTrue

ActivePresentation.Slides(2).Shapes(“salah4″).Visible = msoFalse

Else

ActivePresentation.Slides(2).Shapes(“benar4″).Visible = msoFalse

ActivePresentation.Slides(2).Shapes(“salah4″).Visible = msoTrue

End If

End Sub

Bandingkan penulisan script di atas jika kita menggunakan statement FOR … NEXT, perhatikan gambar (Slide 3) berikut :

gbr_artikel

Obyek-obyek TextBox pada slide 3 adalah Slide3.TextBox1, Slide3.TextBox2, Slide3.TextBox3 dan Slide3.TextBox4.

Shapes untuk simbol benar memiliki nama : benar1, benar2, benar3 dan benar4

Shapes untuk simbol salah memiliki nama : salah1, salah2, salah3 dan salah4

Dan kunci jawaban soal adalah “Buenos Aires”, “Havana”, “Lima” dan “Roma”

Prosedur untuk obyek-obyek yang kita simpan dengan array:

Option Base 1

Public isi As Variant

Public kunci As Variant

Public simbolCek_B As Variant

Public simbolCek_S As Variant

Public Sub kumpulan()

isi = Array(Slide3.TextBox1, Slide3.TextBox2, Slide3.TextBox3, Slide3.TextBox4)

kunci = Array(“Buenos Aires”, “Havana”, “Lima”, “Roma”)

simbolCek_B = Array(ActivePresentation.Slides(3).Shapes(“benar1″), _

ActivePresentation.Slides(3).Shapes(“benar2″), _

ActivePresentation.Slides(3).Shapes(“benar3″), _

ActivePresentation.Slides(3).Shapes(“benar4″))

simbolCek_S = Array(ActivePresentation.Slides(3).Shapes(“salah1″), _

ActivePresentation.Slides(3).Shapes(“salah2″), _

ActivePresentation.Slides(3).Shapes(“salah3″), _

ActivePresentation.Slides(3).Shapes(“salah4″))

End Sub

 

Script (Prosedur) untuk tombol “CLEAR” (dengan statement FOR … NEXT) :

Public Sub hapus_m()

Call kumpulan

For i = 1 To 4

isi(i).Text = “”

simbolCek_B(i).Visible = msoFalse

simbolCek_S(i).Visible = msoFalse

Next

End Sub

 

Script (Prosedur)  untuk tombol “PERIKSA” (dengan statement FOR … NEXT) :

Public Sub Periksa_m()

Call kumpulan

For i = 1 To 4

If isi(i).Text = kunci(i) Then

simbolCek_B(i).Visible = msoTrue

simbolCek_S(i).Visible = msoFalse

Else

simbolCek_B(i).Visible = msoFalse

simbolCek_S(i).Visible = msoTrue

End If

Next

End Sub

Flip Your Classroom: Reach Every Student in Every Class Every Day

fliptheClassroom

Flip Your Classroom: Reach Every Student in Every Class Every Day by Jonathan Bergmann, Aaron Sams
English | 2012 | ISBN: 1564843157 | 100 pages | PDF | 2 MB

It started with a simple observation: students need their teachers present to answer questions or to provide help if they get stuck on an assignment; they don’t need their teachers present to listen to a lecture or review content. From there, Jonathan Bergmann and Aaron Sams began the flipped classroom-students watched recorded lectures for homework and completed their assignments, labs, and tests in class with their teacher available.
What Bergmann and Sams found was that their students demonstrated a deeper understanding of the material than ever before. This is the authors story, and they’re confident it can be yours too. Learn what a flipped classroom is and why it works and get the information you need to flip a classroom.

You’ll also learn the flipped mastery model, where students learn at their own pace-furthering opportunities for personalized education. This simple concept is easily replicable in any classroom, doesn’t cost much to implement, and helps foster self-directed learning. Once you flip, you wont want to go back!

The International Society for Technology in Education (ISTE) is the trusted source for professional development, knowledge generation, advocacy and leadership for innovation. ISTE is the premier membership association for educators and education leaders engaged in improving teaching and learning by advancing the effective use of technology in PK-12 and teacher education. Home of the National Educational Technology Standards (NETS), the Center for Applied Research in Educational Technology (CARET), and ISTE’s annual conference (formerly known as the National Educational Computing Conference, or NECC), ISTE represents more than 100,000 professionals worldwide. We support our members with information, networking opportunities, and guidance as they face the challenge of transforming education.

Some of the areas in which we publish are:
-Web. 2.0 in the classroom-RSS, podcasts, and more
-National Educational Technology Standards (NETS)
-Professional development for educators and administrators
-Integrating technology into the classroom and curriculum
-Safe practices for the Internet and technology
-Educational technology for parents

download  dari  Keep2Share

Membuat “Splash Screen” pada Excel dengan VBA

Bagaimana membuat “Splash Screen” sebagai intro  ketika file excel kita buka (klik).  Berikut ini script yang saya peroleh dari buku excel 2013 power programming with VBA – John WalkenBach.

splahScreen

Script pada UserForm

Option Explicit

Const GWL_STYLE = -16
Const WS_CAPTION = &HC00000
Private Declare PtrSafe Function GetWindowLong Lib “user32″ Alias “GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowLong Lib “user32″ Alias “SetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare PtrSafe Function DrawMenuBar Lib “user32″ (ByVal hWnd As Long) As Long
Private Declare PtrSafe Function FindWindowA Lib “user32″ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

‘Script untuk menghilangkan bingkai window pada UserForm

Private Sub UserForm_Initialize()
Dim lngWindow As Long, lFrmHdl As Long
lFrmHdl = FindWindowA(vbNullString, Me.Caption) ‘ The UserForm must have a caption
lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
lngWindow = lngWindow And (Not WS_CAPTION)
Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
Call DrawMenuBar(lFrmHdl)
End Sub

‘Script untuk Splash Screen

Private Sub UserForm_Activate()
Application.OnTime Now + TimeValue(“00:00:05″), “KillTheForm”
End Sub

‘Prosedur untuk tombol test splash screen dan  “KillTheForm”

Sub TestSplashScreen()
With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) – (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height)
.Show
End With
End Sub

Private Sub KillTheForm()
Unload UserForm1
End Sub

download file contoh : splash Screen 

Membuat Quiz Pilihan Ganda dengan SpinButton

 

Biasanya untuk membuat soal pilihan ganda dengan Powerpoint, kita menyediakan tombol A, B, C, dan D sebagai pilihan jawaban. Kali ini kita menggunakan TextBox dan SpinButton  untuk memilih jawaban.

QuizPG

Script pada Slide 2 :

Private Sub SpinButton1_Change()
Dim pilihJawab As Variant
pilihJawab = Array(“…”, “Jakarta”, “Kualalumpur”, “Manila”, “Hanoi”)
ActivePresentation.Slides(2).Shapes(“kotak”).TextFrame.TextRange.Text = pilihJawab(Val(SpinButton1))
ActivePresentation.Slides(2).Shapes(“benar”).Visible = msoFalse
ActivePresentation.Slides(2).Shapes(“salah”).Visible = msoFalse
End Sub

Script pada module 1 :

Public Sub tombolPeriksa()
If Slide2.SpinButton1.Value = 2 Then
ActivePresentation.Slides(2).Shapes(“benar”).Visible = msoTrue
ActivePresentation.Slides(2).Shapes(“salah”).Visible = msoFalse
Else
ActivePresentation.Slides(2).Shapes(“benar”).Visible = msoFalse
ActivePresentation.Slides(2).Shapes(“salah”).Visible = msoTrue
End If
End Sub

link download contoh file pptm : Quiz PG

Membuat Soal Quiz “Isian” dengan VBA for PPT

coverDIKLAT

Salah satu materi yang kami berikan dalam diklat “Pemanfaatan Teknologi Informasi dalam Pembelajaran bagi Guru SMP, SMA dan SMK”  Dinas Pendidikan dan Olahraga Kabupaten Karanganyar yang di selenggarakan di Hotel Pondok Sari Tawangmangu yang terbagi menjadi 4 angkatan, dengan jadwal sebagai berikut : Angkatan 1 – tanggal 3 sd 5 November 2014, Angkatan 2 – tanggal 10 sd 12 November 2014, Angkatan 3 – tanggal 17 sd 19 November 2014, dan Angkatan 4 – tanggal 19 sd 21 November 2014 – adalah pemrograman VBA untuk memmbuat powerpoint lebih Interaktif, berikut ini adalah contoh penggunaan VBA untuk membuat soal quiz isian.

Tutorial 1 :

gbr1

script VBA Tutorial 1 :
Public Sub tombolClear()
ActivePresentation.Slides(2).Shapes(“cek”).TextFrame.TextRange.Text = “”
Slide2.TextBox1.Text = “”
End Sub

Public Sub tombolPeriksa()

If Slide2.TextBox1.Text = “Tokyo” Then
ActivePresentation.Slides(2).Shapes(“cek”).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(2).Shapes(“cek”).TextFrame.TextRange.Text = “SALAH”
End If

End Sub

Tutorial 2 :

gbr2script VBA Tutorial 2 :
Public Sub tombolClear2()
ActivePresentation.Slides(3).Shapes(“cek1″).TextFrame.TextRange.Text = “”
ActivePresentation.Slides(3).Shapes(“cek2″).TextFrame.TextRange.Text = “”
ActivePresentation.Slides(3).Shapes(“cek3″).TextFrame.TextRange.Text = “”
ActivePresentation.Slides(3).Shapes(“cek4″).TextFrame.TextRange.Text = “”
ActivePresentation.Slides(3).Shapes(“cek5″).TextFrame.TextRange.Text = “”
Slide3.TextBox1.Text = “”
Slide3.TextBox2.Text = “”
Slide3.TextBox3.Text = “”
Slide3.TextBox4.Text = “”
Slide3.TextBox5.Text = “”
End Sub

Public Sub tombolPeriksa2()
‘periksa soal no 1
If Slide3.TextBox1.Text = “Tokyo” Then
ActivePresentation.Slides(3).Shapes(“cek1″).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(3).Shapes(“cek1″).TextFrame.TextRange.Text = “SALAH”
End If
‘periksa soal no 2
If Slide3.TextBox2.Text = “Teheran” Then
ActivePresentation.Slides(3).Shapes(“cek2″).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(3).Shapes(“cek2″).TextFrame.TextRange.Text = “SALAH”
End If
‘periksa soal no 3
If Slide3.TextBox3.Text = “Moskow” Then
ActivePresentation.Slides(3).Shapes(“cek3″).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(3).Shapes(“cek3″).TextFrame.TextRange.Text = “SALAH”
End If
‘periksa soal no 4
If Slide3.TextBox4.Text = “London” Then
ActivePresentation.Slides(3).Shapes(“cek4″).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(3).Shapes(“cek4″).TextFrame.TextRange.Text = “SALAH”
End If
‘periksa soal no 5
If Slide3.TextBox5.Text = “Paris” Then
ActivePresentation.Slides(3).Shapes(“cek5″).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(3).Shapes(“cek5″).TextFrame.TextRange.Text = “SALAH”
End If
End Sub

Tutorial 3 :

gbr3Script VBA Tutorial 3 :

Public cekJawab As Variant
Public isiJawab As Variant
Public kunciJawab As Variant

Public Sub deklarasiArray()
cekJawab = Array(“cek1″, “cek2″, “cek3″, “cek4″, “cek5″)
isiJawab = Array(Slide4.TextBox1, Slide4.TextBox2, Slide4.TextBox3, _
Slide4.TextBox4, Slide4.TextBox5)
kunciJawab = Array(“Tokyo”, “Teheran”, “Moskow”, “London”, “Paris”)
End Sub

Public Sub tombolClear3()
Call deklarasiArray
For i = 0 To 4
ActivePresentation.Slides(4).Shapes(cekJawab(i)).TextFrame.TextRange.Text = “”
isiJawab(i).Text = “”
Next
End Sub

Public Sub tombolPeriksa3()
Call deklarasiArray
For i = 0 To 4
If isiJawab(i).Text = kunciJawab(i) Then
ActivePresentation.Slides(4).Shapes(cekJawab(i)).TextFrame.TextRange.Text = “BENAR”
Else
ActivePresentation.Slides(4).Shapes(cekJawab(i)).TextFrame.TextRange.Text = “SALAH”
End If
Next
End Sub

Tutorial 4 :

gbr4

Script VBA Tutorial 4 :

Public cekJawab As Variant
Public isiJawab As Variant
Public kunciJawab As Variant

Public Sub deklarasiArray()
cekJawab = Array(“benar1″, “salah1″, “benar2″, “salah2″, “benar3″, “salah3″, _
“benar4″, “salah4″, “benar5″, “salah5″)
isiJawab = Array(Slide5.TextBox1, Slide5.TextBox2, Slide5.TextBox3, _
Slide5.TextBox4, Slide5.TextBox5)
kunciJawab = Array(“Tokyo”, “Teheran”, “Moskow”, “London”, “Paris”)
End Sub

Public Sub tombolClear4()
Call deklarasiArray
For i = 0 To 4
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i)).Visible = msoFalse
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i + 1)).Visible = msoFalse
isiJawab(i).Text = “”
Next
End Sub

Public Sub tombolPeriksa4()
Call deklarasiArray
For i = 0 To 4
If isiJawab(i).Text = kunciJawab(i) Then
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i)).Visible = msoTrue
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i + 1)).Visible = msoFalse
Else
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i)).Visible = msoFalse
ActivePresentation.Slides(5).Shapes(cekJawab(2 * i + 1)).Visible = msoTrue
End If
Next
End Sub

Tutorial 5 :

gbr5

Script VBA Tutorial 5 :

Public cekJawab As Variant
Public isiJawab As Variant
Public kunciJawab As Variant
Public nilai As Variant
Public nilaiTotal As Integer

Public Sub deklarasiArray()
cekJawab = Array(“benar1″, “salah1″, “benar2″, “salah2″, “benar3″, “salah3″, _
“benar4″, “salah4″, “benar5″, “salah5″)
isiJawab = Array(Slide6.TextBox1, Slide6.TextBox2, Slide6.TextBox3, _
Slide6.TextBox4, Slide6.TextBox5)
kunciJawab = Array(“Tokyo”, “Teheran”, “Moskow”, “London”, “Paris”)
nilai = Array(“0″, “0”, “0”, “0”, “0”)
End Sub

Public Sub tombolClear5()
Call deklarasiArray
For i = 0 To 4
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i)).Visible = msoFalse
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i + 1)).Visible = msoFalse
ActivePresentation.Slides(6).Shapes(“nilaiAnda”).TextFrame.TextRange.Text = “0”
isiJawab(i).Text = “”
Next
End Sub

Public Sub tombolPeriksa5()
Call deklarasiArray
nilaiTotal = 0
For i = 0 To 4
If isiJawab(i).Text = kunciJawab(i) Then
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i)).Visible = msoTrue
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i + 1)).Visible = msoFalse
nilai(i) = 20
Else
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i)).Visible = msoFalse
ActivePresentation.Slides(6).Shapes(cekJawab(2 * i + 1)).Visible = msoTrue
nilai(i) = 0
End If
nilaiTotal = nilaiTotal + nilai(i)
Next
ActivePresentation.Slides(6).Shapes(“nilaiAnda”).TextFrame.TextRange.Text = nilaiTotal
End Sub

Link Download : https://drive.google.com/file/d/0B_eVGY1-CRo3b1ZjQTRiREpVMTA/view?usp=sharing

 

 

Membuat Visualisasi Perkalian 20 x 20 dengan PPT VBA

perkalianVisual

Script VBA :

Private Sub ScrollBar1_Change()

For i = 1 To 20
For j = 1 To 20
If Val(ScrollBar1) > i – 1 And Val(ScrollBar2) > j – 1 Then
ActivePresentation.Slides(2).Shapes(20 * (i – 1) + j).Visible = msoTrue
Else
ActivePresentation.Slides(2).Shapes(20 * (i – 1) + j).Visible = msoFalse
End If
Next
Next

ActivePresentation.Slides(2).Shapes(“bilangan 1″).TextFrame.TextRange.Text = Val(ScrollBar1)
ActivePresentation.Slides(2).Shapes(“bilangan 2″).TextFrame.TextRange.Text = Val(ScrollBar2)
ActivePresentation.Slides(2).Shapes(“hasil”).TextFrame.TextRange.Text = Val(ScrollBar1) * Val(ScrollBar2)

End Sub

Private Sub ScrollBar2_Change()
ScrollBar1_Change
End Sub

 

download file : Visualisasi Perkalian 20 x 20 pptm

Why Learn Scratch

 

Nine Learning Skills in three Areas

“The report Learning for the 21st Century identifies nine types of learning skills,

divided into three key areas. This handout highlights the ways Scratch supports

the development of these 21st Century learning skills.”

Area-1: Information and Communication Skills

1. Information and Media Literacy Skills

“By working on Scratch projects, students learn to select, create, and manage multiple forms of media, including text, images, animation, and audio recordings. As students gain experience creating with media, they become more perceptive and critical in analyzing the media they see in the world around them.”

2. Communication Skills

“Effective communication in today’s world requires more than the ability to read and write text. Scratch engages young people in choosing, manipulating, and integrating a variety of media in order to express themselves creatively and persuasively.”

Area-2: Thinking and Problem Solving Skills

3. Critical Thinking and Systems Thinking

“As they learn to program in Scratch, young people become engaged in critical reasoning and systems thinking. In order to build projects, students need to coordinate the timing and interactions between multiple “sprites” (programmable moving objects). The ability to program interactive input provides students direct experience with sensing, feedback, and other fundamental systems concepts.”

4. Problem Identification, Formulation & Solution

“Scratch supports problem finding and solving in a meaningful design context. Creating a Scratch project requires thinking of an idea, then figuring out how to break the problem into steps and implement them using Scratch programming blocks. Scratch is designed to be “tinkerable”: students can dynamically change pieces of code and immediately see the results (e.g., doubling a number to see how it changes a graphic effect). Throughout the design process, students engage in experimenting and iterative problem-solving.”

5. Creativity and Intellectual Curiosity

“Scratch encourages creative thinking, an increasingly important skill in today’s rapidly changing world. Scratch involves young people in seeking innovative solutions to unexpected problems—not just learning how to solve a predefined problem, but being prepared to come up with new solutions as new challenges arise.”

Area-3: Interpersonal & Self-Directional Skills

6. Interpersonal and Collaborative Skills

“Because Scratch programs are built of graphical blocks, the programming code is more readable and shareable than other programming languages. The visual objects and modular code supports collaboration, enabling students to work together on projects and exchange objects and code.”

7. Self-Direction

“Taking an idea and figuring out how to program it in Scratch requires persistence and practice. When young people work on project ideas they find personally meaningful, their ideas provide internal motivation for overcoming challenges and frustrations encountered in the design and problem-solving process.”

8. Accountability and Adaptability

“When students create Scratch projects, they have an audience in mind, and need to think about how other people will react and respond to their projects. Since Scratch projects are easy to change and revise, students can modify their projects based on feedback from others.”

9. Social Responsibility

“Because Scratch programs are shareable, students can use Scratch to provoke discussion of important issues with other members of their immediate learning environment, as well as with the wider international Scratch community.”

Sumber : http://learnscratch.org/resources/why-learn-scratch

 

 

 

 

When you cease to make a contribution, you begin to die

Follow

Get every new post delivered to your Inbox.

Join 4,935 other followers