SharePoint - Design and Customization (pre-SharePoint 2010) Forum

SharePoint - Design and Customization (pre-SharePoint 2010) Forum

Improve performance of Excel and VBA

Tip #1 Isolate repeated formulae and move them to single cells

If possible, avoid the use of the same formula repeatedly in multiple cells by taking in out and using in a separate cell. You can then link all those cells which need to use the formula, to that single cell. So look at your spreadsheet, if you have a vlookup() that gets repeated over a 1000 cells and essentially all of them return the same value, take it out and put it in a single cell.

Tip #2 Reduce dependence on volatile functions
Volatile functions get calculated every time there is a change, even though their own output may not change. Some of the most frequently used volatile functions are : OFFSET(), INFO(), RAND(), NOW(), TODAY(), CELL(), INDIRECT() etc.

A point to note is that a cell will be marked for recalculation if it has a volatile function, even though you may be nested deep within a conditional formula and you expect is to be called rarely. For example, if you have a formula like =if(value1 > value2, rand(),0), the cell will be recalculated even though value1 and value2 may not change. So, wherever possible avoid using volatile functions in your spreadsheets and while writing VBA code, avoid the generous use of Application.Volatile command.

Tip #3 Try placing everything within a single worksheet.
Use references within the worksheet, then within various worksheets of the same workbook and only then to other workbooks. That will reduce the overhead on Excel and allow faster execution of calculations.

Tip #4 Nest if conditions in the order of frequency of occurrence

You may want to visit the IF() conditions and see if they are nested in the order in which they are most likely to be encountered. Nested IF() conditions should be like:
= IF ( most_likely_condition , IF(the_second_most_likely_condition , IF(the_third_most_likely_condition,….), ), )
Why, because the moment the first condition is met, the function will stop further evaluation and promptly return a value rather than having to go on checking if any of the other IF conditions are met. In some cases, an OR condition can be
Fastest finger first you see…

Tip #5 Keep the total area under reference to a minimum
Imagine for a minute that a single formula, say a simple SUM(“range”) function. Expectedly, it references a number of cells to generate an output. If each cell that it references can be given a weight of one, the total weight of the formula would be the number of cells it references. Now imagine a vlookup(). It’s total weight would be the number of cells that are specified as the lookup range in the argument. If you could sum up all the weights of all the formulae in your worksheet, you can get an idea of the amount of resources Excel will have to marshal to keep track of changes. Imagine using an entire column (sum(A:A)) as an argument. On the other hand, if you keep the area under references, like limiting the lookup range for a vlookup or a sum function, to only those many rows or columns that are ever expected to contain data, now or in the future, you’d do a world of good. I was once puzzled by the slow response of one of the dashboards that I was working on which didn’t seem to have too many formulae, only to realize that I had a single formula copied not to 1000 rows (as was needed), but to all the 65536 rows in the spreadsheet. No wonder the spreadsheet responded to my commands with utmost disdain!

Tip #6 Use simple formulae in multiple cells rather than a complicated formula in a single cell
Having said it, in some cases, this may not be advisable, especially when you utilize a large number of such complicated formulae – you may bloat out the spreadsheet and prevent others from understanding what you did once you’re gone!!! But in many other cases, especially when you have volatile functions in your complicated formula, by virtue of point 2 above, you may want to simplify things for Excel by breaking things down into smaller easily digestible pieces for it. That way, Excel may have to perform a much smaller number of calculations.

Tip #7 Use Application.ScreenUpdating in VBA
Use Application.ScreenUpdating = False at the beginning of your VBA code and end it with Application.ScreenUpdating = True. It relieves Excel of it’s duty to update the screen till the time the code is getting executed.

Tip #8 Weigh the use of VBA against using inbuilt Excel formulae
Carefully weigh using VBA code vs. using multiple formulae in excel. Sometimes the VBA code can do the job much faster, on others it can slow thing down. As rule of thumb, if you use VBA – optimize. Like any other language, reuse variables and define data and return types so that you don’t keep VBA from guessing an object’s type at run time.

Additionally if you write your custom VBA functions (also called the UDF or User Defined Function), until unless you are a coding God, there is a strong chance that your function is going to consume more system resources than a comparable in built VBA function. So weigh the trade-off between usability and performance carefully. Given that, also ensure that your own function is called last in a nested IF/OR condition.

Tip #9 Reduce Used Range
The used range can roughly be described as the area that Excel may want to run recalculations over. Now one would rarely a situation where every cell in the spreadsheet contains some value, Excel tries to run calculations only over the area which contains some value and then ignores the rest of the cells. So when resolving a very large set of dependencies, Excel tries to determine which cell may be the last one after which no other cells may have been touched or ‘dirtied’ so that it can stop its recalculations after reaching that cell. Press CTRL+END on your workbook and you will be taken to the last cell of the used range. If you find that you’ve just landed at row 50,000 and column GS, and you know that your data set does not expand that far, go back to the last cell in your data set and delete all the cells to the right and below it. Then save the workbook. That will reduce the used range and should add that extra boost.

Tip #10 Sort Final and Static Data
Often times we shut shop the moment we think we have the formulas working and VBA code running properly. But lookups and many other functions work a lot faster if they have to run on data that has been properly sorted. So before you close that worksheet, take a few seconds to sort that data if it’s not going to change further.

Tip #11 Sort worksheets
.Excel recalculates worksheets in alphabetical order. To improve performance, you can rename the sheets in your workbook in such a manner that the sheet containing only data is the first one, the one with intermediate formulae in the middle and the ones with the final presentation in the last. Use f4 or View->Properties window in VBA editor to change sheet names.

Also if Excel has to resolve dependencies using ‘brute’ force, it will start from the first cell and proceed right downwards. So if everything is in the same sheet, place data first, then intermediate formulas to the right or downwards and the final presentation towards the rightmost bottom.

Tip #12 Use conditional formatting with care

Conditional formats may seem a good choice to present data in a user friendly manner but remember that they are going to be recalculated if the input values change, just like any other formulae in the worksheet. So if you have to use conditional formatting, save it for the dessert – when showing the final result to the user. When using VBA you can use Worksheet.EnableFormatConditionsCalculation =TRUE/FALSE to temporarily turn conditional formatting On and Off.

Tip #13 When using VBA, don’t read/write cell by cell – Read/write in bulk
(Thanks for Jon for the input)
When writing VBA code, reading values cell by cell from a spreadsheet can bog down the speed of execution of the code. A faster way of carrying out processing is to simply read the entire row/column/range into memory by assigning it to a Variant object. You can then carry out operations on the variant object and once done, write it back to the spreadsheet. Here are two examples:

The Wrong Way

The RightWay

The later executes much faster, especially if you, like me, work using an old war horse of a pc 

And while at that, another tip – you don’t have to select cells to access and modify them them. (Range(“A1″).Select is not a necessary step to read value from that cell. (However, when you use the macro recorder, you will often see lines of codes like these.)

Tip #14 If everything fails – Increase RAM and chip power
Sometimes it is better to go back and ask for greater resources. The productivity improvement may well worth the investment.

Recommended readings in this regard are:

http://msdn.microsoft.com/en-us/library/aa730921.aspx
http://www.decisionmodels.com
http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx
http://msdn.microsoft.com/en-us/library/aa140058.aspx
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Uploading Excel Data into Sharepoint Lists

How to bulk upload and synchronize data into SharePoint using the Excel Add-in and SharePoint Designer Workflows

Overview
Provide the ability for selected individuals who need to continue to maintain data in Excel and also share the data with users via SharePoint taking advantage of all SharePoint’s built-in features for lists.
This solution provides the ability for Excel users to select data stored in their Excel spreadsheet and synchronize the data to a custom SharePoint list eliminating the sometimes redundant and time-consuming process of entering each item individually.
Screenshot from a table in Excel spreadsheet:
This scenario is part of an overall solution that takes advantage of SharePoint’s out-of-the-box features including SharePoint Designer workflows and custom lists to provide an online database for event tracking, a database of subject matter experts, workflow processes for resourcing, and incorporating scoring data imported from pre-populated reports downloaded in Excel format.  
In addition the solution uses the new document sets in SharePoint 2010 to allow user to upload and share supporting files and tag supporting files at a folder level eliminating redundant input.
Configuring SharePoint

• Create a new custom list to serve as a public list that will display the data to end-users.
• Create a new custom list to serve as an import list supporting the import workflow process.  This list is created directly from the Excel spreadsheet using the Excel Add-in’s Publish and allow Sync command (more later on this).
• Create a new site column named BulkImportID and that will be added to both the Import List and Public list.  
• Create a new SharePoint Designer workflow to handle the import process.  Set the workflow to execute when a new item is added to the Import List. 
• Add logic to the workflow detect whether a new item should be added or whether the item has been previously added to the public list requiring only an update to the list item.  The purpose of the BulkImportID is to be used to evaluate the workflow condition.
• Using the create item function in the workflow populate the public list with the appropriate values from each newly added item in the import list and set the BulkImportID for each added list item. Delete each list item in the import list using the Delete Item function when the workflow has succeeded for the item.

Installing and Configuring the Excel Add-in

• Install the Excel Add-in to the user’s desktop or laptop (individuals contributing data to the system) This provides the mechanism to synchronize the data from the user’s Excel spreadsheet to the Import List in SharePoint.
• Save the spreadsheet as Excel 2003-2007 format as described in the Excel Add-in configuration instructions.  You can also create a copy of the original to support the import process to maintain the original’s advanced features.   
• Configure the Excel spreadsheet for the Excel Add-in on the Excel spreadsheet by completing the configuration steps.  Create a new table in Excel with your data and use the Publish and allow sync command to deploy your Import List for the first time.  Optionally add a new worksheet to each spreadsheet where the  user can copy in the specific data they need to import to SharePoint preserving the source worksheets.

Advantages

• Users can continue to use their Excel spreadsheets for advanced computations, offline access, and custom formatting while also being able to share the data via SharePoint. 
• Users can bulk-import pre-populated spreadsheets and reports provided by other systems into SharePoint. 
• Custom filtered views including progressive filtering can be created and shared via SharePoint not provided in Excel auto-filtering. 
• SharePoint Designer workflows provide a non-code mechanism to both import new items into the Public List and also detecting existing items for performing updates.   

Using the Excel Add-in:

Deploy Excel Add-in to selected users who will be importing data from Excel to SharePoint
Brief Description This add-in works with Excel 2007 to allow you to synchronize data in a table with a list on a SharePoint site.
Download from MSDN http://www.microsoft.com/downloads/details.aspx?FamilyId=25836E52-1892-4E17-AC08-5DF13CFC5295&displaylang=en
Configure Excel spreadsheets for Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists
Summary: In Microsoft Office Excel 2007, the ability to synchronize the data between a table and a list in Microsoft Windows SharePoint Services is deprecated. This article describes an add-in that enables you to update the information in a SharePoint list from Excel 2007.
Configuration Instructionshttp://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx#Office2007SynchronizeSharePointListfromExcel_Synchronizing

Important considerations when using the Excel Add-in.

• Create your custom “Import List” directly from the Add-in using the Publish and allow sync command in the Table Tools-Design tab of your Excel spreadsheet provided by the Excel Add-in.  You can add your own fields to the Import List later and synchronize with Excel. 
  Think carefully about what fields you want as required fields and the desired order in your spreadsheet before provisioning the Import List since the field order is difficult to re-arrange once the Excel spreadsheet is connected to SharePoint. 
• Take advantage of the ability for the Excel spreadsheet to incorporate pick-lists from the columns in your SharePoint Import list. 
• There is a limit of 6 fields if I remember correctly and you are not able to provide for multiple selections but very usefull for maintaining data consistancy and case where selecting users from AD would be usefull.
• A common confusion I have seen on MSDN for users implementing the add-in is the Publish and allow Sync command in the Excel toolbar.  Some users attempt to select this command to sync the data with SharePoint.  This command is only used to provision the initial list in SharePoint bound to the Excel table and not used for updates.  For updates right-click anywhere on the Excel table and use the additional features added to the context-menu to sync data (see screenshot above)

Conclusion
There are circumstances when users need to continue to use existing Excel spreadsheets and adding each data item to SharePoint individually would be both redundant and time-consuming.  Using the Excel Add-in in combination with custom Import Lists and SharePoint Designer workflows provide one mechanism for bulk-uploading data from Excel to SharePoint. 

Excel Developer Center | How-to Videos, SDKs, References | MSDN

Excel Developer Center | How-to Videos, SDKs, References | MSDN

Classes In VBA

Classes In VBA

Class Basics

For illustration, let's adapt the Employee Type described above into a class. First, insert a class module into your VBProject (from the Insert menu in the VBA editor). Name the class CEmployee (it is common practice to use a 'C' as the first letter of a class). There are three properties to create: Name, Address, and Salary. These values will be stored in private variables within the class. Since they are declared Private, they cannot be accessed outside the class module.

Private pName As String
Private pAddress As String
Private pSalary As Double

Next, we need to declare Property procedures to allow these variables to be read from and written to. This is done with Property Get and Property Let functions (or Property Set for object type variables).

''''''''''''''''''''''
' Name property
''''''''''''''''''''''
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(Value As String)
pName = Value
End Property

''''''''''''''''''''''
' Address property
''''''''''''''''''''''
Public Property Get Address() As String
Address = pAddress
End Property
Public Property Let Address(Value As String)
pAddress = Value
End Property

''''''''''''''''''''''
' Salary property
''''''''''''''''''''''
Public Property Get Salary() As Double
Salary = pSalary
End Property
Public Property Let Salary(Value As Double)
pSalary = Value
End Property

The Get procedure is used to return a value out of the class, and the Let procedure is to put a value into the class. Note that the return data type of the Get property procedure must be the same data type as the (last) parameter to the Let property procedure. Otherwise, you'll get a compiler error.

Because Property procedures can contain any code you like, the Let Salary procedure can be written to exclude non-positive values.

Public Property Let Salary(Value As Double)
If Value > 0 Then
pSalary = Value
Else
' appropriate error code here
End If
End Property

A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be withholding tax, which is calculated when it is called. E.g.,

Property Get WithholdingTax() As Double
WithholdingTax = calculated value
End Property

Finally, the class can contain methods, such as a PrintPaycheck procedure.

Public Sub PrintPaycheck()
' actual code to print check
End Sub

Now that we have defined the class, we can create objects based on the class. In a standard code module, declare a variable of type CEmployee.

Dim Emp As CEmployee

Then, Set that variable to a new instance of the class and assign some property values.

Set Emp = New CEmployee
Emp.Name = "Joe Smith"
Emp.Address = "123 Main Street"
Emp.Salary = 40000

Automatically Expand a Named Range in Excel | Excel Semi-Pro

Automatically Expand a Named Range in Excel | Excel Semi-Pro

Inserting a row inside this range will automatically expand the reference for the Named Range, but normally a user would add data to bottom of the table in the first empty row.

My solution is event based. I write a simple subroutine.

Sub ShiftRangeAndRename()
Const n As String = "myFoodData"
Dim rng As Range
Set rng = Range(n).CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Name = n
End Sub

Then reference it from the deactivate routine on the MyData worksheet.

Private Sub Worksheet_Deactivate()
Call ShiftRangeAndRename
End Sub

When a user goes to the MyData worksheet and updates data and returns to the main worksheet, the worksheet deactivate routine calls the routine to update the range reference and its associated name. This also works if they are deleting data, but that’s not common in this type of situation.

Related posts:

1. Put an OFFSET Formula Inside a Named Range
2. Become the OFFSET Function and Tell a Short Story
3. Expand Menus in Excel 2003
4. Name and Select a Range with the Name Box in Excel
5. The VLOOKUP Function in Excel

Defined names to auto update graphs

How to use defined names to automatically update a chart range in Excel