copy

Sunday, 16 February 2014



Quick Excel-VBA Functions Help (Add-In)


Introduction



This post can be considered as special, since it’s the first time that I upload an add-in that wasn’t developed by me, but by a friend of mine. Petros Chatzipantazis is the founder/owner of Spreadsheet1, a site that deals with Excel and VBA programming. On his site you can find a lot of articles, add-ins, as well as various tutorials about Excel and VBA.

Below you will find an Excel add-in that will help you navigate to the numerous Microsoft's online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice. Note that the text below was copied from Petros’ site.



Add-in description



By using the most suitable functions in your formulas, you will get the most accurate results possible, plus your Excel models will be easier to maintain and audit.

Questions:
  • Are you searching for a new function to simplify your formulas?
  • Are you wondering, if a function is backwards compatible with previous Excel versions?
  • Are you looking for translated function help (besides English)?
  • Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook?

Facts:
  1. There are over 600 Excel & VBA functions in Office 2013.
  2. Excel functions have been translated in 16 languages.
  3. Microsoft offers over 20,000+ function help webpages in 50+ languages.

So, how to navigate fast among so many help pages? The answer:
  • This ribbon add-in will help you navigate to Microsoft's online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.
  • The add-in is unlocked and requires enabled macros.
  • The add-in works with Excel 2007, 2010, 2013 (both 32 & 64-bit Office).
  • License: Creative Commons Attribution-ShareAlike CC-BY-SA (commercial use allowed).
Quick Excel-VBA Functions Help - Description

Additional information:
  1. New functions are being added in Excel's library with every new Microsoft Office launch. The new functions can be used in the current version of Excel, but are incompatible with earlier Excel versions.
  2. Some Statistical functions have been renamed so that they are more consistent with the function definitions of the scientific community and with other function names in Excel.
  3. If backward compatibility is required, you can run the Compatibility Checker, so that you can make the necessary changes in your worksheet to avoid errors.
  4. An icon next to a function name indicates the Excel version in which the function was first added or renamed (see the picture above).


VBA code



Here is the VBA code of the add-in. For more information please visit the corresponding page on Spreadsheet1 site.

Option Explicit

' (c) www.spreadsheet1.com
' ------------------------
' License: Creative Commons Attribution-ShareAlike CC-BY-SA
' http://creativecommons.org/licenses/by-sa/4.0/legalcode

' Please feel free to remix, tweak & build upon this work, even for commercial purposes, as long as:

' 1) www.Spreadsheet1.com & www.RibbonCommander.com are credited.
' 2) New creations are licensed under identical terms.
' 3) Add-in is freely distributed ** NOT intended for sale **

Sub CategoryBtn_OnAction(control As IRibbonControl)

    Dim sLink As String
    
    On Error GoTo ErrHandler
    
    sLink = "http%3A%2F%2Foffice.microsoft.com%2Fen-us" & _
            "%2Fexcel-help%2Fexcel-functions-by-category-HA102752955.aspx%23_Toc3093067" & _
            Format(CStr(5 + Val(control.Tag)), "00")
    
            Application.ThisWorkbook.FollowHyperlink sLink
Exit Sub
    
ErrHandler:
    MsgBox "The function category webpage" & vbCrLf & _
           "cannot be launched due to a web error or" & vbCrLf & _
           "due to a temporarily unavailable service.", vbCritical, "Functions 2013 Demo"
End Sub

Sub NavigateToWebPage_OnAction(control As IRibbonControl)

    On Error GoTo ErrHandler

    Application.ThisWorkbook.FollowHyperlink control.Tag
    
Exit Sub

ErrHandler:
    MsgBox "The function webpage cannot be launched due to a web error" & vbCrLf & _
           "or due to a temporarily unavailable service.", vbCritical, "Functions 2013 Demo"
End Sub

Sub About_click(control As IRibbonControl, id As String, Index As Integer)

    On Error GoTo ErrHandler

    Select Case Index
        Case 0
            Application.ThisWorkbook.FollowHyperlink "http://www.spreadsheet1.com/excel-2013-translated-functions-free-addins.html"
        Case 1
            Application.ThisWorkbook.FollowHyperlink "http://www.ribboncommander.com"
        Case 2
            Application.ThisWorkbook.FollowHyperlink "http://www.myengineeringworld.net"
    End Select
    
Exit Sub

ErrHandler:
    MsgBox "The webpage cannot be launched due to a web error or" & vbCrLf & _
           "due to a temporarily unavailable service.", vbCritical, "Functions 2013 Demo"
End Sub



Downloads



Download

The zip file contains two versions of the add-in: the standard English version and the corresponding Greek one. Both files can be opened with Excel 2007 or newer. Please enable macros before using the files.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info