How many sheets do I have in my workbook?

Saw this question here:

Great job as usual.  I'm sorry that I did not make myself clear.  I
have several workbooks that contain several sheets each and while I do
have tabs at the bottom, I wanted to see a number perhaphs at the
bottom representing how many sheets the workbook had upon opening it.
If that's not possible, that's okay.  I really appreciate the
shortcut.  It's going to come in very handy. Thanks

The simple answer is to create a UDF, which reads:
Public Function NumSheets()
NumSheets = ThisWorkbook.Sheets.Count
End Function

Then, in a cell, type =NumSheets()

Sometimes it is a trivial answer.

Generating a Word Count of an Excel Workbook

That is the question at this forum:

Someone suggests: Save as a CSV file.
Another gives some VBA code he created. See inside.

How would I do it? Well, VBA is certainly one way to go, but why do this when a simple formula will accomplish the same thing? If you make it a macro, then you need to enable macros for that spreadsheet, and some folks are wary of macros.

How do you get a word count of words in an individual cell? Well, a similar way to the VBA approach, the annoying kludge of substitute of space for null followed by a difference in length. There are other ways to avoid this kludge, but for now we will not:

That is, imagine the text is in A1.
The formula in A2: =TRIM(A1)
This will remove any spaces before and after the text, as well as runs of spaces inside the text.
A3: =SUBSTITUTE(A2, " ", "")
A4: =1 + LEN(A1) - LEN(A3)
Since we replaced all " " with nothings, each replacement will make it one character less. The difference between these two lengths are the number of spaces. That number + 1 is the number of words.

That is for a single cell. The combined formula would be:
=1+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

Now what about for a range of cells? We just use an array formula, and take the SUM. Also, if the cell does not have text, we don't want to count it. For the range A1:D4, type the following in some other cell:
=SUM(IF(ISTEXT(A1:D4), 1+LEN(TRIM(A1:D4))-LEN(SUBSTITUTE(TRIM(A1:D4)," ","")), 0))
and press Ctrl-Enter. Thus will give you a count of all words in that range.

How do we do it for all cells on a given worksheet? Make the reference be 1:1048576.
=SUM(IF(ISTEXT(1:1048576), 1 + LEN(TRIM(1:1048576)) - LEN(SUBSTITUTE(TRIM(1:1048576), " ","")), 0))

Of course, this would not work, because Excel would run out of resources before calculating that value, and will give us an error message to that effect. Besides, if it encompasses the entire spreadsheet, you will get a circular reference. But use a smaller range and it should work fine.

Which is better, the VBA or this? I don't know.

Calling Python from an Excel UDF

Just to start of this blog, I'll post some interesting code I found, which lets you run python within VBA. I got this from here. I'll comment on it afterward.

Global sc As New MSScriptControl.ScriptControl

Public Function os_getcwd()
  sc.Language = "python"
  sc.ExecuteStatement ("import os")
  os_getcwd = sc.Eval("os.getcwd()")
End Function

With this you can set your Excel formula to =os_getcwd()

For me it returns "C:\Documents and Settings\Administrator\My Documents",
which I needed to know at the time so I didn't have to screw around with
the ever annoying pythonpath.

Read it all. We can do other things in it. For example, I would suggest the following:

sc.ExecuteStatement ("x = 7 + 6")
os_getcwd = sc.Eval("x")

and we would get 13. This is just a proof of concept. Practical examples which make use of this would be useful may be forthcoming if I am interested enough. In particular, the memory this retains plus the built in set and list formers may lead to some very powerful built-in functions. Clever!

One such example: How do I center text in a string, by padding with spaces before and after, such that the entire length of the string is 100. I could take the string length, figure out have many spaces to append before, how many after, and so on. Or else I could use this UDF:

Public Function CenterText(text, width)
text = Chr(34) & text & Chr(34) ' add quotes around "text"
sc.ExecuteStatement ("import string")
sc.ExecuteStatement ("x = string.center(" & text & ", " & width & ")")
CenterText = sc.Eval("x")
End Function

See how I first build the command using string concatenation, and then run it.
I don't actually need to store in a temporary in the meantime. I could write:

Public Function CenterText(text, width)
text = Chr(34) & text & Chr(34) ' add quotes around "text"
sc.ExecuteStatement ("import string")
CenterText = sc.Eval("string.center(" & text & ", " & width & ")")
End Function

And of course, the "import string" should come much earlier, on the load of the workbook, so that we don't need to import every single time.

Is there anything you would like to accomplish in Excel, but don't know how? Ask away!