DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

List of functions

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Dec 2005

Posts: 2

Reputation: Duckman is an unknown quantity at this point

Solved Threads: 0

Duckman

Offline

Newbie Poster

List of functions

Dec 8th, 2005

Hi,

I am not much of a programmer, so I was wondering if someone could help me write a VBA function that will go through all the cells of an excel sheet, and if a function exists in that cell, output that function to a text file?

After having the list of functions, I'd also like to know if the function is an excel built in function, a function local to my excel work book, or if it is a function available through a XLA that I've included as an add in...

Is this possible to do? or do I have to manually do it?

Thanks!

•

Join Date: Nov 2005

Posts: 134

Reputation: Yomet is an unknown quantity at this point

Solved Threads: 10

Yomet

Offline

Junior Poster

Re: List of functions

Dec 11th, 2005

Duckman,

Here is a little Sub I typed up that will export all formulas in the current worksheet to a text file. Just paste this Sub into a new module of the current workbook, change the Row and Column ranges to fit your worksheet, select the worksheet you want to export the functions from and run the Sub.

This Sub will list all cells that begins with "=" even if they only contain a formula like =A1/$F$3 but at least it's a beginning and you can probably refine it as you see fit.

Have fun

Yomet

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Public Sub ExportFunctions()
   Dim outFile As Integer
   Dim RowVar As Integer
   Dim ColVar As Integer
 
   On Error GoTo SubExit
 
   outFile = FreeFile
   Open ActiveWorkbook.Path & "\Functions.txt" For Output As #outFile
'Change the indexes for your own Column range from 1 to 256 - in English from "A" to "IV"
   For ColVar = 1 To 10
'Change the indexes for your own Row range from 1 to 65536
      For RowVar = 1 To 10
'Change "Sheet1" to reflect your own worksheet
         If Left(Worksheets("Sheet1").Range(Cells(RowVar, ColVar), Cells(RowVar, ColVar)).Formula, 1) = "=" Then 'This cell contains a formula
            Print #outFile, "Row " & RowVar & ":Col " & ColVar & " - " & Worksheets("Sheet1").Range(Cells(RowVar, ColVar), Cells(RowVar, ColVar)).Formula
         End If
      Next
   Next
 
SubExit:
   Close #outFile
End Sub
Public Sub ExportFunctions()
   Dim outFile As Integer
   Dim RowVar As Integer
   Dim ColVar As Integer
   
   On Error GoTo SubExit

   outFile = FreeFile
   Open ActiveWorkbook.Path & "\Functions.txt" For Output As #outFile
'Change the indexes for your own Column range from 1 to 256 - in English from "A" to "IV"
   For ColVar = 1 To 10
'Change the indexes for your own Row range from 1 to 65536
      For RowVar = 1 To 10
'Change "Sheet1" to reflect your own worksheet
         If Left(Worksheets("Sheet1").Range(Cells(RowVar, ColVar), Cells(RowVar, ColVar)).Formula, 1) = "=" Then 'This cell contains a formula
            Print #outFile, "Row " & RowVar & ":Col " & ColVar & " - " & Worksheets("Sheet1").Range(Cells(RowVar, ColVar), Cells(RowVar, ColVar)).Formula
         End If
      Next
   Next

SubExit:
   Close #outFile
End Sub

•

Join Date: Dec 2005

Posts: 2

Reputation: Duckman is an unknown quantity at this point

Solved Threads: 0

Duckman

Offline

Newbie Poster

Re: List of functions

Dec 12th, 2005

•

Originally Posted by Yomet

Duckman,

Here is a little Sub I typed up that will export all formulas in the current worksheet to a text file. Just paste this Sub into a new module of the current workbook, change the Row and Column ranges to fit your worksheet, select the worksheet you want to export the functions from and run the Sub.

This Sub will list all cells that begins with "=" even if they only contain a formula like =A1/$F$3 but at least it's a beginning and you can probably refine it as you see fit.

Have fun

Yomet

Thanks Yomet!!

I was wondering, if I wanted to take this a step further, and figure out a list of embedded functions, how I could parse this text file? I mean, if there is a line that is =IF(ISBLANK(A1)), I would need it to recognize IF and ISBLANK as functions, and recurse as necessary...

One last step I'd need is to determine what module the function is part of - either VBA, a local module, or part of an XLA that I've added as an addin - is there any function that would tell me this?

Thanks!

•

Join Date: Nov 2005

Posts: 134

Reputation: Yomet is an unknown quantity at this point

Solved Threads: 10

Yomet

Offline

Junior Poster

Re: List of functions

Dec 12th, 2005

Duckman,

I tried to find a way of doing this easily but there is nothing available through the Excel interface, at least not that I can find, so I guess the only way of really doing this is to create a real parser for the Excel functions. Which is more than I am willing to do for now...

However, I did start through an Access project - why Access? Because that way you can add functions to a table and use it to store the functions as well as their location in. All in order to make your bogus parser smarter.

What I found was that it is easy to parse through the output of my original sub to find functions (they are all followed by "(") but it is very time consuming to enter all the functions and make the find algorithm even fairly smart.

If you want what I have done so far I can upload it for you but it is not ready by a long way. Actually it is more like a start than anything else but it could point you in the right direction.

If anyone knows how to list all functions available in Excel (or Word or Access) through a program please tell me since it might help.

Have fun and keep coding

Yomet