Posts filed under ‘Vba Excel’

vba Bekerja dengan Workbooks

Berikut ini beberapa procedure vba yang digunakan untuk mengecek….

(Kode-kode berikut diperoleh dari OZGRID)

  • Apakah Workbook terbuka atau tidak.
  • Apakah Workbook ada dalam  file dan folder .
  • membuka Semua Workbooks folder tertentu .
  • Apakah  Worksheet tertentu ada dalam active Workbook .
  • Apakah  nama range tertentu ada dalam active Workbook .
  • A range is hidden by Auto Filters .
  • Count how many pages will be printed .
 (more…)

April 12, 2009 at 5:44 am 3 comments

Custom Function untuk Excel

Berikut adalah contoh custom function untuk excel.

untuk melihat semua fungsi dan contoh pemakaiannya  download file excel custom function di sini :

Fungsi terdiri dari :

(more…)

March 29, 2009 at 4:27 pm Leave a comment

Menampilkan Pesan Pada Workbook

Berikut adalah contoh VBA Excel yang dapat digunakan sebagai antar muka dengan user yang menampilkan pesan pada saat user membuka workbook (file) excel.  Selain menampilkan pesan, kegunaan dari sub routine ini bisa dikembangkan menjadi counter (penghitung) berapa kali user memakai workbook.  Misal kita memiliki aplikasi yang dikembangkan dengan excel yang harus berbayar, dan memperbolehkan user untuk memakai beberapa kali (uji coba) sebelum workbook tersebut dibayar.

Berikut tampilannya:     Download File pesan dng Counter workbook.xls

pesan

March 13, 2009 at 3:00 am 2 comments

eBook on Ms Office VBA

Advancing Your Office 2007 Power: Using Visual Basic
By Greg Perry

You can automate many routine tasks that you perform with Office 2007’s products by recording your keystrokes and storing them in a macro. You can easily assign the macro to a keystroke such as Ctrl+Shift+F12 and Word or Excel will zip through your automated commands the moment you press the keys.
Recorded macro keystrokes have been around for years and many Office users are familiar with them. The only drawback is they are simple and don’t go the extra mile when you want something really automated.  Read more on Office2007withVisualBasic.pdf size : 206.65 KB

March 6, 2009 at 7:09 pm 1 comment

FUNGSI EXCEL HITUNG KATA

Kadang kita perlu mengetahui berapa jumlah kata yang kita tulis dalam Workbooks atau Worksheet Ms Excel. Dalam Excel tidak ada fungsi khusus yang dapat digunakan untuk menghitung jumlah kata. Oleh karena itu perlu dibuat fungsi untuk menghitung kata dengan menggunakan VBA. However, you can count the number of words with a simple VBA macro.

Sub Procedure di bawah ini akan menghitung berapa jumlah kata yang ada di dalam ActiveSheet (sheet yang aktif) dan menampilkannya dalam Kotak Pesan (Message Box). Contoh file dapat di download adalah (Download file : hitung-kata.zip)

(more…)

January 17, 2009 at 4:53 am 2 comments

Improving Recorded Macros

The good way to learn the basics of VBA is to record a macro and see how Excel writes its own code. Often, though, recorded macros contain much more code than is necessary. The following exercises demonstrate how you can improve and streamline code that has been produced by a recorded macro.

Exercise 2: Improving on Recorded Macros

This exercise shows that when macros are recorded, often more code is generated than necessary. It demonstrates the use of the With statement to précis the code.

  1. Select any cell or block of cells.
  2. Start the macro recorder and call the macro FormatCells. The Relative References setting will not be relevant.
  3. Go to Format > Cells > Font and choose Times New Roman and Red.
    Go to Patterns and choose Yellow.
    Go to Alignment and choose Horizontal, Center
    Go to Number and choose Currency.
  4. Click OK and stop the recorder.
  5. Click the Undo button (or Ctrl+Z) to undo your changes to the worksheet.
  6. Select a block of cells and run the FormatCells macro. Note that it can not be undone! Type in the cells to check the result of the formatting.
  7. Look at the code:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00″

    With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .ShrinkToFit = False

    .MergeCells = False

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .FontStyle = “Regular”

    .Size = 10

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = xlUnderlineStyleNone

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End Sub

     

  8. Note all the extra instructions that have been recorded. Delete lines of code so that only the following remains:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00″

    With
    Selection

    .HorizontalAlignment = xlCenter

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    End With

    End Sub

  9. Run the macro to test the edited code. It still works as before.
  10. Now modify the code even further:

Sub
FormatSelection()

With
Selection

.NumberFormat = “$#,##0.00″

.HorizontalAlignment = xlCenter

.Font.Name = “TimesNewRoman”

.Font.ColorIndex = 3

.Interior.ColorIndex = 6

End With

End Sub

  1. Test the macro. Everything still works and the code will run much faster.
  2. Try recording the same macro using toolbar buttons instead of going to the dialog box:
    Change the Font to Times New Roman
    Change the Font Colour to Red
    Change the Fill Color to Yellow
    Click the Center button
    Click the Currency button
  3. Look at the code. You still get lots of stuff that you don’t necessarily want. Excel is recording all the default settings. Most of these are safe to delete.
  4. Experiment with editing directly into the code to change colours, the font, the number format etc.

December 27, 2008 at 3:50 pm Leave a comment

Improving Recorded Macros

The good way to learn the basics of VBA is to record a macro and see how Excel writes its own code. Often, though, recorded macros contain much more code than is necessary. The following exercises demonstrate how you can improve and streamline code that has been produced by a recorded macro.

Exercise 2: Improving on Recorded Macros

This exercise shows that when macros are recorded, often more code is generated than necessary. It demonstrates the use of the With statement to précis the code.

  1. Select any cell or block of cells.
  2. Start the macro recorder and call the macro FormatCells. The Relative References setting will not be relevant.
  3. Go to Format > Cells > Font and choose Times New Roman and Red.
    Go to Patterns and choose Yellow.
    Go to Alignment and choose Horizontal, Center
    Go to Number and choose Currency.
  4. Click OK and stop the recorder.
  5. Click the Undo button (or Ctrl+Z) to undo your changes to the worksheet.
  6. Select a block of cells and run the FormatCells macro. Note that it can not be undone! Type in the cells to check the result of the formatting.
  7. Look at the code:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00″

    With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .ShrinkToFit = False

    .MergeCells = False

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .FontStyle = “Regular”

    .Size = 10

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = xlUnderlineStyleNone

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End Sub

     

  8. Note all the extra instructions that have been recorded. Delete lines of code so that only the following remains:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00″

    With
    Selection

    .HorizontalAlignment = xlCenter

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    End With

    End Sub

  9. Run the macro to test the edited code. It still works as before.
  10. Now modify the code even further:

Sub
FormatSelection()

With
Selection

.NumberFormat = “$#,##0.00″

.HorizontalAlignment = xlCenter

.Font.Name = “TimesNewRoman”

.Font.ColorIndex = 3

.Interior.ColorIndex = 6

End With

End Sub

  1. Test the macro. Everything still works and the code will run much faster.
  2. Try recording the same macro using toolbar buttons instead of going to the dialog box:
    Change the Font to Times New Roman
    Change the Font Colour to Red
    Change the Fill Color to Yellow
    Click the Center button
    Click the Currency button
  3. Look at the code. You still get lots of stuff that you don’t necessarily want. Excel is recording all the default settings. Most of these are safe to delete.
  4. Experiment with editing directly into the code to change colours, the font, the number format etc.

December 27, 2008 at 3:35 pm Leave a comment

Using the Macro Recorder

Open Excel and the VBE (Visual Basic Editor). Unless it has been changed, the VBE window contains the Project Explorer window and the Properties window (these can be accessed from the View menu).

Project Explorer: Works like a file manager. Helps you navigate around the code in your workbook.

Properties Window: Shows the properties of the currently active object (e.g. Sheet1) of the current workbook (e.g. Book1).

Exercise 1: Recording a Macro.

This exercise shows what happens when a macro is recorded and demonstrates the difference between recording absolute and relative references.

  1. On an empty worksheet in a new workbook, select cell C10
  2. Start the Macro Recorder with option to save macro in This Workbook. At this point the VBE creates a new Modules folder. It’s quite safe to go and look at it – your actions won’t be recorded. Click the [+] next to the folder and see that the VBE has places a module in the folder and named it Module1. Double-click the module icon to open its code window. Switch back to Excel.
  3. Make sure that the Relative Reference button on the Stop Recording toolbar is NOT pressed in.
  4. Select cell B5 and stop the recorder.
  5. Switch to the VBE and look at the code:

    Range(“B5″).Select

  6. Now record another macro, exactly the same way, but this time with the Relative Reference button pressed in.
  7. Switch to the VBE and look at the code:

ActiveCell.Offset(-5, -1).Range(“A1″).Select

  1. Now record another macro, but instead of selecting cell B5, select a block of cells 3×3 starting at B5 (select cells B5:F7)
  2. Switch to the VBE and look at the code:

ActiveCell.Offset(-5, -1).Range(“A1:B3″).Select

  1. Play back the macros, having first selected a different cell than C10 (for Macro2 and Macro3 the starting cell must be in row 6 or below – see step 11 below)

    Macro1 – always moves the selection to B5
    Macro2 – moves the selection to a cell 5 rows up and 1 column to the left of the selected cell.
    Macro3 – always selects a block of six cells starting 5 rows up and 1 column to the left of the selected cell.

  2. Run Macro2 but force an error by selecting a cell in row 5 or above. The macro tries to select a non-existent cell because its code is telling it to select a cell 5 rows above the starting point, and that’s off the top of the sheet. Press Debug to be taken to the part of the macro that caused the problem.

    NOTE: When the VBE is in Debug mode the line of code that caused the problem is highlighted in yellow. You must “reset” the macro before you can proceed. Click the Reset button on the VBE toolbar or go to Run > Reset. The yellow highlighting disappears and the VBE comes out of Debug mode.

  3. It is important to try and anticipate user error like this. The simplest way is to modify the code to simply ignore errors and move on to the next task. Do this by adding the line…

    On Error Resume Next

    … immediately above the first line of the macro (underneath the line Sub
    Macro1()

  4. Run Macro2 as before, starting too high on the sheet. This time the line you typed tells Excel to ignore the line of code that it can’t execute. There is no error message and the macro exits having done all it can. Use this method of handling errors with caution. This is a very simple macro. A more complex macro would probably not perform as expected if errors were simply ignored. Also, the user has no idea that something has gone wrong.
  5. Modify the code of Macro2 to include a more sophisticated error handler thus:

    Sub Macro2()

    On Error GoTo ErrorHandler

    ActiveCell.Offset(-5, -1).Range(“A1″).Select

    Exit Sub

    ErrorHandler:

    MsgBox “You must start below Row 5″

    End Sub

 

  1. This time the user is presented with a dialog box when something goes wrong. If there is no error the line Exit Sub causes the macro to finish after it has done its job – otherwise the user would see the message even if there were no error.

December 27, 2008 at 3:34 pm Leave a comment

Fungsi dan Add In Terbilang

Banyak sekali XLer (orang yang hobi ngoprek excel) telah membuat fungsi yang berguna untuk membuat kwitansi atau laporan yang menampilkan angka dalam kata-kata (terbilang). Jadi disini akan dilampirkan linksnya untuk download fungsi atau add ini excel tersebut.

Posting fungsi terbilang yang saya upload tahun 2005 di aurino.tblog.com adalah Fungsi Terbilang dan TerbilangRp

di XL Mania banyak sekali yang memposting antara lain Siti V, Abimono, Zam Wong Batu dll  yaitu

terbilang_101

Terbilang is a free Visual Basic Add In for Microsoft Excel to help Indonesian people to write numbers in word mode

Membuat Add In Terbilang Untuk Microsoft Excel dari IlmuKomputer.Com

Miswati: Is a Visual Basic Add In on Microsoft Excel to help Indonesian people for write number in word mode where in use as at any invoice Excel application just type =terbilang(+cell/number) an the add in is write like is it.

Silakan Klik Disini dari ICT Klungkung

Terbilang is a free Visual Basic Add In for Microsoft Excel to help Indonesian people to write numbers in word mode

skip to main | skip to sidebar

Menerjemahkan Angka ke Kata-kata di dalam Microsoft Excel di Cixers

dan masih banyak lagi …

.

December 14, 2008 at 3:59 pm 7 comments

Font Blinking di Excel

Excel tidak punya fitur untuk membuat teks berkedip.  (catatan: blinking teks sangat mengganggu orang yang membaca pesan anda jika pesannya banyak). Untuk membuat teks berkedip, Anda harus menjalankan prosedur secara berkala untuk mengubah warna font teks. Metode OnTime dalam VBE dapat digunakan untuk menjalankan prosedur itu. Anda dapat menggunakan module VBA sbb:

Download file:  Blinking excel 2007.zip

Blinking excel 97-2003.zip

Prosedur untuk Teks Blinking

Public RunWhen As Double

Sub StartBlink()
With ThisWorkbook.Worksheets(“Sheet1″).Range(“A1″).Font
If .ColorIndex = 3 Then ‘ Red Text
.ColorIndex = 2 ‘ White Text
Else
.ColorIndex = 3 ‘ Red Text
End If
End With
RunWhen = Now + TimeSerial(0,0,1)
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , True
End Sub

Sub StopBlink()
ThisWorkbook.Worksheets(“Sheet1″).Range(“A1″).Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
End Sub

Kemudian dalam module ThisWorkbook, masukan kode berikut:

Private Sub Workbook_Open()
StartBlink
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlink
End Sub

November 10, 2008 at 2:56 pm 3 comments

Older Posts


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 76,508 hits

Twitter Updates

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Iklan


Follow

Get every new post delivered to your Inbox.