How to write Excel VBA function

If there is no proper function for your purposes, you can always write one by yourself. All you need is a basic understanding of Basic-programming. It is not hard!

In this demonstration we implement macro function for reversing text (e.g. “help”–>”pleh”). Following steps are needed:

  • Add Developer tab to Excel Ribbon bar:
    Excel 2011 for Mac: Select click gear in the Ribbon’s right edge and select Ribbon Preferences…
    Excel 2010 for Windows: Right clickon the Ribbon and select Customize Ribbon…

    –> Make sure that the Developer-Tab is selected in the Tab list
  • Select Developer-Tab
  • Start Excel’s Visual Basic -editor
  • Add new module by opening context menu from VBAProject and selecting Insert –> Module
  • Write following code to Module1
    Function TransposeText(txt)
        Dim returntxt
        returntxt = ""
        For i = Len(txt) To 1 Step -1
            returntxt = returntxt + Mid(txt, i, 1)
        Next i
        TransposeText = returntxt
    End Function
  • Now your function is ready to use
  • Go back to the worksheet and test the new function
Here is the video demonstrating this using Excel 2011 for Mac:

Leave a Reply

Your email address will not be published. Required fields are marked *