[Trick] Microsoft Excel - Copy, Cut, Paste and Paste feature Disabled

Issue

How to solve the issue when your Microsoft Excel’s Copy, Cut, Paste and Paste Special feature are disabled?

Answer

Troubleshooting steps: 1. Open MS Excel 2. Press Alt + F11 to get into Microsoft Visual Basic

Number to Word with MS-EXCEL (USD sample)

Continuing the previous post on how to spell number to Indonesian Words, here Me tried to share on how to do it in English.

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

    Dim Dollars, Cents, Temp

    Dim DecimalPlace, Count

    ReDim Place(9) As String

    Place(2) = " Thousand "

    Place(3) = " Million "

    Place(4) = " Billion "

    Place(5) = " Trillion "

    ' String representation of amount.

    MyNumber = Trim(Str(MyNumber))

    ' Position of decimal place 0 if none.

    DecimalPlace = InStr(MyNumber, ".")

    ' Convert cents and set MyNumber to dollar amount.

    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

     

Number to Word with MS-EXCEL (IDR sample)

This is the function to make my life easier (in the Excel Term though :^_^):

Function Terbilang(n As Long) As String 'max 2.147.483.647

    Dim satuan As Variant, Minus As Boolean

    On Error GoTo terbilang_error

   

    satuan = Array("", "Satu", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Delapan", "Sembilan", "Sepuluh", "Sebelas")

    If n < 0 Then

        Minus = True

        n = n * -1

    End If

    Select Case n

        Case 0 To 11

            Terbilang = " " + satuan(Fix(n))

        Case 12 To 19

            Terbilang = Terbilang(n Mod 10) + " Belas"

        Case 20 To 99

            Terbilang = Terbilang(Fix(n / 10)) + " Puluh" + Terbilang(n Mod 10)

8 levels or more IF function for EXCEL 2003 or below

"the specified formula cannot be entered because it uses more levels than are allowed in the current file format"

For those who actively use Microsoft Excel, may had found the error statement above. It caused by the limitation of nesting level of older version of the application (2003 and below). They only support up to 7 nesting level of a function in one statement.

There are some alternative ways to get around, here's some of them:

1. Upgrade your EXCEL to 2005 or above.
2. If you had a macro programming skill, create a function in VBA.
3. or, you can split the function up to some parts.

Here, we'll try the last option, Split up the function.

I have this function that had the nesting level issue:

=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,if(H6<99.5%,4%,0%)))))))))

as the limitation goes, than the function will only work if we cut the red part off:

...if(H6<99.5%,4%,0%)...

But that won't solve the problem. So, I came up with this way.

• Take the 8th level of nesting out (let's say use the C2 cell) and exchange it with "FALSE" value, so the function will look like this:

=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,FALSE))))))))

• Put the 8th IF to another cell and put another IF statement before it to check the output value of the IF statement above (from the C2 cell). If it return FALSE then execute the 8th IF. So the second part of the function will be like this:

=IF($C2=FALSE,IF(H6<99.5%,4%,0%))

• The last part is to choose which output to use. Here we'll use another IF. Check whether the return value of the first statement is FALSE or not. If it is, then use the return value of the second if statement. If not, then just use the value. Here's what it would be like:

=IF($C2=FALSE,$D2,$C2)

There you go! We finally made it. Hope you'll find this article useful. If you want to see the real example of the EXCEL format, I have uploaded it here. Have a good day!