Saturday 19 November 2011

Using the Microsoft CDN or Google CDN for jQuery

As we develop new websites and try to introduce more user interactivity and animation effects it will become more frequent that we use some kind of JavaScript library.

Probably the most popular and most well-known is the jQuery library.  This post is going to show you how to link the library to your site.  We’ll also add a tip or two along the way.

For a jQuery tutorial click here for the w3schools page.

Adding the jQuery Library to Your Pages

The jQuery library is stored as a single JavaScript file, containing all the jQuery methods. It can be added to a web page with the following mark-up:

<head>
<script type="text/javascript" src="jquery.js"></script>
</head>

Remember that the <script> tag should be inside the page's <head> section. 

The above method requires you to download the jQuery file (see here) and store it on your server with your site.  Two versions of jQuery are available for downloading: one minified and one uncompressed (for debugging or reading).

Alternatively you can use a Content Delivery Network (CDN).  There are three CDNs available; jQuery CDN (via Media Temple), Google Ajax API CDN and the Microsoft CDN.

Google:
<script type="text/javascript" src=http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js>
</script>


Microsoft:
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js">
</script>

jQuery:
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.min.js”></script>

 

A couple of tips:

Monday 31 October 2011

Variables in CSS File (Dynamic CSS)

Problem: Using CSS Variables/Dynamic CSS;

Firstly, I am aware that using variables in CSS seems to be topic for many a forum and blogs alike, W3C have not to my knowledge implemented variables in full within CSS as it breaks the segregation between Stylesheets and logic/script files (php etc).
Using variables is CSS has been somewhat of a minefield with entire articles and dedicated web pages on the matter. Not that these are bad in anyway, as they are, in the author's opinion, a way of producing the desired result. It is just my opinion which says these methods are, at time, very long winded and are quite 'fatty'.
Most requests for such variables in CSS are based around outputs from database queries, corporate colour schemes was my driving force and seems to be the case for the majority of reasons for the CSS variable request.
What i aim to provide with the solution i found, is a very lean and simple to use way of using variables within CSS styling.

Solution:
I come back and edit this in the near future and give you a working example of my colour scheme mySQL database table, create script and a site i used to generate the perfect tones that i required, but for now, this is the workings of "PCSS" (php CSS file).
  • Create a php file called PCSS.php
  • Paste this code into the file:
1:  <?php 
2:
3: $PCSS = getPCSS();
4: header("Content-type: text/css");
5: echo $PCSS;
6:
7:
8: ?>

  • Create a function called "getPCSS()" - Remember to insert this before the "?>" php closure tag.
  • Within the getPCSS function, write your db extract routine or variables you wish to dynamically use within a CSS file.
  • Save and close the file.
  • Open and edit the file that you wish to include the dynamic CSS within and point a new link to the new PCSS.php file (example: )
  • Thats it!
I said it wasn't going to be difficult, and in my opinion, this is the leanest way of producing the desired result.
The simple "header("Content-type: text/css");" tells the PHP file to return the content as CSS, which means you can have functions, variables, db queries etc within this file, but the return of function "getPCSS()" will pass CSS to the browser.

I could give you a bench mark, but to be honest, the results are subjective to the size of your PCSS.php file, subsequent db queries etc and performance will vary. However, with that said, an average time for my PCSS.php file to query 30 colours from 1 table, build 15 different CSS names/classes for each colour, which is around 1200 LOC, takes around 200ms.

As said, i will give you a working db example using a colour scheme which is the most common reason to require dynamic CSS (I didn't want to remember the 30 different colours and shades HTML colour codes).


If you think my post is good, or have any questions, please feel free to drop me a comment and I will get back to you.

Aaron Harrison - New Blog Contributor

Hello World!

I have recently been working with Matthew Sims over at zypher.co.uk and was asked if I wanted to contribute on their blog. I thought sure, sounds good and its probably long overdue to give back to the web community what I have taken from it over the years.

I have an array of solutions for everything from simple Excel problems/quirks, VBA solutions utilizing Windows APIs, Microsoft Word solutions/automation's, right through to solutions for web development problems which have stumbled many developers.
This means, you should begin to see various posts from myself, as well as the posts from Matt.

Just a heads up, I will be creating a post about a solution I created for "Dynamic CSS" files, i.e using php variables etc within a CSS file. I hope to get it posted today so keep your eyes peeled ;-) (here it is)

Well, enough about me. You should check-out zypher.co.uk, they have services and solutions for many ICT requirements and could be just what your looking for. Matt is a very knowledgeable professional with a vast and dynamic background which brings a wealth of experience.


Hope you find my posts easy to follow, useful and informative. If you have any questions or feedback (please, be gentle), feel free to leave a comment.

Wednesday 31 August 2011

Creating and reading data from text files

It is sometimes useful to keep data in a text file.  For example one of our clients has a resource file which has onOpen and onClose routines that log the current user, the date and time and whether the file was opened read/write or read only.

The code below shows how this can be done.

Sub Create_A_File()
' Put data into a text file
' This routine will create the file if it does not already exist

    Dim strMsg  As String
    Dim strFile As String

    strFile = ThisWorkbook.Path & "myFile.txt"
    ' could be .doc, .txt, .log etc

    strMsg = "Some text could go here"

    ' Open file for output - clears any current data
    'Open strFile For Output As #1
    ' or Open file for append - updates any current data
    Open strFile For Append As #1

    Print #1,                       ' Print blank line to file.
    Print #1, "*****"               ' Print ***** line to file.
    Print #1, strMsg                ' Print a variables value to the text file
    Print #1, Now, strMsg           ' Print date/time and the message, using commas create tab seperated data
    Print #1,                       ' Print another blank line to file.
    Print #1, Application.UserName  ' Print the current user's Excel username
    Print #1, Now                   ' Print the date and time

    Close #1                        ' Close file.

End Sub

Sub GetFromTxt()
' Copy data from one text file to another

    Dim FilePath    As String
    Dim File1       As String
    Dim File2       As String
    Dim File3       As String
    Dim FileData    As String

    Const FileExt   As String = ".txt"

    FilePath = "C:\File\Path\"
    File1 = "File Name 1"
    File2 = "File Name 2"
    File3 = "File Name 3"

    ' Example: FilePath & File1 & FileExt = "C:\File\Path\File Name 1.txt"

    'Open all relevant files
    Open FilePath & File1 & FileExt For Output As #1
    Open FilePath & File2 & FileExt For Input As #2
    Open FilePath & File3 & FileExt For Input As #3

    'Copy each line of the first existing file into the
    'new combined file
    Do While Not EOF(2)
        Input #2, FileData
        Print #1, FileData
    Loop

    'Copy each line of the second existing file into the
    'new combined file
    Do While Not EOF(3)
        Input #3, FileData
        Print #1, FileData
    Loop

    'Close all files
    Close #1
    Close #2
    Close #3
End Sub

Hope that proves useful.

Friday 26 August 2011

Useful VBA file functions - Microsoft Scripting Runtime

Some example functions for finding out the last modified date of a file, copying a file and deleting a file using VBA.

Public Sub File_Last_Modified_Date()
' Use a function to return the last modified date of a file
    MsgBox f_File_Last_Modified_Date("C:\Put\the\full\file\address\here.xlsx")
End Sub

Private Function f_File_Last_Modified_Date(FileName As String) As String
' In the VBE, set a reference to Microsoft Scripting runtime
' Tools -> References... ->
    Dim fso As Scripting.FileSystemObject
    Dim fsof As Scripting.File
    Dim strPath As String

    Set fso = New FileSystemObject
    strPath = FileName
    Set fsof = fso.GetFile(strPath)
    With fsof
        f_File_Last_Modified_Date = .DateLastModified
    End With

    Set fso = Nothing
    Set fsof = Nothing
End Function


Public Sub CopyFile()
' Copy a file, the file cannot be open when the copy is attempted

    Dim SourceFile As String
    Dim DestFile As String

    SourceFile = "C:\Put\the\full\file\address\here.doc"
    DestFile = "C:\Put\the\new\file\address\here.doc"

    FileCopy SourceFile, DestFile
End Sub

Public Sub DeleteFile()
' Delete a file

    Dim FileName As String

    FileName = "C:\Put\the\full\file\address\here.txt"

    ' Check the file exists
    If Dir(FileName) = "" Then
        ' File does not exist
    Else
        ' Delete the file
        Kill FileName
    End If

End Sub

As always, any questions or comments please let us know via the comments section.

Monday 22 August 2011

Application.Filesearch Replacement For Office 2007

If you have ever used completed a search for files using VBA you probably made use of Application.Filesearch.  However, if you’ve updated to Office 2007 or 2010 you may have noticed that Filesearch has been removed… but the (very) old Dir remains, so you can use this instead, an example of which follows:

Sub FileSearch()
    Dim fso As Object
    Dim FileName As String
    Dim strArr(1 To 65536, 1 To 1) As String
    Dim i As Long

    ' Set the directory / filename you are looking for
    Const strDir As String = "C:\Your\File\Path"
    Const SearchTerm As String = "YourFileNameTerm"

    ' Complete the search
    Let FileName = Dir$(strDir & "\*" & SearchTerm & "*.xls")
    Do While FileName <> vbNullString
        ' For each file found load to the array
        Let i = i + 1
        Let strArr(i, 1) = strDir & "\" & FileName
        Let FileName = Dir$()
    Loop

    ' Search within sub-folders
    Set fso = CreateObject("Scripting.FileSystemObject")
    Call RecurseSubFolders(fso.GetFolder(strDir), strArr(), i, SearchTerm)

    ' Tidy up and copy the results to the active worksheet
    Set fso = Nothing
    If i > 0 Then
        Range("A1").Resize(i).Value = strArr
    End If

End Sub

Private Sub RecurseSubFolders( _
    ByRef Folder As Object, _
    ByRef strArr() As String, _
    ByRef i As Long, _
    ByRef SearchTerm As String)

    Dim SubFolder As Object
    Dim FileName As String

    ' Search sub folders
    For Each SubFolder In Folder.SubFolders
        Let FileName = Dir$(SubFolder.Path & "\*" & SearchTerm & "*.xls")
        Do While FileName <> vbNullString
            Let i = i + 1
            Let strArr(i, 1) = SubFolder.Path & "\" & FileName
            Let FileName = Dir$()
        Loop
        Call RecurseSubFolders(SubFolder, strArr(), i, SearchTerm)
    Next
End Sub

Tuesday 12 July 2011

DreamWeaver - while executing onload in _onOpen.htm, the following JavaScript error(s) occurred:

 

DreamWeaverJsError

To fix the above error navigate to one of the file address below:

Windows XP:

C:\Documents and Settings\Administrator\Ap Data\Adobe\Dreamweaver CS5\en_US\Configuration\

Vista:

C:\Users\[Your Username]\AppData\Roaming\Adobe\Dreamweaver CS5\en_US\Configuration\

Windows 7:

C:\Users\[user]\Ap CS4\en_US\Configuration\

And delete the file called ‘WinFileCache-[random numbers].dat’.

Saturday 9 July 2011

“Code execution has been interrupted”

Every now and then, while executing macro routines within Excel you may get the error message “code execution has been interrupted”.  This error message should only appear if you hit “Ctrl+Break” to stop the execution and view the code. But… sometimes it just happens, normally with no apparent reason.

If you hit the “Continue” button, the code will execute for a while, and may even finish, but you may get the same error message again. Sometimes you may even have to click the “continue” button several times to complete the execution of a macro.

This issue caused intermittent problems for me over a long period of time, that was until I found the solution.

To stop this, often re-occurring, problem all you have to do is add one line of code “Application.EnableCancelKey = xlDisabled” as the first line of your macro.

This should fix the problem and enable you to execute your macro code successfully without getting the dreaded error message “Code execution has been interrupted” again.

The above fix is fine for any projects being rolled out to a group of users. If you’re testing code and using “Ctrl+Break” this might be a hindrance. Another fix is, while In the debug window, press Ctrl+Break, this again fixes the issue.

Of course, the next time you press “Ctrl+Break” in the Excel window the problem may well come back!

I say should because this is not guaranteed :( I have had occasions when this does not work, rebooting the PC is the only other possible solution I’ve found.

Friday 3 June 2011

Compile Error: "Object Library Invalid..."

If you’ve ever come across the ‘Compile Error’ message you’ll know how much fun they can be to correct. 

Quite often when this message appears you’ll find yourself faced with the VBE telling you that ‘Left’ is not a valid function !  The first thing you should check is what references you are using.  If you’ve ever written some code on one version of Excel and tried to role it out to users with an older version you’ll have seen this many times. 

When you add references in an Excel 97 file and use it in Excel 2003 the references will automatically update.  If you try and do that the other way round the references do not ‘back-date’.  You’ll need to go to the references box and un-check the ‘Missing’ reference.  Then look through the list and find the older version which is available to the Excel version you are using.

The most recent one I found displayed the following message:

Object Library Invalid or Contains References to Object Definitions that could not be found.

I’ll add here that I was working in an office which predominately used Excel 97 with several PCs with Excel 2003.  A little retro there ;)

The error occurred in Excel 2003 using an Add-In (.xla) created in Excel 2003 !  I checked for missing references and found none.  The file opened and the code ran without issue on other machines, both Excel 97 and Excel 2003. 

After turning to Google I found this thread at ozgrid.com.  The answer; deleting the all the EXD files left by previous iterations of the project. EXD files cache ActiveX component information. If the component changes without removing the EXD file (which is created when the component is first used), then the system will be out of sync. 

To delete the EXD files go to command prompt and type the following DOS commands:

  • > CD \Document and Settings
  • > DEL /S /A:H /A:-H *.EXD

To explain what that does I’ll quote from the thread:

Essentially the command recursively deletes all your hidden and exposed exds. That will make all your ActiveX components load a bit slower the first time you use them again, but it should also clear out the problematic ones.

So, I’ll keep my fingers crossed that the above works for you too.

Saturday 28 May 2011

Using VBA to Convert a Column Number To A Letter

Sometimes while manipulating data in Excel you’ll have a integer which represents the column number, but need the column letter.

The function below can translate that number to the column letter required.  This function has been tested and works to 702, column ‘ZZ’.

Function ConvertToLetter(iCol As Integer) As String
'-----------------------------------------------------------------------------
' Procedure : ConvertToLetter
' Author    : Niall - Zypher.co.uk
' Purpose   : Convert a number to a column letter
'             Tested to 256 columns, assumed to work until 702 (ZZ)
'-----------------------------------------------------------------------------
'
    If iCol <= 26 Then
        ' Columns A-Z
        ConvertToLetter = Chr(iCol + 64)
    Else
        ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & _
                          Chr(((iCol - 1) Mod 26) + 65)
    End If
End Function

As always, any questions or enquiries let us know via the comments section.

Monday 9 May 2011

Add worksheets to Excel using VBA

Adding worksheets to Excel is quite simple. For example, to add a Worksheet before the active sheet (default unless stated otherwise), name it "MyWorksheet" and have it become the active sheet, you would use code similar to below;

Sub Add_New_Worksheet()
    ' Add a new worksheet in front of the active sheet
    Worksheets.Add().Name = "MyWorksheet"
End Sub

If we want to add a new Worksheet as the last Worksheet in the active workbook and name it "MyWorksheet" we would use;

Sub Add_As_Last_Worksheet()
    ' A a named worksheet to the end
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MyWorksheet"
End Sub

The Add Method (with regards to the Worksheet Object) has an After Variant as well as an Before Variant. You can only use one of the options, either the Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet before the current active Sheet.

the example below shows how to add more then one Worksheet, the code below adds 2 new worksheets;

Sub Add_n_Worksheets()
    ' Insert 2 worksheets after the last current worksheet
    Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
End Sub

The last Variant available is the Type Variant. The Type specifies the sheet type. The choices available are listed below;
XlSheetType constants:

  • xlWorksheet
  • xlChart
  • xlExcel4MacroSheet
  • xlExcel4IntlMacroSheet

If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.

Friday 6 May 2011

Create a Microsoft Word Document with VBA

The below code is a basic example on how to create a Microsoft Word document.  This is sometimes useful if want to create a report showing your data outside of Excel.

Private Sub CreateWordDoc()
'-----------------------------------------------------------------------------
' Procedure : CreateWordDoc
' Author    : Matthew - Zypher.co.uk
' Date      : 12/04/2007
' Purpose   : Create a MS Word document using earling binding
'             Requires a reference to 'Microsoft Word ??.? Object Library'
'-----------------------------------------------------------------------------
'
    Dim objWord As Word.Application
    Dim doc As Word.Document

    'Create Word doc object
    Set objWord = CreateObject("Word.Application")

    With objWord
        ' Ensure the MS Word object is visible
        .Visible = True

        ' Add a new word document and save the file prior to adding text
        Set doc = .Documents.Add
        doc.SaveAs "C:\Your\File\Directory\Filename.doc(x)"

        ' Or open an existing document
        'Set doc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
    End With

    'Construct document
    With objWord.Selection
        ' Set the font type
        .Font.Name = "Trebuchet MS"
        ' Set the font size
        .Font.Size = 16

        ' Set the format, depending on the value of i
        For i = 1 To 50
            Select Case i
                Case Is < 10
                    ' Set the font size
                    .Font.Size = 12
                    ' Set font to bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 20
                    ' Set the font size
                    .Font.Size = 8
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphLeft

                Case Is < 30
                    ' Set the font size
                    .Font.Size = 10
                    ' Turn on bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 40
                    ' Set the font size
                    .Font.Size = 6
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the center
                    .ParagraphFormat.Alignment = wdAlignParagraphCenter

                Case Else
                    ' do nothing
            End Select

            ' Add text
            .TypeText "Here is an example test line, #" & i _
               
& " - Font size is " & .Font.Size
            ' Move to the next line
            .TypeParagraph

        Next i

    End With

    ' Save the file
    doc.Save
    ' Bring the MS Word window to the front
    doc.Activate

End Sub

As always, if you have any questions do let us know.

Saturday 9 April 2011

HTML Special Characters from 1 to 10000

In four fonts: Times New Roman, Arial, Courier New, and Comic Sans MS.

Blogger didn’t like the amount of code required to show the list of special characters so please click here to see the lists.  They are hosted on our main website.

Tuesday 5 April 2011

VBA Array Within An Array

When using VBA, writing anything but the most trivial VBA routines, it is likely that you’ll be using arrays somewhere in your code. This post describes how you can load one array to another and then pull the data out into a worksheet.
It is assumed that you know the basics of VBA arrays.
Sub ArrayWithinAnArray() 
'----------------------------------------------------------------------------
' Procedure : ArrayWithinAnArray
' Author    : Matthew - Zypher.co.uk
' Date      : 27/03/2011
' Purpose   : Load an ID, a data value and an array to an array
'             Then loop through the 1st array loading it’s values to the
'             active worksheet
'----------------------------------------------------------------------------
'
    Dim i As Integer
    Dim l As Integer
    Dim x As Integer
    Dim y As Integer

    Dim array1(2, 2) As Variant
    Dim array2(1, 1) As Variant

    On Error GoTo ArrayWithinAnArray_Error

    ' Add values into the 1st array
    For i = 0 To 2
        array1(i, 0) = "ID " & i
        array1(i, 1) = "Some Data"

        ' Load values to the 2nd array
        array2(0, 0) = "row1 col1"
        array2(0, 1) = "row1 col2"
        array2(1, 0) = "row2 col1"
        array2(1, 1) = "row2 col2"

        ' Load the 2nd array into the 1st array
        array1(i, 2) = array2
    Next i

    ' Set l as the first row
    l = Cells.SpecialCells(xlCellTypeLastCell).Row + 1

    ' Loop through the first array
    For i = 0 To UBound(array1)
        Cells(l, 1).Value = array1(i, 0)
        Cells(l, 2).Value = array1(i, 1)

        ' Check the 3rd value is an array
        If IsArray(array1(i, 2)) Then

            ' Loop down through the 2nd array
            For x = 0 To UBound(array1(i, 2))

                ' Loop across the 2nd array
                For y = 0 To UBound(array1(i, 2), 2)

                    ' Load the values in the 2nd array to the worksheet
                    Cells(l, y + 3).Value = array1(i, 2)(x, y)

                Next y

                ' Get the current bottom row, then add 1
                l = Cells.SpecialCells(xlCellTypeLastCell).Row + 1
            Next x

        End If

        ' Get the current bottom row, then add 1
        l = Cells.SpecialCells(xlCellTypeLastCell).Row + 1

    Next i

    On Error GoTo 0
    Exit Sub

ArrayWithinAnArray_Error:
    ' Add some error handling code here
End Sub

As always, if you have any questions do let us know via the comments section.

Thursday 31 March 2011

April Fools Reverse The Excel Menu with VBA

The code below will, when run, reverse all of the menus and their options within Excel.  For example; File will become Elif and Tools will become Sloot.  It works on Excel 97 through 2003.  Simply run the code again to ‘reverse’ the name back to their original format.  It is worth noting that Excel remembers the settings, so you will need to be able to re-run the code.

Sub ReverseMenuText()
    Dim m1 As CommandBarControl
    Dim m2 As CommandBarControl
    Dim m3 As CommandBarControl

    On Error Resume Next
    For Each m1 In Application.CommandBars(1).Controls
        m1.Caption = Reverse(m1.Caption)
        For Each m2 In m1.Controls
            m2.Caption = Reverse(m2.Caption)
            For Each m3 In m2.Controls
                m3.Caption = Reverse(m3.Caption)
            Next m3
        Next m2
    Next m1
End Sub


Function Reverse(MenuText As String) As String
    Dim Temp As String, Temp2 As String
    Dim ItemLen As Integer, i As Integer
    Dim HotKey As String * 1
    Dim Found As Boolean

    ItemLen = Len(MenuText)
    Temp = ""
    For i = ItemLen To 1 Step -1
        If Mid(MenuText, i, 1) = "&" Then _
            HotKey = Mid(MenuText, i + 1, 1) _
        Else Temp = Temp & Mid(MenuText, i, 1)
    Next i
    Temp = Application.Proper(Temp)
    Found = False
    Temp2 = ""
    For i = 1 To ItemLen - 1
        If UCase(Mid(Temp, i, 1)) = UCase(HotKey) And Not Found Then
            Temp2 = Temp2 & "&"
            Found = True
        End If
        Temp2 = Temp2 & Mid(Temp, i, 1)
    Next i
    If Left(Temp2, 3) = "..." Then Temp2 = Right(Temp2, ItemLen - 3) & "..."
    Reverse = Temp2
End Function

This code was orinally found on MrExcel.com.

Tuesday 29 March 2011

Aprils Fools ‘Quit Excel’ Workbook_Open Event :)

I just came across this Aprils Fools trick while looking through some old code files.  Figured as it’s the right time of year I’d post it.  Put this code in the ‘ThisWorkbook’ code module, save the file and wait for the users to shout ;)

Private Sub Workbook_Open()
'-------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author    : Matthew Sims - Zypher.co.uk
' Date      : 01/03/2005
' Purpose   : To annoy ;)
'             Quit Excel 30 times out of 100 when the user opens this file
'-------------------------------------------------------------------------
'
    ' Set the annoying level (percentage chance that Exel will close)
    Const annoying_level As Long = 30
    Dim annoying_number As Long

    Application.DisplayAlerts = False

    Randomize
    annoying_number = 100 * Rnd

    ' If the randomly chosen 'annoying_number' is lower then the
    ' preset 'annoying_level' then quite Excel
    If annoying_number < annoying_level Then
        ' Quit Excel
        Application.Quit
    End If

End Sub

Wednesday 23 March 2011

Using VBA to Truncate Decimal Values

Recently I have needed to truncate a decimal value to a set number of decimal places. 

For example; If you wanted the number 55.446 to two decimal places rounding the number would return 55.45.  Truncating the number to two decimal places returns 55.44. 

To do this I created the function below.  Pass in a decimal value and the number of decimal places you want the number truncated too and the function returns the value as a ‘Double’.

Public Function TruncTo(dblValue As Double, lngPlaces As Long) As Double
'-------------------------------------------------------------------------
' Procedure : TruncTo
' Author    : Matthew Sims
' Date      : 08-Oct-2010
' Purpose   : Truncate a decimal value to the requested number of decimal places
'-------------------------------------------------------------------------
'
    On Error GoTo TruncTo_Error

    If IsNumeric(dblValue) Then
        TruncTo = Int(dblValue * 10 ^ lngPlaces) / 10 ^ lngPlaces
    Else
        TruncTo = 0
    End If

    On Error GoTo 0
    Exit Function

TruncTo_Error:
    ' Add some error handling code here
End Function

Thursday 3 March 2011

VBA Adjusting Speaker Volume (and Mute)

Following on from the code here (Using VBA Speech) the code below is used to make adjust the speaker volume, including turning mute on / off.

Private Declare Sub keybd_event Lib "user32" ( _
   ByVal bVk As Byte, ByVal bScan As Byte, _
   ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Sub VolUp()
'-- Turn volumn up --
   keybd_event VK_VOLUME_UP, 0, 1, 0
   keybd_event VK_VOLUME_UP, 0, 3, 0
End Sub

Sub VolDown()
'-- Turn volumn down --
   keybd_event VK_VOLUME_DOWN, 0, 1, 0
   keybd_event VK_VOLUME_DOWN, 0, 3, 0
End Sub

Sub VolToggle()
'-- Toggle mute on / off --
   keybd_event VK_VOLUME_MUTE, 0, 1, 0
End Sub

Again, this requires the PC to a sound card and speakers.

Tuesday 1 March 2011

Using VBA Speech

With April Fools day approaching (well, four weeks) I though I’d post a couple of amusing VBA tricks and jokes you could use.  The first is using the speech and the CD trey.  The code below can be copied straight into a VBA code module.

Declare Sub mciSendStringA Lib "winmm.dll" (ByVal lpstrCommand As String, _
ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, _
ByVal hwndCallback As Long)

Private Sub Workbook_Open()
    UseSpeech "The Mouse is hungry."
    OpenCDTray
    UseSpeech "Please add cheese."
    CloseCDTray
End Sub

Private Sub OpenCDTray()
    mciSendStringA "Set CDAudio Door Open", 0&, 0, 0
End Sub

Private Sub UseSpeech(stringToSpeak As String)
    Range("A1").Value = stringToSpeak
    Range("A1").Speak
    Application.CommandBars("Text To Speech").Visible = False
End Sub

Private Sub CloseCDTray()
    mciSendStringA "Set CDAudio Door Closed", 0&, 0, 0
End Sub

This does require the PC to have speakers.

Tuesday 25 January 2011

VBA Version Property

It is sometimes useful to know which version of Excel your user is using.  An example of when this would be useful is knowing whether the user is in Excel 2007/2010 or and older version.  Excel 2007 was the first release that allowed over 65535 rows and 255 columns.  Excel 2007 has over 1 million rows (1048576) and 16384 columns.

This can make a big difference to what you can do with the data available to you.  If your user is in Excel 2003 for example your code could crash if it attempts to copy too much data into the active workbook.  The function below can be used within your code to confirm the Excel version, you can then call a different routine depending on it’s results.

Private Function ReturnExcelVersion() As String
'-----------------------------------------------------------------
' Procedure : ReturnExcelVersion
' Author    : Matthew - Zypher.co.uk
' Purpose   : return a string value denoting the version of Excel
'             Check the first two characters as Excel 97 has
'             several different releases and patches
'             http://support.microsoft.com/kb/232652
'-----------------------------------------------------------------
'
    Select Case Left(Application.Version, 2)
         Case Is = "14"
            ReturnExcelVersion = "2010" 
        Case Is = "12"
            ReturnExcelVersion = "2007"
        Case Is = "11"
            ReturnExcelVersion = "2003"
        Case Is = "10"
            ReturnExcelVersion = "2002"
        Case Is = "9."
            ReturnExcelVersion = "2000"
        Case Is = "8."
            ReturnExcelVersion = "97"
        Case Is = "7."
            ReturnExcelVersion = "95"
        Case Else
            ReturnExcelVersion = "Unknown"
    End Select
End Function

Excel 2010 is version 14. The version number 13 was skipped because of the aversion to the number 13.  Another relevant link is XL97: Overview and History of Excel Patches.

You may also want to look into Application.OperatingSystem.

As always, if you have any questions do let us know via the comments section.

Wednesday 12 January 2011

Special Folders using the FileSystemObject

There are several methods to get find out the file paths for Microsoft Windows’ special folders (Systems folder, Temporary folder etc).  The example below uses the FileSystemObject.

Sub Special_Folders()
'-----------------------------------------------------------------
' Procedure : Special_Folders
' Purpose   : Retrieve file path for MS Windows 'special folders'
'             Requires a reference to Microsoft Scripting Runtime
'-----------------------------------------------------------------
'
    On Error GoTo ErrTrap

    Dim oFS As FileSystemObject

    Set oFS = New FileSystemObject

    ' Windows Folder Path
    MsgBox FS.GetSpecialFolder(WindowsFolder)

    ' System Folder - (example - Windows\System32)
    MsgBox oFS.GetSpecialFolder(SystemFolder)

    ' Temporary Folder Path
    MsgBox oFS.GetSpecialFolder(TemporaryFolder)

    If Not oFS Is Nothing Then Set oFS = Nothing

ErrTrap:
    Select Case Err.Number
        Case Is = 0
            ' No error continue
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Err.Clear
    End Select
End Sub

This routine requires a reference Microsoft Scripting Runtime.