Simple Search Engine in Ms Excel with VBA [EN – ID] Aplikasi Pencarian Sederhana Di Ms Excel Dengan VBA
ENGLISH
1. Importance of a Good Database
A good database is something that has high significance in an institution whether public or private, for various needs that are usually determined by the nature of the work or services they offer and the type of data they manage. A good database helps interested parties to keep abreast of developments and provides the information needed for various purposes including monitoring, evaluation, and planning.
One example of the usefulness of a database is searching for information on a person (member of an association or employee or other, e.g. fugitive database) based on keywords. This is commonly found when someone wants to access public services such as healthcare. A person who has a membership at a clinic, for example, will have an ID integrated in the clinic's database that contains relevant info such as age, gender, allergies, blood type, and medical history.
2. Databases in Healthcare Development in Indonesia
On a broader scale, for example in Indonesia today, everyone is registered in the national health database using the number of their identity card (Kartu Tanda Penduduk or KTP for short). So their health records can be accessed by any health facility, if at any time someone has to get health services at a different health facility, it is no longer a problem even if they have a special ID at a clinic (certain health facility) because the ID Card number is also recorded there.
3. Simple Forms In Microsoft Excel With VBA
3.1. Preparation (Data Table)
I will create a simple form related to health services using Microsoft Excel and Visual Basic for Application (VBA). The following figure shows the anatomy of the form that I will create.
Explanation:
- Pointer 1: text box to fill in the ID to search for data;
- Pointer 2 : a button to search for patient data based on the ID that has been typed;
- Pointer 3 : the place where the patient info is displayed;
- Pointer 4 : patient photo;
For this purpose, I already have a table in Microsoft Excel. My table contains a list of 10 patients and the related info of each of them. This list can grow longer and the VBA form will adjust accordingly. The table I have can be seen in the image below.
3.2. Creating a UserForm in VBA
- Open the VBA Editor in Ms Excel by pressing [ALT + F11]. You can also access this by pressing the [Developer] menu button and then selecting the [Visual Basic] button on the ribbon in the Code section.
- The VBA window opens. Click [Insert] > [UserForm] to add a UserForm.
- The UserForm window (named UserForm1 by default) appears along with the Controls window. What follows is to add the necessary elements (available in the Controls window) to the UserForm.
According to the anatomy of the planned form as shown in Figure 1 above, there are 4 kinds of elements that will be used in this form, namely: TextBox (box to fill in the ID to be searched); CommandButton (button to execute the search based on the given ID); Label (to display information); and Image Control (to display the photo based on the searched ID). - Let's add the first element, the TextBox.
- (1) Click on the TextBox button in the Controls window;
- (2) Click (or click and drag) inside the UserForm window to place the TextBox.
- (3) Use the Properties window (at the bottom left) to set the TextBox properties such as changing the name (in this case -as seen in the picture- the TextBox name has been changed to txtSearch), specifying the font type and size, alignment (left or right or centre), and so on.
- Add other elements.
The result after all the required elements are added. - Next, add the code containing the commands to search and fill the data based on the given ID. Right-click inside the form's working area and select [View Code].
- Enter the following code:
Private Sub btnSearch_Click()
Dim ws As Worksheet
Dim rng As Range
Dim foundID As Range
Dim fotoPath As String
' Set sheet of the data
Set ws = ThisWorkbook.Sheets("Sheet1")
' Find ID in colomn A
Set rng = ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
Set foundID = rng.Find(What:=txtSearch.Value, LookAt:=xlWhole)
' If ID found, fill the data to TextBox
If Not foundID Is Nothing Then
txtName.Value = foundID.Offset(0, 1).Value
txtGender.Value = foundID.Offset(0, 2).Value
txtDOB.Value = foundID.Offset(0, 3).Value
txtBloodType.Value = foundID.Offset(0, 4).Value
txtAllergy.Value = foundID.Offset(0, 5).Value
' Show photo in Image Control base on photo path
fotoPath = foundID.Offset(0, 6).Value
If Dir(fotoPath) <> "" Then
imgPhoto.Picture = LoadPicture(fotoPath)
Else
imgPhoto.Picture = Nothing
End If
Else
MsgBox "ID not found!", vbExclamation, "Warning"
End If
End Sub
- We need to save the Excel file as an Excel Macro-Enabled Workbook (*.xlsm), so that it can run the Macro code that we embed through Visual Basic for Application (VBA). Click [Ctrl + S] in the VB Editor window, and then select [No] in the dialogue window that appears.
- Select the right file type, and click [Save].
3.3. Running the Form (UserForm1)
- In order for the form (UserForm1) that has just been created to run, we need to add a Macro to it. In the VBA Editor, click [Insert] and then select [Module].
- Enter the following code:
Sub OpenPatientInfo()
UserForm1.Show
End Sub
- Save the form (click [Ctrl + S]).
- In Ms Excel, click [Alt + F8], select the form based on the name written in the Macro code, in this case [OpenPatientInfo]. Click [Run].
- I typed an ID that was already available in the table, 108, and pressed the [Find] button. This is what happened to the UserForm1 form:
Notes: Not actual data. Model photo source: https://www.pexels.com/photo/positive-ethnic-lady-carrying-folder-with-documents-and-standing-in-sulight-6084178/ - If I type an input that is not registered as an ID in the table, then the UserForm1 will give the following result:
3.4. Shortcut Button to Open Form
We can create a button inside the Excel file that will simplify the way we access the form, so we don't need to press [Ctrl + F8] and select the Module to run, just press the button.
- Click the [Developer] tab in the menu. Click [Insert] in the [Controls] group. Select [Button (Form Controls)].
- Click and drag inside the worksheet to create a button. The Assign Macro window will appear right after that. Select the Macro you want to embed into the button, in this case it is OpenPatientInfo. Click [OK].
- Customisation of the buttons (such as changing the button name, font type and size) can be done. If required, buttons can also be moved to another Sheet or to another Workbook (Ms. Excel file).
- I copied the button in a different Workbook, and when I pressed it, it successfully called the form I created.
Notes: Not actual data. Model photo source: https://www.pexels.com/photo/beautiful-woman-wearing-hijab-9218726/ - The final result I got after making some further adjustments is shown in the image below.
Note: Not actual data. Model image source: https://www.pexels.com/photo/portrait-of-woman-in-hijab-with-graffiti-background-31075446/
4. Closure
That's how I created a simple form in Microsoft Excel using Visual Basic for Application. I would appreciate feedback from the #techclub mentors (@kafio, @alejos7ven, @mohammadfaisal), especially on what and how I can improve in this regard. Thanks in advance.
My Introductory Post | Telegram | Discord | X
BAHASA INDONESIA
1. Pentingnya Basis Data (Database) Yang Baik
Basis data yang bagus adalah hal yang memiliki signifikansi yang tinggi dalam suatu institusi baik pemerintah maupun swasta, untuk berbagai kebutuhan yang biasanya ditentukan oleh sifat dari pekerjaan atau jasa yang mereka tawarkan serta jenis data yang mereka kelola. Basis data yang bagus membantu pihak-pihak yang berkepentingan untuk terus ter-update terhadap perkembangan dan menyediakan informasi yang dibutuhkan untuk berbagai keperluan termasuk monitoring, evaluasi, dan perencanaan.
Salah satu contoh kegunaan basis data adalah pencarian informasi terhadap seseorang (anggota perkumpulan atau pegawai atau lainnya, misal basis data buronan) berdasarkan kata kunci. Hal seperti ini umumnya ditemukan ketika seseorang ingin mengakses layanan publik seperti layanan kesehatan. Seseorang yang telah memiliki keanggotaan di suatu klinik, misalnya, akan memiliki ID yang terintegrasi dalam basis data klinik yang memuat info-info relevan seperti usia, gender, alergi, golongan darah, dan riwayat kesehatan.
2. Basis Data Dalam Pembangunan Pelayanan Kesehatan Di Indonesia
Dalam skala yang lebih luas, misalnya di Indonesia saat ini, setiap orang terdaftar dalam basis data kesehatan nasional dengan menggunakan nomer Kartu Tanda Penduduk (KTP). Jadi catatan kesehatannya bisa diakses oleh fasilitas kesehatan mana saja, jika suatu saat seseorang harus mendapatkan layanan kesehatan di fasilitas kesehatan berbeda, tidak lagi merupakan masalah walaupun mereka memiliki ID khusus di klinik (fasilitas kesehatan tertentu) karena Nomer KTP juga direkam di sana.
3. Formulir Sederhana Di Microsoft Excel Dengan VBA
3.1. Persiapan (Tabel Data)
Saya akan membuat suatu formulir sederhana terkait layanan kesehatan. dengan menggunakan Microsoft Excel dan Visual Basic for Application (VBA). Gambar berikut memperlihatkan anatomi dari formulir yang akan saya buat.
Penjelasan:
- Penunjuk 1 : kotak teks untuk mengisi ID;
- Penunjuk 2 : tombol pencarian pasien berdasarkan ID yang telah diketikkan;
- Penunjuk 3 : tempat di mana info pasien ditampilkan;
- Penunjuk 4 : foto pasien;
Untuk keperluan tersebut, saya telah memiliki tabel di Microsoft Excel. Tabel saya berisi daftar 10 pasien dan info terkait dari masing-masing mereka. Daftar ini bisa terus bertambah panjang dan formulir VBA akan menyesuaikan diri. Tabel yang saya miliki bisa dilihat pada gambar di bawah.
3.2. Membuat UserForm di VBA
- Buka VBA Editor di Ms. Excel dengan menekan tombol [ALT + F11]. Anda juga bisa mengakses ini dengan menekan tombol menu [Developer] lalu milih tombol [Visual Basic] pada pita di seksi Code.
- Jendela VBA terbuka. Klik [Insert] > [UserForm] untuk menambahkan UserForm.
- Jendela UserForm (secara default dinamakan UserForm1) muncul disertai dengan jendela Controls. Yang akan dilakukan selanjutnya adalah menambahkan elemen-elemen yang diperlukan (dan tersedia di jendela Controls) ke dalam UserForm.
Sesuai anatomi formulir yang telah direncanakan sebagaimana terlihat pada gambar 1 di atas, ada 4 macam elemen yang akan dipergunakan di dalam formulir ini, yaitu: TextBox (box untuk pengisian ID yang hendak dicari datanya); CommandButton (tombol untuk mengeksekusi pencarian berdasarkan ID yang diberikan); Label (untuk menampilkan informasi); dan Image Control (untuk menampilkan foto berdasarkan ID yang dicari). - Mari tambahkan elemen pertama, yaitu TextBox.
- (1) Klik pada tombol TextBox di jendela Controls;
- (2) Klik (atau klig dan seret) di dalam jendela UserForm untuk menempatkan TextBox.
- (3) Pergunakan jendela Properties (di kiri bawah) untuk menetapkan properties TextBox seperti mengubah nama (dalam hal ini -sebagaimana terlihat di dalam gambar- nama TextBox telah diubah menjadi txtSearch), menentukan jenis dan ukuran huruf, perataan (kiri atau kanan atau tengah), dan sebagainya.
- Tambahkan elemen-elemen lain.
Hasil setelah semua elemen yang diperlukan ditambahkan. - Selanjutnya menambahkah kode berisi perintah-perintah untuk mencari dan mengisi data berdasarkan ID yang diberikan. Klik kanan di dalam wilayahj kerja formulir dan pilih [View Code].
- Masukkan kode berikut:
Private Sub btnSearch_Click()
Dim ws As Worksheet
Dim rng As Range
Dim foundID As Range
Dim fotoPath As String
' Atur sheet yang mengandung data
Set ws = ThisWorkbook.Sheets("Sheet1")
' Temikan ID di kolom A
Set rng = ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
Set foundID = rng.Find(What:=txtSearch.Value, LookAt:=xlWhole)
' Jika ID ditemukan, isi data ke TextBox
If Not foundID Is Nothing Then
txtName.Value = foundID.Offset(0, 1).Value
txtGender.Value = foundID.Offset(0, 2).Value
txtDOB.Value = foundID.Offset(0, 3).Value
txtBloodType.Value = foundID.Offset(0, 4).Value
txtAllergy.Value = foundID.Offset(0, 5).Value
' Tampilkan foto di Image Control berdasarkan path yang diberikan
fotoPath = foundID.Offset(0, 6).Value
If Dir(fotoPath) <> "" Then
imgPhoto.Picture = LoadPicture(fotoPath)
Else
imgPhoto.Picture = Nothing
End If
Else
MsgBox "ID not found!", vbExclamation, "Warning"
End If
End Sub
- Kita perlu menyimpan file Excel dalam bentuk Excel Macro-Enabled Workbook (*.xlsm), agar file ini bisa menjalankan kode Macro yang kita tanam melalui Visual Basic for Application (VBA). Klik [Ctrl + S] di jendela VB Editor, lalu pilih [No] pada jendela dialog yang muncul.
- Pilih jenis file yang tepat, dan klik [Save].
3.3. Menjalankan Formulir (UserForm1)
- Agar formulir (UserForm1) yang baru saja dibuat bisa berjalan, kita perlu menambahkan Macro ke dalamnya. Di VBA Editor, klik [Insert] lalu pilih [Module].
- Masukkan kode berikut:
Sub OpenPatientInfo()
UserForm1.Show
End Sub
-
- Simpan Formulir (klik [Ctrl + S]).
- Di Ms. Excel, klik [Alt + F8], pilih formulir tadi berdasarkan nama yang dituliskan di dalam kode Macro tadi, dalam hal ini [OpenPatientInfo]. Klik [Run].
- Saya ketikkan sebuah ID yang telah tersedia di dalam tabel, 108, dan menekan tombol [Find]. Ini yang terjadi pada formulir UserForm1:
Catatan: Bukan data sebenarnya. Foto model : https://www.pexels.com/photo/positive-ethnic-lady-carrying-folder-with-documents-and-standing-in-sulight-6084178/ - Jika saya mengetikkan input yang tidak terdaftar sebagai ID di tabel, maka UserForm1 tersebut akan memberikan hasil sebagai berikut:
3.4. Tombol Pintas Untuk Membuka Formulir
Kita bisa membuat tombol di dalam file Excel yang akan menyederhanakan cara kita mengakses formulir tadi, jadi tidak perlu menekan [Ctrl + F8] dan memilih Module untuk dijalankan, cukup dengan menekan tombol.
- Klik tab [Developer] di menu. Klik [Insert] di group [Controls]. Pilih Button (Form Controls).
- Klik dan seret di dalam lembar kerja untuk membuat tombol. Jendela Assign Macro akan muncul setelah itu. Pilih Macro yang ingin ditanam ke dalam tombol, dalam hal ini adalah OpenPatientInfo. Klik [OK].
- Penyesuaian terhadap tombol (seperti merubah nama tombol, jenis dan ukuran huruf) bisa dilakukan. Jika diperlukan, tombol juga bisa dipindahkan ke Sheet lain atau ke Workbook (file Ms. Excel) lain.
- Saya menggandakan tombol tersebut di Workbook berbeda, dan ketika saya tekan berhasil memanggil formulir yang saya buat.
Catatan: Bukan data sebenarnya. Foto Model: https://www.pexels.com/photo/beautiful-woman-wearing-hijab-9218726/ - Hasil akhir yang saya dapat setelah melakukan beberapa penyesuaian lanjutan, terlihat pada gambar di bawah.
Note: Not actual data. Model image source: https://www.pexels.com/photo/portrait-of-woman-in-hijab-with-graffiti-background-31075446/
4. Penutup
Demikianlah cara membuat formulir sederhana di Microsoft Excel. Semoga ini bermanfaat.
Artikel Perkenalan Saya | Telegram | Discord | X
#techclub #techclub-s23w4 #indonesia #learnwithsteem #tutorial
Click on the image to see it on X.
I am impressed...
@kafio what do you think? Is the techclub dead that no one shows up? I start thinking it.
@aneukpineung78 how about asking for a fund for all the data you provide Steemit with? @xpilar has a proposal.
P.s. what happened to the MOuSE? This is a #howto!
No, someone will show up, @Alejos7ven will give a review of the posts.
@aneukpineung78 Your lesson is really comprehensive and well organized. the detailed instructions with associated pictures make following simple, I really like how you used practical healthcare applications to show the worth of database systems. This excellent post demonstrates that the club welcomes members from all backgrounds interested in technology, not just experienced programmers.
0.00 SBD,
0.00 STEEM,
0.10 SP
Thank you, Kafio. I hope one day I can do something like this on a website (online). You have done a great job on Steem, and must be amongst those who inspire Steemians (and probably non-Stemians) who love related technology.
just passing by, having troubles with my internet provider this week.
0.00 SBD,
0.05 STEEM,
0.05 SP
That is a pain. I really hope it will be solved soon dear @alejos7ven. I wish you all the best, but more likely it's strength you need to survive the provider if they are as nasty and no helpful as with me.
Technicians finally fixed it and I was able to connect again! I was so hesite to post my latest video about my daily routine with Artrogriposis. BTW, check it out if you have free time:
I saw the video, subscribed to the channel. You rock!
https://gifsec.com/you-rock-gifs/
I even think it's possible that some of them are no longer alive, and have left their accounts following the voting trail of @xpilar, who has put me on his autovote list. I sometimes hate my brain.
There are also males in the table, I jut happened to fill in the IDs of females.
I don't think so. I got these photos from Pexels, I provided information on the link of each photo, where everyone can read the licence that these photos are available to use for free. Below is the screenshot of the licence statement on the website as seen on https://www.pexels.com/license/.
I would love to. Is that possible? How?
I left you a link on Telegram. It sounds to me that every development which can benefit Steemit could be supported by a fund. Not only what you develop but also @kafio. It's not that only 50 million developments will make the platform better besides it feels that the small developments are faster realized and more visible.
So pexels works the same as pixabay.com both say there's no need to mention the owner although this is "forced" by many on Steemit same for AI generated pictures.
Let's see if tomorrow brings some solutions
I don't really understand the the proposal. But who would resist getting a slice of cake? Hhaa.
Yes, that's right, those image hosts don't require it, that's one thing. While in Steem it seems to be required, I might understand why.
Is tomorrow here already? Seems like more questions than solutions. Hahaha.
0.00 SBD,
0.05 STEEM,
0.05 SP
If no one knows there's a cake, a slice will not be collected. If donations will be realized, no idea how to do that if basically no one is interested, this means there will be a reserve fund used or meant for a very small selected group.
It's tomorrow. Let's see if there will be any answers.
@suryati1 @naina9 @ronnie10 @sumarni123 leave a comment please.
#miner-wewrite #wewrite #comment
😀
Have a great weekend.
Saya tidak tahu harus berkata apa, yang jelas saya sangat salut dengan teman kita @aneukpineung78 yang mempunyai kemampuan luar biasa di bidang teknologi.
Kalau saya jangan dikatakan lagi begitu melihat banyaknya arahan yang di tunjukkan semakin pusing kepala saya, ibaratnya ketika saya sedang mempelajari pelajaran matematika tiba-tiba saya mengantuk seperti telah menelan pil tidur 5 butir.
Saya tidak sanggup memikirkan terlalu jauh tentang semua ini karena semakin saya coba seakan isi kepala saya menjadi kosong saja, saya berkata jujur kalau soal yang seperti ini saya bakal menyerah sebelum bertanding 🤦.
Semoga anda selalu bahagia temanku🌹.
#wewrite #comment
Saya paham bahwa inti tulisan ini (pemanfaatan VBA di dalam Ms Excel untuk membangun formulir pencarian sederhana) tidak cocok untuk semua orang, bahkan untuk @wakeupkitty sekalipun, tetapi saya selalu kagum bagaimana dia bisa melihat celah lain yang bisa dikomentarinya, sebagaimana terlihat di sini. Jadi saya rasa berkomentar juga sebuah keahlian tersendiri. Ha!
Bu @suryati1, saya punya tulisan lain yang mungkin akan menarik bagi Anda, dan mungkin akan menambah pengetahuan Anda tentang Steem, atau sebaliknya: Anda menambah pengetahuan saya dalam topik tulisan ini, yaitu Tentang Witness Steem (6): Apa dan Kenapa Memilih Mereka. Tadinya saya ingin ini menjadi bagian dari miner-wewrite, tetapi ternyata wakeupkitty telah memiliki rencana lain sejak minggu lalu.
Déjame decirte que estoy sorprendido, antes había escuchado sobre visual basic pero nunca había visto algo puesto en práctica, me alegra mucho aprender algo nuevo cada día de todos ustedes, y hoy has hecho un buen trabajo enseñándome has despertado mi curiosidad sobre este tema.
Muchas veces los programas que tenemos no los sabemos usar ni al 20%, y es que la mayoría de ellos están cargados de opciones y botones que muchas veces incluso asusta tocarlos ja ja ja, profundizando en ello vemos que podemos hacer cosas increíbles.
Es posible obtener el archivo de este proyecto? me gustaria probarlo.
I knew about VB many years ago, and learnt it by myself from books and computer magazines, but I just didn't have any talent in programming. Your classes on programming some time ago reminded me of this. My interest was rekindled. I am very grateful for your co-operation, including being so kind when I asked for help on Discord.
I remember in one of your classes you said to keep learning code. This includes tinkering with code that others have created and learning from it. That's what happened when I created this form. And the internet, too, played an important role in me completing this project. I wish back then, when I was first interested in VB (programming), the internet was as easily available as it is today. Anyway, thank you very much.
The files to this project (containing Macro-enabled Excel workbook and photos and sources) can be found on Google Drive, here.
You have done a great job in developing this search engine in Excel. Btw all the models are really pretty. It looks like you are a big fan of these models 😉 and specifically women.
I knew you would come and pay attention, so I was looking for pictures of attractive women, so you wouldn't get bored of this post. Haha.
The main thing is not whether I'm an admirer of beautiful women, but that it's hard to find ugly women on pexels. Please try. Haha.
Hahaha 😂. Thank you for taking care of me. Literally I opened your post and it was more than just a search engine but an exhibition of models.
Nice efforts 😃
WATChOUT!!
sourc