Bookmark for Later: Generating Spreadsheets with PHP and PEAR
Looks like the perfect solution for web developers that need to develop dynamic spreadsheets for download.
Can anybody recommend a good tutorial on the reverse of this? Or reading and presenting spreadsheet data with PHP and HTML? I’m not a fan of the “Save as webpage”. Update: PHPReader
You might want to combine or join multiple ranges if the range string is too long, or you want to attempt to make your code easier to work with.
The need to combine multiple range strings is probably most common when selecting a large number of non contiguous cells with VBA.
One way to join multiple range strings is using VBA’s Union function. The syntax for the Union function is:
Union(Arg1 as Range, Arg2 as Range,etc,etc) as Range
The following is an example of using Union. First you declare some range variables, then set the range variables equal to something, and finally combine them using Union.
Tom | 
09 30th, 2004| 
Sub UnionExample()
Dim Rng1, Rng2, Rng3 As Range
Set Rng1 = Range("A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21")
Set Rng2 = Range("C1,C3,C5,C7,C9,C11,C13,C15,C17,C19,C21")
Set Rng3 = Range("E1,E3,E5,E7,E9,E11,E13,E15,E17,E19,E21")
Union(Rng1, Rng2, Rng3).Select
End Sub
What is autocorrect? From microsoft help: “a built-in proofreader that automatically corrects common typos, misspellings, grammatical errors, and incorrect capitalization.”
You can access the autocorrect dialogue box by clicking Tools->Autocorrect Options. For a detailed explanation you can read more at Microsoft’s website.
You can also use autocorrect as a shortcut to save time typing long names, places, or things. For instance if you work at the company “Superior Banking and Investments LLC.” you can set the autocorrect options to correct “mycompany” with “Superior Banking and Investments LLC.”, and every time you type the former in a cell it will change it to the latter.
And a final note about autocorrect for helpdesk employees or someone working in an office with a prankster or two. A lame joke someone may play on a less informed employee is to alter the autocorrect options slightly or altogether. For instance when an employee enters their name “John Doe” it changes it to “Johny Doe” or “Worst Employee Ever!”. Just remember the first place to look in a situation like this is the autocorrect settings.
You can display the formulas in all cells on a worksheet instead of displaying the actual result. For an example you can read the post Print the formulas in a worksheet.
But what if you want to display the formula instead of the result for just one cell? Excel does not have the built in ability to do this, however placing an apostrophe before your formula will get the job done.
Placing the following formula in a cell with a leading apostrophe:
‘=G8/F8
Will result in the formula to be displayed instead of the result.
If you need to create a copy of a worksheet in the same workbook there’s a quicker way than right clicking the tabname and using the “move or copy” dialogue box.
While holding the CTRL key down, left click the activesheets tab, then drag the tab to the location you would like a copy.
Note: This only works for the activesheet. In other words, if Sheet1 is active and you hold CTRL and click Sheet2’s tab, you will invoke the multiple select, not the copy.
You can launch Excel to display fullscreen using VBA by using this line of code:
Application.DisplayFullScreen = True
To exit fullscreen using VBA:
Application.DisplayFullScreen = False
While Excel’s main function is not word processing, many spreadsheets can accumulate a large amount of text, and spell checking a worksheet is usually the last thing people think to of doing.
If your spreadsheets fall in this category, you might try some creative ways to launch Excel’s “SpellChecker” from code as a gentle reminder.
The syntax to launch the Spelling dialog box using the default settings:
expression.CheckSpelling
expression is a reference to the range or object to check the spelling of.
The following is example code to launch the Spelling dialog box before a workbook closes, but before asking to save. The expression tells VBA to check the spelling of all the cells in Sheet1. For the code to work you must place it in the ThisWorkbook code window in VBA.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheet1.Cells.CheckSpelling End Sub
To change a cells background color using VBA you can use the interior.colorindex property. Here’s a couple ways to change the background color of cell A1.
An example using the Range() method:
Range("A1").Interior.ColorIndex = 5
An example using the Cells() method:
Cells(1, 1).Interior.ColorIndex = 15
Need an easy way to determine what number equals what color? Check out Color Reference For Colorindex.
A formula to insert the tabname in a cell in excel:
=MID(CELL(”filename”,A1),SEARCH(”]”,CELL(”filename”,A1))+1,LEN
(CELL(”filename”,A1))-SEARCH(”]”,CELL(”filename”,A1)))
Note: Remove the line break from the formula in Excel.
A formula to insert the filename of the workbook in a cell:
=MID(CELL(”filename”,A1),FIND(”[",CELL("filename",A1))+1,FIND("]“,
CELL(”filename”,A1))-FIND(”[”,CELL(”filename”,A1))-1)
Note: Remove the line break from the formula in Excel.
The LEN function is used to determine the length of the contents of a cell or string.
Example 1
To get the length of a string you place the string inside the LEN function:
=LEN(”StringToFindGoesHere”)
This example would return 20 because there are twenty characters in the string StringToFindGoesHere.
Example 2
To get the length of the contents of a cell (A1 for instance), place the cell reference in the LEN function:
=LEN(A1)
Example 3
And finally, one of the more useful roles the LEN function can serve is validating a range that needs to be a specific length.
In this example a user needs to input 5 digit Zipcodes in range A2:A10. You will notice in the picture that every time a user inputs cell content that does not equal 5 characters the cell color changes, making it very easy to audit.
This is achieved by using the LEN function with conditional formatting, here’s how:
1. Select the Range or cell to apply Conditional Formatting to
2. Select Format on the Main Menu
3. Select Conditional Formatting
4. In the dropdown change Cell value is to Formula is
5. Enter =LEN(A2:A10)<>5
6. Click the Format… button and choose the format to apply if the condition is met. I changed the Pattern or background color
7. Click OK, OK
Here’s a snippet of my Conditional Formatting window using LEN
The usual way to locate or find data in a range is to loop through a range applying logic to stop if a criteria is met.
Using the find method may be a quicker and more efficient way to locate data when searching a worksheet using VBA.
While there isn’t an abundance of information on using the find method with VBA, I did stumble upon this great tutorial:
Link:
The Code Net - The Ultimate Search
To select all the cells on a sheet using VBA you can use the .cells property of the worksheet, without specifying a specific cell.
An example of selecting all cells on Sheet1 using the code name Sheet1:
Sub MySelectAll()
Sheet1.Activate
Sheet1.Cells.Select
End Sub
An example of selecting all cells on Sheet1 using it’s tabname. You can replace “PlaceTabNameHere” with the name of your tab:
Sub MySelectAll2()
Sheets("PlaceTabNameHere").Activate
Sheets("PlaceTabNameHere").Cells.Select
End Sub
When creating an Excel template you may want to turn off the formula bar to limit unwanted user actions.
If you are using a spreadsheet and the formula bar is missing you may want to turn it back on.
To toggle the formula bar On or Off:
On the main menu bar
1. Click Tools
2. Select Options
3. Select the View tab
4. Removing a checkmark next to the Formula bar option will remove the formula bar. Placing a checkmark next to the Formula bar option will add the formula bar. Click OK.
To do this from VBA take a look at Excel VBA: Turn On Formula Bar - Turn Off Formula Bar
If you are looking to benchmark re-written code or debate “quicker” methods in VBA you can use VBA’s built-in timer. Setting a variable equal to the timer at the start of your code and subtracting this from the timer at the end will give you a good estimate of how long a piece of code takes to run.
Performance may be affected by other programs running or trying to run while you’re macro is active, among other things.
The following example was used to see how long it would take to write the word “test” to cell A1 on Sheet1 a half million times. It took 21 seconds on my machine.
Sub BenchMark() Dim Count As Long Dim BenchMark As Double BenchMark = Timer 'Start of Code to Test For Count = 1 To 500000 Sheet1.Cells(1, 1) = "test" Next Count 'End of Code to Test MsgBox Timer - BenchMark End Sub
You can use the MID function to extract a string from a string. In this example we want to remove the middle three numbers from a social security number, or extract the shorter string 456 from 123-456-7890. We do this with the MID function.
The syntax for the mid function is:
=MID(text, start_num, num_chars)
text is the text to extract from or look in. In this case we would indicate the cell containing 123-456-7890
start_num is the number of characters to count over in the original string to begin extracting. Here we want to start our new string at the 5th character.
num_chars is the number of characters to extract, starting with our start_num. We want to take 3 characters from the larger string starting with our start_num.
Finally, to extract the shorter string 456 from 123-456-7890 we use the function: (this assumes our larger string is in cell A1)
=MID(A1,5,3)
This question was asked by a friend; “How do I save only one worksheet in a workbook with many worksheets?” Technically you can’t, unless you delete all of the other worksheets, however you can copy the tab you want to save to another workbook and save it by it’s lonesome there.
1. Right click on the tab you want to save and select Move or Copy…
2. In the To Book: dropdown menu select (new book)
3. Place a checkmark in the Create a copy box and hit OK
4. This created a new workbook with only your selected sheet in it. You can now go to a newly created workbook and save as you would like. Save or discard your old workbook.
This picture shows text in cell A1 will text wrap turned off, and text in cell A3 with text wrap turned on.
To wrap text in a cell:
1. Right click the cell and choose Format Cells
2. Select the Alignment tab
3. Place a check mark in the box next to Wrap Text and click OK
To set the wrap text property for the entire sheet, select one cell and use the shortcut Ctrl and A to select all the cells, then proceed to format the cells with wrap text.
You may want to run a macro when a cell changes. A popular use of this ability is to have custom code validate a cell after a change is made. It’s easy to do this by using the worksheet objects change event.
In the Visual Basic Editor you must first double click the sheet name where the cell changes that activates the macro. This opens the code window for that sheet object. In this case I wanted to run a macro when a cell in Sheet1 changes.
After opening the code window for the Worksheet you place your code in the Worksheet_Change event. The following example will display a message box if the contents of cell A1 change. First the subroutine fires if any cell changes, then the use of an IF..Then statement will run the code only if cell A1 was the cell that changed based on the If…Then.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "This Code Runs When Cell A1 Changes!"
End If
End Sub
You can place your code directly in the Worksheet_Change subroutine or call another macro from there.
An easy one in Excel is absolute value, or change a negative number to positive. The function for absolute value is ABS()
To get the absolute value of a value in cell A1 enter the following in a blank cell:
=ABS(A1)
