CARA BUAT MACRO PADA EXCEL

Have you ever wanted to impress your boss or colleagues with your Excel skills? Well, look no further than our guide on how to utilize VBA macros and write functions to turn numbers into words. With these tips and tricks, you’ll be able to make your Excel spreadsheets stand out and streamline your work processes.

Step 1: Enabling and accessing VBA Macros

Before we dive into the nitty-gritty of writing Excel functions and macros, we must first enable and access VBA Macros in Excel. Follow these steps to enable VBA Macros:

1. Open Excel and navigate to Options.
2. Select “Customize Ribbon” in the left sidebar and check the box next to “Developer.”
3. Select “OK.”
4. Navigate to the “Developer” tab at the top of the Excel screen.

By enabling VBA Macros, you can now access the VBA editor, which is where you’ll be able to write and edit macros and functions.

Step 2: Writing Functions to Turn Numbers into Words

One of the most impressive and useful skills you can have in Excel is the ability to write functions that turn numerical values into written words. This can be especially helpful if you’re presenting data to clients or stakeholders who might not be as familiar with numbers and formulas. Follow these steps to write a function:

1. Open Excel and navigate to the VBA editor by going to the “Developer” tab and selecting “Visual Basic”.
2. Click “Insert” and select “Module.”
3. In the module, type “Function SpellNumber (ByVal MyNumber)” followed by “As String.”
4. Copy and paste the below code into the module:

“`
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = ” Thousand ”
Place(3) = ” Million ”
Place(4) = ” Billion ”
Place(5) = ” Trillion ”

MyNumber = Trim(Str(MyNumber))

DecimalPlace = InStr(MyNumber, “.”)

If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
“00”, 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))
End If

Count = 1

Do While MyNumber <> “”
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> “” Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) – 3)
Else
MyNumber = “”
End If
Count = Count + 1
Loop

Select Case Dollars
Case “”
Dollars = “No Dollars”
Case “One”
Dollars = “One Dollar”
Case Else
Dollars = Dollars & ” Dollars”
End Select

Select Case Cents
Case “”
Cents = ” and No Cents”
Case “One”
Cents = ” and One Cent”
Case Else
Cents = ” and ” & Cents & ” Cents”
End Select

SpellNumber = Dollars & Cents

End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right(“000” & MyNumber, 3)

If Mid(MyNumber, 1, 1) <> “0” Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & ” Hundred ”
End If

If Mid(MyNumber, 2, 1) <> “0” Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String

Result = “”

If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: Result = “Ten”
Case 11: Result = “Eleven”
Case 12: Result = “Twelve”
Case 13: Result = “Thirteen”
Case 14: Result = “Fourteen”
Case 15: Result = “Fifteen”
Case 16: Result = “Sixteen”
Case 17: Result = “Seventeen”
Case 18: Result = “Eighteen”
Case 19: Result = “Nineteen”
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: Result = “Twenty ”
Case 3: Result = “Thirty ”
Case 4: Result = “Forty ”
Case 5: Result = “Fifty ”
Case 6: Result = “Sixty ”
Case 7: Result = “Seventy ”
Case 8: Result = “Eighty ”
Case 9: Result = “Ninety ”
Case Else
End Select

Result = Result & GetDigit _
(Right(TensText, 1))
End If

GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = “One”
Case 2: GetDigit = “Two”
Case 3: GetDigit = “Three”
Case 4: GetDigit = “Four”
Case 5: GetDigit = “Five”
Case 6: GetDigit = “Six”
Case 7: GetDigit = “Seven”
Case 8: GetDigit = “Eight”
Case 9: GetDigit = “Nine”
Case Else: GetDigit = “”
End Select
End Function
“`

This function will allow you to convert numerical values into written words. For example, if you input “1234.56”, the output will be “One Thousand Two Hundred Thirty-Four Dollars and Fifty-Six Cents.” The possibilities are endless, and the function is fully customizable to fit your specific data set.

Step 3: Writing Macros to Automate Repetitive Tasks

Writing macros in Excel can be an excellent way to automate repetitive tasks, saving you time and energy. Let’s take a look at how you can use macros to insert photos into tables or cells in your Excel spreadsheet.

1. Open Excel and navigate to the VBA editor by going to the “Developer” tab and selecting “Visual Basic”.
2. Click “Insert” and select “Module.”
3. In the module, enter the following code:

“`
Sub InsertPhoto()
Dim Pic As Picture
Dim Path1 As String
Dim Table1 As Range

‘Path to picture
Path1 = “C:\Users\YourUserName\Desktop\ImageName.jpg”

‘This is where your table is on the sheet
Set Table1 = Range(“A1:C4”)

Set Pic = ActiveSheet.Pictures.Insert(Path1)

With Pic
‘Sets the top left cell of picture
.Top = Table1.Top
.Left = Table1.Left
‘Sets height and width of picture
.Width = Table1.Width
.Height = Table1.Height
‘Sets picture placement type
.Placement = xlMove
End With
End Sub
“`

This macro will allow you to select a table or a cell in your Excel sheet, and automatically insert a photo into it from the file path specified in the code. This can save you time if you have to insert many photos into your spreadsheet.

FAQs

Q: What are VBA Macros, and why should I use them?

A: VBA Macros are a set of programming instructions that can automate tasks in Excel and other Microsoft Office products. They can save you time by allowing you to program repetitive tasks and make your spreadsheet look more professional. Macros can be used to create dynamic charts, dashboards, and data visualizations that can be updated automatically.

Q: Is coding experience necessary to write Excel functions and macros?

A: While a basic understanding of coding concepts can be helpful, it’s not necessary to have prior coding experience to write Excel functions and macros. Online tutorials and forums can provide step-by-step instructions and helpful tips for coding in Excel. Additionally, recording macros can be a straightforward way to create automated tasks without writing code.

Here’s a helpful video tutorial on writing Excel macros and functions:

In conclusion, writing functions and macros in Excel can save you ample time and impress your colleagues and superiors. With a basic understanding of VBA Macros, you can create automated tasks and make your spreadsheets look professional and dynamic. Happy coding!