Wednesday 30 January 2013

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:



No comments:

Post a Comment