LINGKUNGAN VBA

LINGKUNGAN VBA Excel (Lingkungan)

Fungsi Lingkungan VBA (berarti LINGKUNGAN ) dapat dikategorikan sebagai “Fungsi Informasi” karena fungsi ini mengembalikan nilai untuk variabel lingkungan sistem operasi. Variabel lingkungan (sistem operasi) berisi informasi tentang profil semua pengguna, nama pengguna, profil pengguna, folder rumah pengguna, dll. Fungsi ini mengembalikan nilai string.

Sintaksis

Fungsi ini hanya memiliki satu argumen, yaitu ‘ Expression .’ Kita dapat menentukan posisi numerik (nilai bilangan bulat) yang mewakili posisi numerik variabel lingkungan dalam tabel variabel lingkungan atau nama variabel itu sendiri.

Jika kita menentukan posisi numerik, fungsi mengembalikan variabel lingkungan dan nilainya, dengan tanda sama dengan di antaranya.

Jika kita menentukan nama variabel, fungsi mengembalikan satu-satunya nilai.

Keluaran:

Bagaimana cara menggunakan Fungsi Lingkungan di VBA?

Contoh 1

Buka Command Prompt menggunakan Fungsi Environ di VBA.

Untuk melakukan hal yang sama, langkah-langkahnya adalah:

Sisipkan tombol perintah menggunakan perintah ‘Sisipkan’ yang tersedia di grup ‘Kontrol Formulir’ di tab ‘Pengembang’ atau gunakan tombol pintasan excelShortcut Excel KeyAn Pintasan Excel adalah teknik melakukan tugas manual dengan cara yang lebih cepat.Baca lebih lanjut ( Alt+F11 ).

Jika tab ‘Pengembang’ tidak terlihat, gunakan langkah-langkah berikut untuk membuat tab terlihat.

Klik ‘File’ dan pilih ‘Options’ dari daftar.

Pilih ‘Sesuaikan Pita’ dari menu sebelah kiri, centang kotak untuk tab ‘Pengembang’ , dan klik ‘OK.’

Sekarang, tab ‘Pengembang’ terlihat.

Saat memasukkan tombol perintah, jika kita terus menekan tombol ALT , maka tepi tombol perintah akan berada di sepanjang batas sel. Ubah properti dari tombol perintah yang disisipkan menggunakan menu kontekstual, yang kita dapatkan dengan mengklik kanan pada ‘Tombol perintah.’

Jendela Properti

Untuk menulis kode VBA Kode VBA Kode VBA mengacu pada serangkaian instruksi yang ditulis oleh pengguna dalam bahasa pemrograman Visual Basic Applications pada Visual Basic Editor (VBE) untuk melakukan tugas tertentu. Baca lebih lanjut untuk tombol perintah, kita harus memilih perintah tombol dan pilih ‘Lihat Kode’ dari menu kontekstual.

Tulis kode sebagai berikut:

Kami telah memanggil fungsi ‘Shell’ untuk mengetik perintah untuk menjalankan program (dalam kasus kami, prompt perintah).

Kami telah menggunakan ‘ComSpec,’ yang merupakan singkatan dari ‘Command Specifier.’

Sekarang, keluar dari VBE dan klik tombol “Command”. Kami telah menampilkan prompt perintah.

Contoh #2

Misalkan kita ingin mengekstrak nama file dan detail untuk folder yang dipilih.

Langkah-langkah untuk melakukan hal yang sama adalah:

Pertama, isi sel B2:H9 dengan warna oranye muda.

Buat label menggunakan perintah ‘Sisipkan’ di grup ‘Kontrol Formulir’ di grup ‘Pengembang . ‘

Harap buat label di bawah dan edit properti seperti Caption , BackColor , BackStyle , BorderStyle , dan Shadow.

Buat ComboBox menggunakan perintah ComboBox (salah satu dari ActiveX Controls) yang tersedia di perintah Insert di grup Form Controls di Developer.

Setelah membuat Combo Box di excelCombo Box Di ExcelCombo Box di Excel adalah sejenis alat validasi data yang dapat membuat daftar dropdown untuk dipilih pengguna dari daftar yang telah ditentukan sebelumnya. Ini adalah kontrol formulir yang tersedia di tab sisipkan tab pengembang. Baca lebih lanjut, kita dapat mengubah propertinya.

Kami akan menambahkan kode untuk daftar yang akan ditampilkan di kotak kombo menggunakan perintah Lihat Kode di menu kontekstual.

Ini adalah kode untuk ComboBox ‘Pilih Folder’ .

Ini adalah kode untuk ComboBox ‘SortBy’ .

Ini adalah kode untuk ComboBox ‘Pilih Pesanan’ .

Kami akan membuat kotak daftar yang berisi semua jenis file untuk memilihnya agar hanya mendapatkan file tersebut di hasil. Untuk melakukan hal yang sama, silakan pilih ” List Box (ActiveX Control) ” dari perintah ” Insert ” di grup ” Controls ” di tab ” Developer “.

Seret kotak daftar, seperti yang ditunjukkan di bawah ini.

Ubah properti kotak daftar sebagai berikut.

Silakan gunakan kode berikut untuk menambahkan jenis file ke kotak daftar.

Tulis kode di ‘Buku kerja ini.’

Kode:

Sub Workbook_Open() Dim ArrFileType(25) Sebagai Varian ArrFileType(0) = “Lembar Kerja Microsoft Excel 97-2003(.xls)” ArrFileType(1) = “Lembar Kerja Microsoft Office Excel(.xlsx)” ArrFileType(2) = ” Lembar Kerja Berkemampuan Makro Microsoft Excel(.xlsm)” ArrFileType(3) = “Dokumen Word 97-2003(.doc)” ArrFileType(4) = “Dokumen Word 2007-2010(.docx)” ArrFileType(5) = “Teks Document(.txt)” ArrFileType(6) = “Adobe Acrobat Document(.pdf)” ArrFileType(7) = “Folder Terkompresi (zip)(.Zip)” ArrFileType(8) = “Arsip WinRAR(.rar)” ArrFileType (9) = “Pengaturan konfigurasi(.ini)” ArrFileType(10) = “File GIF(.gif)” ArrFileType(11) = “File PNG(.png)” ArrFileType(12) = “File JPG(.jpg) ” ArrFileType(13) = “MP3 Format Sound(.mp3)” ArrFileType(14) = “M3U File(.m3u)” ArrFileType(15) = “Rich Text Format(.rtf)” ArrFileType(16) = “Video MP4 (.mp4)” ArrFileType(17) = “Video Clip(.avi)” ArrFileType(18) = “Windows Media Player(.mkv)” ArrFileType(19) = “SRT File(.srt)” ArrFileType(20) = “File PHP(.php)” ArrFileType(21) = “Firefox HTML Document(.htm, .html)” ArrFileType(22) = “Cascading Style Sheet Document(.css)” ArrFileType(23) = “JScript Script File(.js)” ArrFileType(24) = “XML Document( .xml)” ArrFileType(25) = “Windows Batch File(.bat)” Sheet2.FileTypesListBox.List = ArrFileType End Sub

Masukkan kotak centang menggunakan perintah ‘ Sisipkan ‘ yang sama di grup ‘ Kontrol ‘ di tab ‘ Pengembang ‘ dan ubah properti untuk ‘Kotak centang’ yang disisipkan menggunakan kekuatan ‘Properti’ yang tersedia di grup yang sama setelah memilih objek.

Sisipkan tombol perintah menggunakan perintah ‘Sisipkan’ yang tersedia di grup yang sama dan ubah properti seperti keterangan dan hal lainnya.

Kami telah membentuk seluruh struktur. Sekarang kita perlu menulis kodenya.

Aktifkan ‘Design Mode’, dan klik kanan pada tombol ‘ Fetch all files details ‘ untuk memilih ‘ View Code ‘ dari menu kontekstual untuk menambahkan kode untuk peralihan.

Kami akan mendeklarasikan beberapa variabel terlebih dahulu di modul.

Di bawah ini adalah kode yang ditambahkan ke tombol ‘ Ambil semua detail file ‘.

Kode:

Sub Pribadi FetchFilesBtnCommandButton_Click() iRow = 14 fPath = Environ(“HOMEPATH”) & “” & SelectTheFolderComboBox.Value If fPath <> “” Kemudian Setel FSO = New Scripting.FileSystemObject If FSO.FolderExists(fPath) <> False Kemudian Setel SourceFolder = FSO.GetFolder(fPath) Jika Sheet2.IncludingSubFoldersCheckBox.Value = True Maka IsSubFolder = True Else IsSubFolder = Salah Jika SourceFolder.Files.Count = 0 Kemudian MsgBox “Tidak ada file di Folder ini” & vbNewLine & vbNewLine & “Periksa folder Anda path dan Coba Lagi !!”, vbInformation Exit Sub End If End If Call ClearResult If FetchAllTypesOfFilesCheckBox.Value = True Kemudian Panggil ListFilesInFolder(SourceFolder, IsSubFolder) Panggil ResultSorting(xlAscending, “C14”, “D14”, “E14”) Else Call ListFilesInFolderXtn(SourceFolder, IsSubFolder) Panggil ResultSorting(xlAscending, “C14”, “D14”, “E14”) End If FilesCountLabel.Caption = iRow – 14 Else MsgBox “Jalan yang Dipilih Tidak Ada !!” & vbNewLine & vbNewLine & “Pilih Yang Benar dan Coba Lagi !!”, vbInformation End If Else MsgBox “Folder Path Tidak Bisa Kosong!!” & vbNewLine & vbNewLine & “”, vbInformation Berakhir Jika Berakhir Sub

Tentukan fungsi ‘Hapus Hasil’ dalam modul. Untuk memasukkan modul, pilih ‘Buku Kerja Ini,’ ‘ Sisipkan,’ lalu ‘Modul’.

Tulis kode berikut di modul.

Kode untuk ClearResult

Ada lebih banyak subrutin seperti ‘ListFilesInFolder,’ ‘ListFilesInFolderXtn,’ ‘ResultSorting’, dan kita akan mendefinisikan semua subrutin ini dalam modul.

‘ListFilesInFolder’

Kode:

Public Sub ListFilesInFolder(SourceFolder As Scripting.Folder, IncludeSubfolders As Boolean) On Error Resume NextError Resume NextVBA On Error Resume Statement adalah aspek penanganan kesalahan yang digunakan untuk mengabaikan baris kode karena kesalahan tersebut terjadi dan melanjutkan dengan baris berikutnya tepat setelahnya baris kode dengan error.baca lebih lanjut Untuk Setiap FileItem Di SourceFolder.Files ‘ tampilkan properti file Cells(iRow, 2).Formula = iRow – 13 Cells(iRow, 3).Formula = FileItem.Name Cells(iRow, 4) .Formula = FileItem.Path Cells(iRow, 5).Formula = Int(FileItem.Size / 1024) Cells(iRow, 6).Formula = FileItem.Type Cells(iRow, 7).Formula = FileItem.DateLastModified Cells(iRow , 8).Select Selection.Hyperlinks.Add Anchor:=Pilihan, Alamat:= _ FileItem.Path, TextToDisplay:=”Klik Di Sini untuk Membuka” ‘Sel(iRow, 8).Formula = “=HYPERLINK(“”” & FileItem.Path & “””,””” & “Klik Di Sini untuk Membuka” & “””)” iRow = iRow + 1 ‘ nomor baris berikutnya FileItem Berikutnya Jika SertakanSubfolder Kemudian Untuk Setiap SubFolder Di SourceFolder.Su bFolder ListFilesInFolder SubFolder, Benar SubFolder Berikutnya Berakhir Jika Diatur FileItem = Tidak Ada Yang Diatur SourceFolder = Tidak Ada Yang Diatur FSO = Tidak Ada Yang Berakhir Sub

‘ListFilesInFolderXtn’

Sub Publik ListFilesInFolderXtn(SourceFolder As Scripting.Folder, IncludeSubfolders As Boolean) On Error Resume NextError Resume NextVBA On Error Resume Statement adalah aspek penanganan kesalahan yang digunakan untuk mengabaikan baris kode yang menyebabkan kesalahan terjadi dan melanjutkan dengan baris berikutnya tepat setelahnya baris kode dengan error.baca lebih lanjut Dim FileArray As Variant FileArray = Get_File_Type_Array Untuk Setiap FileItem Di SourceFolder.Files Panggil ReturnFileType(FileItem.Type, FileArray) If IsFileTypeExists = True Then Cells(iRow, 2).Formula = iRow – 13 Cells (iRow, 3).Formula = FileItem.Nama Sel(iRow, 4).Formula = FileItem.Path Sel(iRow, 5).Formula = Int(FileItem.Size / 1024) Sel(iRow, 6).Formula = FileItem .Type Cells(iRow, 7).Formula = FileItem.DateLastModified Cells(iRow, 8).Select Selection.Hyperlinks.Add Anchor:=Pilihan, Alamat:= _ FileItem.Path, TextToDisplay:=”Klik Di Sini untuk Membuka” ‘ Sel(iRow, 8).Formula = “=HYPERLINK(“”” & FileItem.Path & “””,””” & “Klik Di Sini untuk Membuka” & “””)” iRow = iRow + 1 ‘ nomor baris berikutnya Berakhir Jika FileItem Berikutnya Jika SertakanSubfolder Kemudian Untuk Setiap SubFolder Di SourceFolder.SubFolders ListFilesInFolderXtn SubFolder, Benar SubFolder Berikutnya Berakhir Jika Diatur FileItem = Tidak Ada Yang Diatur SourceFolder = Tidak Ada Yang Diatur FSO = Tidak Ada Yang Berakhir Sub

‘Pengurutan Hasil’

Sub HasilPenyortiran(xlSortOrder Sebagai String, sKey1 Sebagai String, sKey2 Sebagai String, sKey3 Sebagai String) Range(“C13”).Pilih Range(Seleksi, Seleksi.End(xlDown)).Pilih Rentang(Seleksi, Seleksi.Akhir(xlToRight) ).Pilih Pilihan.Sort Key1:=Range(sKey1), Order1:=xlSortOrder, Key2:=Range(sKey2 _ ), Order2:=xlAscending, Key3:=Range(sKey3), Order3:=xlSortOrder, Header _ := xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal Range(“B14”).Pilih End Sub

Dalam subrutin ‘ListFilesInFolderXtn’ , kami telah memanggil fungsi bernama ‘ReturnFileType’ dan ‘GetFileTypeArray’ . Kita perlu mendefinisikan fungsi dalam modul yang sama.

‘ReturnFileType’

Kode:

Fungsi Publik ReturnFileType(fileType Sebagai String, FileArray Sebagai Varian) Sebagai Boolean Dim i Sebagai Integer IsFileTypeExists = Salah Untuk i = 1 To UBound(FileArray) + 1 If FileArray(i – 1) = fileType Then IsFileTypeExists = True Exit For Else IsFileTypeExists = Salah Akhir Jika Fungsi Akhir Berikutnya

‘DapatkanFileTypeArray’

Kode:

Fungsi Publik Get_File_Type_Array() Sebagai Varian Dim i, j, TotalSelected As Integer Dim arrList() Sebagai String TotalSelected = 0 For i = 0 To Sheet2.FileTypesListBox.ListCount – 1 If Sheet2.FileTypesListBox.Selected(i) = True Maka TotalSelected = TotalSelected + 1 End If Next ReDim arrList(0 To TotalSelected – 1) As String j = 0 i = 0 For i = 0 To Sheet2.FileTypesListBox.ListCount – 1 If Sheet2.FileTypesListBox.Selected(i) = True Maka arrList(j ) = Left(Sheet2.FileTypesListBox.List(i), InStr(1, Sheet2.FileTypesListBox.List(i), “(“) – 1) j = j + 1 End If Next Get_File_Type_Array = arrList Akhir Fungsi

Kami memiliki tombol perintah bertuliskan ‘Ekspor ke File Excel’, kami perlu menulis kode untuk tombol ini sebagai berikut:

Dalam modul, tentukan subrutin bernama ‘Export_to_excel.’

Kode:

Sub Export_to_excel() On Error GoTo err Dim xlApp As New Excel.Application Dim xlWB As New Workbook Set xlWB = xlApp.Workbooks.Add ‘xlWB.Add xlApp.Visible = False ThisWorkbook.Activate Range(“B13”).Select Range( Seleksi, Seleksi.End(xlDown)).Pilih Range(Seleksi, Seleksi.End(xlToRight)).Pilih Seleksi.Copy xlApp.Visible = True xlWB.Aktifkan xlWB.Sheets(“Sheet1″).Pilih xlWB.Sheets(” Sheet1″).Range(“B2”).PasteSpecial Paste:=xlPasteValues xlWB.Sheets(“Sheet1”).Cells.Select xlWB.Sheets(“Sheet1”).Cells.EntireColumn.AutoFit xlWB.Sheets(“Sheet1”) .Range(“B2”).Select Exit Sub err: MsgBox (“Error Terjadi saat mengekspor. Coba lagi”) End Sub

Kami memiliki satu lagi tombol perintah yang bertuliskan ‘Ekspor ke File Teks.’ Kami akan menulis kode untuk tombol perintah sebagai berikut:

Dalam kode ini, kita dapat melihat bahwa kita memiliki formulir pengguna, yang perlu kita desain menggunakan langkah-langkah berikut:

Klik kanan pada lembar ‘Sheet2 (Example2)’ dan pilih ‘Insert’ dan kemudian ‘UserForm’ dari menu.

Rancang UserForm menggunakan alat dari kotak alat.

Kami telah menggunakan ‘Label’, ‘Kotak kombo,’ ‘Kotak Teks’, dan ‘tombol Perintah’ untuk Formulir Pengguna dan telah mengubah judul dan nama untuk semua komponen.

Untuk command button (OK) yang pertama , kita harus menuliskan kodenya sebagai berikut:

Kode:

Private Sub CommandButton1_Click() Redupkan iSeperator Sebagai String If ComboBox1.Value = “Other” Maka iSeperator = TextBox1.Value Else iSeperator = ComboBox1.Value End If If iSeperator = “” Then If MsgBox(“Hello You have not selected any delimeter.” & vbNewLine & vbNewLine & _ “Akan sangat sulit untuk membaca file Teks, tanpa pembatas tertentu”, vbYesNo) = vbYes Kemudian Panggil textfile(iSeperator) Else Exit Sub End If Else Pilih CasePilih CasePilih case adalah pengganti untuk menuliskan beberapa pernyataan IF di VBA. Dalam kasus banyak pernyataan IF, ini menjadi membosankan dan lebih kompleks, tetapi dalam pernyataan kasus tertentu, kami mendefinisikan kriteria sebagai kasus dan hasil yang berbeda.Baca lebih lanjut ComboBox1.ListIndex Kasus 0: iSeperator = “,” Kasus 1: iSeperator = ” |” Kasus 2: iSeperator = “vbTab” Kasus 3: iSeperator = “;” End Select Call textfile(iSeperator) Unload Me End If End Sub

Kami telah memanggil fungsi ‘textfile’ di subrutin untuk tombol perintah, jadi kami perlu mendefinisikan fungsi ‘textfile’ di modul.

Kode:

Sub file teks(iSeperator Sebagai String) Dim iRow, iCol Dim iLine, f ThisWorkbook.Activate Range(“B13”).Select TotalRowNumber = Range(Selection, Selection.End(xlDown)).Count – 12 If iSeperator <> “vbTab” Kemudian Buka ThisWorkbook.Path & “File1.txt” Untuk Keluaran Sebagai #1 Cetak #1, “” Tutup #1 Buka ThisWorkbook.Path & “File1.txt” Untuk Tambahkan Sebagai #1 Untuk iRow = 13 Ke TotalRowNumber iLine = “” Untuk iCol = 2 To 7 iLine = iLine & iSeperator & Cells(iRow, iCol).Value Next Print #1, iLine Next Close #1 Else Open ThisWorkbook.Path & “File1.txt” Untuk Keluaran Sebagai #1 Cetak #1, “” Tutup #1 Buka ThisWorkbook.Path & “File1.txt” Untuk Tambahkan Sebagai #1 Untuk iRow = 13 Ke TotalRowNumber iLine = “” Untuk iCol = 2 Hingga 7 iLine = iLine & vbTab & Cells(iRow, iCol).Value Next Print #1, iLine Next Close #1 End If f = Shell(“C:WINDOWSnotepad.exe ” & ThisWorkbook.Path & “File1.txt”, vbMaximizedFocus) MsgBox “File Anda disimpan di ” & ThisWorkbook.Path & ” File1.txt” Akhir Sub

Kita harus menulis kode berikut untuk command button 2 (Cancel) . Klik dua kali pada tombol batal untuk menulis kode.

Untuk Combo Box untuk memilih specifier, tuliskan kode berikut.

Untuk UserForm, tulis kode berikut.

Tulis kode berikut untuk kotak centang ‘Ambil semua jenis file’ .

Untuk ‘ListBox’ untuk jenis file, tulis kode berikut.

Untuk combo box ‘SelectTheOrder’ , tuliskan kode berikut.

Kode:

Private Sub SelectTheOrderComboBox_Change() Select Case (SelectTheOrderComboBox.Value) Case “Ascending” If SortByComboBox.Value = “File Name” Kemudian Panggil ResultSorting(xlAscending, “C14”, “E14”, “G14”) End If If SortByComboBox.Value = “Tipe File” Lalu Panggil ResultSorting(xlAscending, “F14”, “E14”, “C14”) End If If SortByComboBox.Value = “File Size” Kemudian Panggil ResultSorting(xlAscending, “E14”, “C14”, “G14” ) Akhiri Jika SortByComboBox.Value = “Terakhir Dimodifikasi” Kemudian Panggil ResultSorting(xlAscending, “G14”, “C14”, “E14”) Akhiri Jika Kasus “Turun” Jika SortByComboBox.Value = “Nama File” Kemudian Panggil ResultSorting(xlDescending , “C14”, “E14”, “G14”) End If If SortByComboBox.Value = “File Type” Kemudian Panggil ResultSorting(xlDescending, “F14”, “E14”, “C14″) End If If SortByComboBox.Value = ” File Size” Lalu Panggil ResultSorting(xlDescending, “E14”, “C14”, “G14”) End If If SortByComboBox.Value = “Last Modified” Kemudian Panggil ResultSorting(xlDescending, “G14”, “C14”, “E14”) End If Case Default Exit Sub End Select End Sub

Kami akan menulis kode berikut untuk kotak kombo ‘Sort By’ .

Kode:

Private Sub SortByComboBox_Change() Select Case (SelectTheOrderComboBox.Value) Case “Ascending” If SortByComboBox.Value = “File Name” Kemudian Panggil ResultSorting(xlAscending, “C14”, “E14”, “G14”) End If If SortByComboBox.Value = “Tipe File” Lalu Panggil ResultSorting(xlAscending, “F14”, “E14”, “C14”) End If If SortByComboBox.Value = “File Size” Kemudian Panggil ResultSorting(xlAscending, “E14”, “C14”, “G14” ) Akhiri Jika SortByComboBox.Value = “Terakhir Dimodifikasi” Kemudian Panggil ResultSorting(xlAscending, “G14”, “C14”, “E14”) Akhiri Jika Kasus “Turun” Jika SortByComboBox.Value = “Nama File” Kemudian Panggil ResultSorting(xlDescending , “C14”, “E14”, “G14”) End If If SortByComboBox.Value = “File Type” Kemudian Panggil ResultSorting(xlDescending, “F14”, “E14”, “C14″) End If If SortByComboBox.Value = ” File Size” Lalu Panggil ResultSorting(xlDescending, “E14”, “C14”, “G14”) End If If SortByComboBox.Value = “Last Modified” Kemudian Panggil ResultSorting(xlDescending, “G14”, “C14”, “E14”) End If Case Default Exit Sub End Select End Sub

Sekarang, kami telah menulis seluruh kode. Sekarang kita dapat memilih folder dan jenis file yang diinginkan dan menemukan daftar file, yang dapat kita urutkan berdasarkan ‘Nama File,’ ‘Jenis File,’ ‘Ukuran File,’ atau ‘Terakhir Dimodifikasi’ dan mengekspor daftar ke Excel atau file teks.

Hal-hal untuk diingat

Jika nilai yang kami tentukan untuk argumen ‘berinvestasi’ tidak ada dalam tabel string lingkungan, fungsi ENVIRON mengembalikan string dengan panjang nol.

Artikel yang Direkomendasikan

Artikel ini telah menjadi panduan untuk VBA Environ. Di sini, kita membahas penggunaan fungsi Environ VBA di Excel untuk mengembalikan nilai variabel lingkungan sistem operasi, bersama dengan contoh dan templat Excel yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –