Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]

Formulae and Other VBA Issues.
This page contains help on Excel Formulae and other related items.



Array Formule Explained
Calculate months in a quarter
Formula for counting number of fortnights
IF Statements and IF Functions explained
Installation file SKU011.cab could not be found
Numbers greater than 15 Characters
Sumproduct Explained

Sum by Colour / Color
Transfer data from Excel to Access
Transfer data from Access to Excel
Prevent users from opening another workbook
Searching and Sorting in VBA
 

Array Formulae/Formulas Explained:
[Back to Menu List]
Need help with array formulae? MVP Bob Umlas has an excellent site for array formulas and lots of tips and tricks..

http://www.emailoffice.com/excel/arrays-bobumlas.html


Formula for counting number of fortnights.
[Back to Menu List]
Array enter (control + shift + enter):
"=SUM((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(B1))))=1)*1)+SUM((DAY(ROW(INDIRECT(VALUE(A1)&":"&VALUE(B1))))=15)*1)"
Where your start and end dates are in A1 & B1 respectively for Sum of the Days of the month between the two dates equals one.  ( i.e. the first or fifteenth of the month ) *
Thanks to Wyatt Lemmons



Help with SUMPRODUCT.
[Back to Menu List]
SUMPRODUCT is one of the most versatile functions provided in Excel. In its most basic form, SUMPRODUCT multiplies corresponding members 
in given arrays, and returns the sum of those products. The link discusses the classic use of SUMPRODUCT, how creativity and inbuilt flexibility
has enabled it to evolve into a far more useful function. A great introduction to SUMPRODUCT.
 
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
Why do some SUMPRODUCT formulae contain "--" in the formula?
For example have a look at the following two formula, which product the same result, both using SUMPRODUCT
=SUMPRODUCT(--(Jobs!$E$2:$E$1215="AUCK"),--(Jobs!$C$2:$C$1215=S3))
=SUMproduct((Jobs!$E$2:$E$1215="AUCK")*(Jobs!$C$2:$C$1215=S3))
 
So what does the use of the double negative "--" in the formula accomplish and what is the difference between these two formulas and how/why
do they behave differently?  Bob Umlus (MVP Excel L developers list) explains they don't behave differently, and they don't produce different answers.

SUMPRODUCT syntax is a list of numeric arrays. Jobs!$E$2:$E$1215="AUCK" is an array of TRUE;FALSE, which is not numeric.
In the post with the multiplication, this winds up being numeric arrays because TRUE * TRUE = 1, and any other combination is 0, so it winds up 
being a numeric array. 

But 1,214 items are being multiplied together.  The one with the --(...) changes the TRUE;FALSE to --{1;0 ...} which is {1;0,...}, a numeric array.
But without the multiplication, it simply executes more quickly. If this were your only SUMPRODUCT formula, you can't notice the difference,
but if you have 10,000 of these formulas, you would.
 
Or put simply, SUMPRODUCT requires a list of numeric arrays. The arrays shown product arrays of True/False (not numeric).
Putting a "-" negative in front of true/false makes it -1/0. We don't want -1, so a 2nd negative changes this to an array of 1/0.
thanks to Bob Umlas - Excel-L Mailing list
 

 
How to get Excel to work with numbers greater than 15 Characters :
[Back to Menu List]
 

It is possible to make Excel work with more significant figures using an add-in. The one I like best is at
http://digilander.libero.it/foxes/SoftwareDownload.htm, which allows you up to 200 sf. The key limitation to this is that every user of the spreadsheet with high-precision numbers would need to have the add-in installed.  It is also non XL2007 compatible.

 

Before you do anything, consider if you really need the value to be a number. If you are not going to do any maths on it, than it can be formatted as
text instead. Credit card numbers would be a good example of this.

If you absolutely have to use really large numbers then consider the following (with thanks to Doug McNutt). The reason for this limit is it's not Excel's
design and it's not magic.


It's the IEEE 64 bit floating point standard that chips, Intel and others, have been using for a long time. Excel uses floating point because nobody would
be happy with the speed of base 10 arithmetic. The IEEE standard uses 52 bits for the significant digits of a number, 1 for sign and the remaining 11
for a power of two that the 52 bit value is multiplied by.

That translates to numbers between 10^-307 to 10^307 with 15, or so, decimal digits of precision. The actual precision depends on the number being
 represented. Numbers like 1/2 or 1/2048 come out exact. Numbers like 1/10 do not in the same way that 1/3 can't be expressed exactly in base 10.
Almost always, 15 digits is quite enough.

 

The problems occur with things that are not really numbers. Telephone numbers with country codes and no internal punctuation, for instance.
Excel should probably recognize impossible input and generate an error or automatically prepend a ' (single quote) character automatically. That will
ask Excel to treat such numbers as TEXT. But you can do that yourself or you can format the cell as TEXT before
you enter the number, for example '937205992294200102.


 
IF Statements and IF Functions explained :
[Back to Menu List]

Need help with Excel's IF functions, looking at one of these links below may help you:

Introduction to IF Statements in Excel:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225

Excel IF Statements, Part II: Nesting Functions:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242

Demystifying the Use of the IIF Function in VBA:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=799

Other links on using the IF Function:
http://spreadsheets.about.com/od/excelfunctions/a/if_funct_hub.htm
http://www.techonthenet.com/excel/formulas/if.php
http://www.ozgrid.com/News/ExcelLogicalFunctionsIfAndOrVBARangeNames.htm#excel

Sum by Colour / Sum by Color
[Back to Menu List]

I am often asked "Can I Sum by Colour/Color in Excel" or "How do I Sum by Colour/Color in Excel".  I have attached a workbook for download that demonstrates how you can sum by colour/color using either the cell's interior colour, or the font colour.  This requires VBA coding to work though as it is not a standard Excel feature.  This workbook will allow the user to colour / color a cell and then sum all values that match that colour.

Download Sample Excel File Here

or view code without downloading sample file:

=SUMBYCOLOR(H1:H289,6,FALSE) as an example of the actual formula.
Where the criteria are "=sumbycolor(Range,ColorIndexID,False = Use Interior Color OR True = Use Font Color)

Put the UDF below in a code module in the VBE.

' --------- START CODE------------------------------------------
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
   Optional OfText As Boolean = False) As Double '
' This function return the SUM of the values of cells in ' InRange with
a background color, or if OfText is True a ' font color, equal to
WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
   If OfText = True Then
       OK = (Rng.Font.ColorIndex = WhatColorIndex)
   Else
       OK = (Rng.Interior.ColorIndex = WhatColorIndex)
   End If
   If OK And IsNumeric(Rng.Value) Then
       SumByColor = SumByColor + Rng.Value
   End If
Next Rng

End Function

' --------- END CODE------------------------------------------


Formula to calculate months in a quarter.
[Back to Menu List]

"=N(MONTH(A1)-INT((MONTH(A1)-1)/3)*3)"

Thanks to Bob Phillips (Excel-L List)


How do I transfer data between MS Access and MS Excel?
[Back to Menu List]

I am also often asked "How do I move data from Access to Excel, or Excel to Access".  There are numerous ways of doing this, however I like to use the recordset method as it is far more reliable than the 'transferspreadsheet' method, which is version dependent and often buggy (well in my experience at least).  I have attached a workbook for download that demonstrates how you can move data between Access and Excel.  In reality I use both, but for ease of understanding I have put the techniques into two seperate zip file. One will download data from Access to Excel, based on a set of parameters choosen by the user.  The other zip file will populate an Access table with data from Excel.

Download example of Excel to Access data transfer Here

Download example of Access to Excel data transfer Here

There are many ways of doing this, this is just one method that has proven to be very reliable for me in a professional / corporate environment.  Also see the Access Page for a code sample of pulling the data from Excel using Access as the master.

If none of these samples help you out I suggest you try the link below which details the pros and cons of all methods of using code to transfer data between Access and Excel:
http://www.zmey.1977.ru/Access_To_Excel.htm#a1


Prevent users from opening another workbook in the same application of Excel.
[Back to Menu List]

Put this code in the ThisWorkbook Sheet Module.  In practice I have found it doesn't work 100% of the time and sometimes you can open workbooks in the same app, but it works often enough to prevent too many foul ups.
'=========================================================
Option Explicit
Private WithEvents objXLApp As Excel.Application
'---------------------------------------------------------
Private Sub Workbook_Open()

On Error Resume Next

Set objXLApp = ThisWorkbook.Application

''' Do your other stuff on open here

End Sub

'---------------------------------------------------------------

Private Sub objXLApp_NewWorkbook(ByVal Wb As Workbook)
   Dim objNewApp As Excel.Application
   Dim lngVisCount As Long

   Wb.Close False
   If MsgBox("You can't open a new workbook in this instance of Excel." _
           & vbLf & vbLf & _
           "Open a new instance of Excel?" & vbLf & vbLf & _
           "* Choose 'Yes' to open a new instance of Excel" _
           & vbLf & "* Choose 'No' to NOT open a new workbook", _
           vbYesNo Or vbQuestion) = vbYes Then
       Set objNewApp = New Excel.Application
       objNewApp.Visible = True
       objNewApp.Workbooks.Add
       objNewApp.UserControl = True
   End If
   Application.EnableEvents = True

End Sub

'---------------------------------------------------------------

Private Sub objXLApp_WorkbookOpen(ByVal Wb As Workbook)

   Dim objNewApp As Excel.Application
   Dim strName As String

   strName = Wb.FullName
   If ThisWorkbook.FullName <> strName Then
       Wb.Close False
       If MsgBox("You can't open the workbook in this instance of Excel." _
               & vbLf & vbLf & _
               "Open in a new instance of Excel?" & vbLf & vbLf & _
               "* Choose 'Yes' to open a new instance of Excel and load the workbook" _
               & vbLf & "* Choose 'No' to NOT open the workbook", _
               vbYesNo Or vbQuestion) = vbYes Then
           Set objNewApp = New Excel.Application
           With objNewApp
               .Visible = True
               .Workbooks.Open strName
               .UserControl = True
           End With
       End If
   Else
       Application.EnableEvents = True
       ' This workbook, so OK to open
       Exit Sub
   End If

   Application.EnableEvents = True

End Sub

'====================================================================


VBA Dev Handbook PDF link (Searching and Sorting in VBA).
[Back to Menu List]

Great Link for sorting and search in Excel and VBA.
http://www.developershandbook.com/Downloads/1951c04.pdf


When Office 2003 (Excel) couldn’t find file SKU011.CAB:
[Back to Menu List]

If you get this message "A required installation file SKU011.cab could not be found" here is how you can fix this issue and repair office 2003

Firstly: Open up the registry and modify the entry as per the instructions below

Goto "Start" > Choose "Run" > Type "regedit" to open the registry editor.  Then use the navigation pane to locate the following:
regedit -> [HKEY_LOCAL_MACHINE] -> [SOFTWARE] -> [Microsoft] -> [Office] -> [11.0] -> [Delivery] -> {90000409-6000-11D3-8CFE-0150048383C9} -> [CDCache] change to [0] …. Done!

Now download the following zip file here (which contains PA561401.cab, YT561401.cab, ZS561401.cab) Unzip the file and save the .cab files (as they are) in the following folder location: "C:\MSOCache\All Users\90000409-6000-11D3-8CFE-0150048383C9 file"

If you cannot open zipped files then get the individual files here:
PA561401.cab
YT561401.cab
ZS561401.cab

Also see: http://hkmicc.wordpress.com/2006/09/25/office-2003-sku011cab-problem/ if you need more help/advice

If the download from this site is not available then try this alternative download location:
http://www.knights-templar.com/FRONTPAGE2003/ZS561401.CAB
http://www.knights-templar.com/FRONTPAGE2003/PA561401.CAB

http://www.knights-templar.com/FRONTPAGE2003/YT561401.CAB


Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]