Create and Install an Excel Add-in from VBA -Tutorial

In this video, you will learn how to create and install an Excel add-ins from VBA. Simple copy and paste below code in your VBA project in any module and save it add-ins file.

Option Explicit

Function TAXSLAB(TAXABLEINCOME As String)
If TAXABLEINCOME <= 250000 And TAXABLEINCOME > 0 Then
TAXSLAB = 0
ElseIf TAXABLEINCOME <= 500000 Then
TAXSLAB = 5.2
ElseIf TAXABLEINCOME <= 1000000 Then
TAXSLAB = 20.8
ElseIf TAXABLEINCOME <= 5000000 Then
TAXSLAB = 31.2
ElseIf TAXABLEINCOME <= 10000000 Then
TAXSLAB = 34.32
ElseIf TAXABLEINCOME <= 20000000 Then
TAXSLAB = 35.88
ElseIf TAXABLEINCOME <= 50000000 Then
TAXSLAB = 39
Else
TAXSLAB = 42.74
End If
End Function

      Function N2W(ByVal MyNumber)
          Dim Dollars, Cents, Temp
          Dim DecimalPlace, Count
          ReDim Place(9) As String
          Place(2) = " Thousand "
          Place(3) = " Lacs "
          Place(4) = " Crores "
          Place(5) = " Arabs "
          Place(6) = " Kharabs "

          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 <> ""
              If Count = 1 Then
                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
              Else
                If Len(MyNumber) > 1 Then
                    Temp = GetTens(Right(MyNumber, 2))
                Else
                    Temp = GetDigit(Right(MyNumber, 1))
                End If
                If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
                If Len(MyNumber) > 2 Then
                    MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                Else
                    MyNumber = ""
                End If
              End If
              Count = Count + 1
          Loop

          Select Case Dollars
              Case ""
                  Dollars = "No Rupees"
              Case "One"
                  Dollars = "One Rupee"
              Case Else
                  Dollars = Dollars '& " Rupees"
          End Select

          Select Case Cents
              Case ""
                  Cents = ""
              Case "One"
                  Cents = " and One Paisa"
              Case Else
                  Cents = " and " & Cents & " Paise"
          End Select
          N2W = Dollars & Cents & " Only"
      End Function

      '*******************************************
      ' Converts a number from 100-999 into text *
      '*******************************************
      Function GetHundreds(ByVal MyNumber)
          Dim Result As String
          If Val(MyNumber) = 0 Then Exit Function
          MyNumber = Right("000" & MyNumber, 3)
          ' Convert the hundreds place.
          If Mid(MyNumber, 1, 1) <> "0" Then
              Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
          End If
          ' Convert the tens and ones place.
          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

      '*********************************************
      ' Converts a number from 10 to 99 into text. *
      '*********************************************
     Function GetTens(TensText)
          Dim Result As String
          Result = ""           ' Null out the temporary function value.
          If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
              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                                 ' If value between 20-99...
              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))  ' Retrieve ones place.
          End If
          GetTens = Result
      End Function

      '*******************************************
      ' Converts a number from 1 to 9 into text. *
      '*******************************************
      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

Tags:

How do you create Excel add in using VBA?

1. Click the File tab.
2. Click on ‘Save As’.
3. In the Save As dialogue box, change the ‘Save as’ type to . xlam.
4. Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.
5. In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.