LazerWire.com

Facebook App: My Fighter Profile

2012-04-19T10:08:00.000+02:00

If you're enjoying the martial arts (karate, tae kwon do, judo, etc.) you should share your fighter profile with your friends on Facebook using the new My Fighter Profile app. You can share all gradings, results from tournaments, etc.

Excel VBA: Display the Developer Toolbar or Ribbon

2011-11-16T15:57:00.005+01:00

By default the developer toolbar or ribbon (depending on the Excel version) is hidden in Microsoft Excel. In Excel 2007 and later it can be quite tricky to figure out where to enable the developer ribbon if you don't know where to look. To make things even worse, in Excel 2010 they have changed the way of enabling the ribbon.

Excel 2010

In Excel 2010 you can display the developer toolbar the following way:

Click the green File Button
Press Options
Make sure that the Customize Ribbon right menu item is selected.
In the dropdown list called Customize the Ribbon, select All Tabs.

In the group called Main Tabs, make sure that the option Developer is checked.

Excel 2007

To display the developer ribbon, do the following:

Click the Office Button
Click the Excel Options button at the bottom of the dialog.
Ensure that the Popular tab in the left menu is selected (se picture below)
Check the option Show Developer tab in the Ribbon

Excel 2003

In older versions of excel the developer ribbon is divided between two toolbars: Control Toolbox and Forms. The toolbars can be displayed the following way:

Press View on the main toolbar
Select Toolbars
Check the toolbar Control Toolbox
Check the toolbar Forms

Visual Studio 2010: Debug the .NET assemblies

2011-11-07T20:55:00.001+01:00

The .NET framework is under heavy development and bugs are removed by Microsoft developers when found. However, you may still experience major bugs that might cause your application to crash. Sometimes it is even hard to determine whether it is a bug in your own code or in the framework. This issue has been taken to a new level in Visual Studio 2010. A feature, which by default is disabled in Visual Studio, enables you to debug the entire .NET source code directly within VS2010. Previously tools like .NET Reflector and ILSpy were required to identify any bugs in the .NET framework.

To enable .NET framework code debugging, please do the following:

1) In Visual Studio 2010, select Debug -> Options and Settings...
The Options dialog is displayed.

2) Select Debugging in the left menu (se picture below) and check the option Enable .NET Framework source stepping.

When running your application in debug mode, Visual Studio will download the .NET source code when needed. Please be aware that the source code might use a lot of free space on your hard drive depending on how many .NET assemblies that are in use.

Blogger: Pretty formatting source code

2011-11-07T20:53:00.000+01:00

When writing a blog related to programming it is quite tricky and troublesome to format the source code in a readable way. That is why we need pretty-formating tools. The following sites offer free tools for formatting source code:

C# Format - despite the name, this online tools allows you to pretty-format C#, VB, HTML and T-SQL. The source code in .NET is even available.

Excel VBA: Adding custom Button to the Toolbar or Ribbon

2011-11-07T20:25:00.000+01:00

Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer.
Please notice that the following code works best with Excel versions prior to Office 2007. If used in newer versions, from Excel 2007, the button and commandbar will be added to the "Add-Ins" tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. Se below for more information.

' ConstantsPrivate Const COMMANDBAR_NAME As String = "Custom Toolbar"Private Const BUTTON_CAPTION As String = "My Button"' OpenPrivate Sub Workbook_Open()
    ' Variables
    Dim objCommandBar As CommandBar
    Dim objButton As CommandBarButton

    ' Try to get the Commandbar (if it exists)
    On Error Resume Next
    Set objCommandBar = Me.CommandBars(COMMANDBAR_NAME)
    On Error GoTo 0
    
    ' Was the commandbar available?
    If (objCommandBar Is Nothing) Then
        ' Create the commandbar
        On Error Resume Next
        Set objCommandBar = Application.CommandBars.Add(Name:=COMMANDBAR_NAME, Position:=msoBarTop, Temporary:=True)
        On Error GoTo 0
        
        ' Valid commandbar?
        If (Not objCommandBar Is Nothing) Then
            ' Add the buttons to the command bar
            With objCommandBar
                ' Add button
                Set objButton = objCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                
                 ' Set the button properties
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = BUTTON_CAPTION
                    .FaceId = 258
                    .TooltipText = "Do Something"
                    .OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething"
                End With
                
                ' Show the command bar
                .Visible = True
            End With
        End If
    End IfEnd Sub' Before ClosePrivate Sub Workbook_BeforeClose(Cancel As Boolean)
    
    On Error Resume Next
    ' Try to remove the iTrade command bar
    Call Application.CommandBars(COMMANDBAR_NAME).Delete
    
    ' Restore error handling
    On Error GoTo 0
End Sub

The custom event called OnDoSomething must be defined in a global Module. It doesn't work to define the method in the Workbook class. Insert a new Module and add the following code:

Option Explicit

Public Sub OnDoSomething()
    MsgBox "Hello World!"End Sub

You can change the button icon by specifying another FaceId value. To get a list of all available FaceIds on you computer, download and and install the FaceID Browser:

FaceID Browser

Excel 2007 and later
To dynamically add buttons to the Ribbon you must use a combination of XML and VBA. For more information, please visit:

How to: Use VBA to Add a Custom Command to the Ribbon

Resources
Other usefull pages:

Excel VBA: Adding items to the right-click menu

Excel VBA: Adding custom buttons to the Right-Click popup menu

2011-11-07T20:15:00.001+01:00

In Microsoft Excel it is possible to extend the right click popup menu by using VBA. The right click menu is implemented as a special CommandBar (like the toolbars in Excel 2003) with the name "Cell".
Customs buttons are added before the popup menu is displayed. It is up to your self to make sure that any custom buttons are removed when they are no longer in use. Otherwise you would end up adding a new button every time the right menu is about to be displayed.

Option Explicit

' Constants
Private Const BUTTON_CAPTION As String = "My Button"


' Before Right Click
Private Sub Workbook_SheetBeforeRightClick(ByVal objSheet As Object, ByVal Target As Range, Cancel As Boolean)
    Dim objButton As CommandBarButton
    
    ' Remove the e-mail popup menu
    On Error Resume Next
    With Application.CommandBars("Cell")
        Call .Controls(BUTTON_CAPTION).Delete
    End With
    On Error GoTo 0
    
    ' Is the sheet1?
    If (objSheet Is Sheet1) Then
    
        ' Add the special button
        Set objButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True, Before:=1)
        
        ' Set the button properties
        With objButton
            .Style = msoButtonIconAndCaption
            .Caption = BUTTON_CAPTION
            .FaceId = 258
            .TooltipText = "Do Something"
            .OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething"
        End With
    End If
End Sub

Notice that the method SheetBeforeRightClick is overriden for the Workbook (ThisWorkbook) object, and not the worksheet, even though it is displayed on the worksheet. We are doing a check in the VBA code before the right click menu is displayed to ensure that it is only displayed for the worksheet named "Sheet1".

Please notice that all actions, i.e. OnAction events, must be stored in a public module. It is not possible to define the OnDoSomething method in the workbook or the worksheet classes. Add a new Module (Module1) and add the following code:

Option Explicit

Public Sub DoSomething()
    MsgBox "Hello World!"
End Sub

To specify an icon for the new button, a numeric value must be set for the FaceId property of the button. There is no built-in way of displaying all possible FaceIds. However, you can download and install the Excel add-in called Face ID Browser to display all possible button icons:

FaceID Browser

The final result will display the following custom menu item on the right click menu:

Resources

For more information on how to add buttons to CommandBars, please check out:

Excel VBA: Add buttons to toolbars/ribbons

Excel VBA: String Format

2011-11-01T19:36:00.000+01:00

The functions string.Format or StringBuilder.AppendFormat are two very usefull functions for formatting strings and increasing the readability of your .NET code. The Format function in VBA unfortunately works in a quite different way than the string.Format function in .NET. As far as I know there is no built-in function in VBA to acomplish the exact same as string.Format. In VBA the functionality can be achieved the following way:

' Format string using the .NET way
Public Function StringFormat(ByVal strValue As String, ParamArray arrParames() As Variant) As String
    Dim i As Integer

    ' Replace parameters
    For i = LBound(arrParames()) To UBound(arrParames())
        strValue = Replace(strValue, "{" & CStr(i) & "}", CStr(arrParames(i)))
    Next

    ' Get the value    StringFormat = strValue
End Function

Excel VBA: Download files from the Internet

2011-11-01T18:33:00.003+01:00

There is no built-in function in Microsoft Excel which allows you to download contents from the Internet on the fly. To accomplish this task we need to use the API for WinInet. The use and explanation of API in VBA is for advanced users which have prior experience from either Visual Basic 6.0 or .NET.

Pitfalls
It is very important that all open Internet connections are closed as soon as the task is completed. WinInet only allows 2 concurrent connections to a given host. If you forget to shut down the connection after use, you will experience timeouts and misleading error messages. Please refer to the following website for more information related to the maximum allowed concurrent web connections:

Adjust maximum concurrent connections

Howto
The source code below should be pasted in a "Class Module" in Excel. If you are not sure how to open the VBA editor in Excel for your current Microsoft Office version, please refer to the following page:

Display the developer toolbar or ribbon in Excel

Create new class module:

Open the Microsoft Visual Basic for Applications editor in Excel.
Select Insert -> Class Module on the main menubar
Rename the new class module to "WebClient"

Example
To use the code, you shold create a new instance of the class and any of the public methods:

DownloadFile - download a specific resource to a local file
UrlExists - check if a given URL exists

Dim objClient As New WebClient
Call objClient.DownloadFile("http://www.google.com", "c:\test.html")

Dependencies
The function "ReThrowError" is defined here:

Re-throw Errors in VBA

Source Code

' API
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long
Private Declare Function InternetGetLastResponseInfo Lib "wininet.dll" Alias "InternetGetLastResponseInfoA" (lpdwError As Long, ByVal lpszBuffer As String, lpdwBufferLength As Long) As Boolean

Private Enum EHttpQueryInfoLevel
    http_QUERY_CONTENT_TYPE = 1
    http_QUERY_CONTENT_LENGTH = 5
    http_QUERY_EXPIRES = 10
    http_QUERY_LAST_MODIFIED = 11
    http_QUERY_PRAGMA = 17
    http_QUERY_VERSION = 18
    http_QUERY_STATUS_CODE = 19
    http_QUERY_STATUS_TEXT = 20
    http_QUERY_RAW_HEADERS = 21
    http_QUERY_RAW_HEADERS_CRLF = 22
    http_QUERY_FORWARDED = 30
    http_QUERY_SERVER = 37
    http_QUERY_USER_AGENT = 39
    http_QUERY_SET_COOKIE = 43
    http_QUERY_REQUEST_METHOD = 45
    http_STATUS_DENIED = 401
    http_STATUS_PROXY_AUTH_REQ = 407
End Enum

Private Declare Function HttpQueryInfo Lib "wininet.dll" Alias "HttpQueryInfoA" (ByVal hhttpRequest As Long, ByVal lInfoLevel As Long, ByRef sBuffer As Any, ByRef lBufferLength As Long, ByRef lIndex As Long) As Integer
Private Declare Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" (ByVal hHttpSession As Long, ByVal sVerb As String, ByVal sObjectName As String, ByVal sVersion As String, ByVal sReferer As String, ByVal something As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function HttpSendRequest Lib "wininet.dll" Alias "HttpSendRequestA" (ByVal hhttpRequest As Long, ByVal sHeaders As String, ByVal lHeadersLength As Long, sOptional As Any, ByVal lOptionalLength As Long) As Integer

' Constants
Private Const INTERNET_FLAG_NO_CACHE_WRITE As Long = &H4000000
Private Const INTERNET_FLAG_NO_UI As Long = &H200
Private Const INTERNET_FLAG_EXISTING_CONNECT As Long = &H20000000
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000

Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_OPEN_TYPE_DIRECT = 1
Private Const INTERNET_OPEN_TYPE_PROXY = 3


' User Agent
Private Const USER_AGENT = "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/6.0)"



' Open
Private Function OpenSession()
    Dim hSession As Long

    ' Open internet connection
    hSession = InternetOpen(USER_AGENT, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)

    ' Valid session?
    If (hSession = 0) Then
        ' Error
        Err.Raise 1234, , "Unable to open internet connection!"
        
        ' Finished
        Exit Function
    End If
    
    ' Get the value
    OpenSession = hSession
End Function

' Close Handle
Private Sub CloseHandle(ByRef hHandle As Long)
   ' Valid handle?
   If (hHandle <> 0) Then
        ' Close
        Call InternetCloseHandle(hHandle)
        
        ' Clear handle
        hHandle = 0
    End If
End Sub


' Open Url
Private Function OpenUrl(ByVal hSession As Long, ByVal strUrl As String, Optional ByVal bRaiseError = True) As Long
    Dim hConnection As Long
    
    ' Valid session?
    If (hSession = 0) Then
        Err.Raise 2345345, , "The session is not set!"
        Exit Function
    End If
    
    ' Open Url
    hConnection = InternetOpenUrl(hSession, strUrl, vbNullString, ByVal 0&, INTERNET_FLAG_EXISTING_CONNECT Or INTERNET_FLAG_RELOAD, ByVal 0&)

     ' Valid file?
    If (hConnection = 0) Then
        ' Error
        Call RaiseLastError
        
        ' Exit
        Exit Function
    End If

    ' Get the value
    OpenUrl = hConnection

End Function

' Raise Last Error
Private Sub RaiseLastError()
    Dim strErrorMessage As String
    Dim lngErrorNumber As Long

    ' Get the last error
    lngErrorNumber = Err.LastDllError
    
    ' Valid error?
    If (lngErrorNumber <> 0) Then
        ' Error
        Err.Raise lngErrorNumber, , "DLL Error: " & CStr(lngErrorNumber)
    Else
        ' Get the error
        If (GetLastResponseInfo(lngErrorNumber, strErrorMessage)) Then
            ' Raise error
            Err.Raise lngErrorNumber, , strErrorMessage
        End If
    End If
End Sub

' Get Last Response Info
Private Function GetLastResponseInfo(ByRef lngErrorNumber As Long, ByRef strErrorMessage As String) As Boolean
    Dim intResult As Integer
    Dim lngBufferLength As Long
    
    ' Get the required buffer size
    intResult = InternetGetLastResponseInfo(lngErrorNumber, strErrorMessage, lngBufferLength)
        
    ' Valid length?
    If (lngErrorNumber <> 0) Then
        ' Allcoate the buffer
        strErrorMessage = String(lngBufferLength, 0)
        
        ' Retrieve the last respons info
        intResult = InternetGetLastResponseInfo(lngErrorNumber, strErrorMessage, lngBufferLength)
    
        ' Get the error message
        GetLastResponseInfo = True
        Exit Function
    End If
    
    ' Not an error
    GetLastResponseInfo = False
End Function


' File Exists?
Public Function UrlExists(ByVal strUrl As String) As Boolean
    On Error GoTo ErrorHandler
    
    Const BUFFER_LENGTH As Long = 255
    
    Dim hSession As Long
    Dim hConnection As Long
    Dim strBuffer As String * BUFFER_LENGTH
    Dim intBufferLength As Long
    Dim intResult As Integer
    Dim lngIndex As Long
    Dim strStatusCode As String
    Dim intStatusCode As Integer
    
    ' Open Session
    hSession = OpenSession
    
    ' Open the file
    hConnection = OpenUrl(hSession, strUrl, False)
    
    ' Set the default bufferlength
    intBufferLength = BUFFER_LENGTH
    
    ' Get the status code
    intResult = HttpQueryInfo(hConnection, http_QUERY_STATUS_CODE, ByVal strBuffer, intBufferLength, lngIndex)
    
    ' Valid value?
    If (intResult <> 0) Then
        ' Get the status code string
        strStatusCode = Left(strBuffer, intBufferLength)
        
        ' Get the integer status code
        intStatusCode = CInt(strStatusCode)
        
        ' Check the status code
        UrlExists = (intStatusCode = 200)
    End If
    
    ' Close the connection
    Call CloseHandle(hConnection)
    Call CloseHandle(hSession)
    Exit Function
    
ErrorHandler:
    Call CloseHandle(hConnection)
    Call CloseHandle(hSession)
    
    ' Re-throw
    Call ReThrowError(Err)
End Function


' Download File
Public Sub DownloadFile(ByVal strUrl As String, ByVal strFilename As String)
    On Error GoTo ErrorHandling
    
    ' Buffer size
    Const BUFFER_SIZE As Integer = 4096
    
    Dim hSession As Long
    Dim hConnection As Long
    Dim strBuffer As String * BUFFER_SIZE
    Dim intFile As Integer
    Dim lngRead As Long
    Dim intResult As Integer

    ' Open session
    hSession = OpenSession()

    ' Open the file
    hConnection = OpenUrl(hSession, strUrl)
    
    ' Find free file
    intFile = FreeFile
    
    ' Create file
    Open strFilename For Binary As #intFile
    
        Do
            ' Read the data
            intResult = InternetReadFile(hConnection, strBuffer, BUFFER_SIZE, lngRead)
    
            ' Valid function?
            If (intResult <> 0) Then
            
                ' Valid number of bytes read?
                If (lngRead > 0) Then
                
                    ' Is less than buffer size?
                    If (lngRead < BUFFER_SIZE) Then
                    
                        ' Get only the relevant data
                        strBuffer = Left(strBuffer, lngRead)
                    End If
                
                    ' Write the data
                    Put #intFile, , strBuffer
                End If
            End If
            
        Loop While (lngRead > 0)
        
    ' Close the file
    Close #intFile
    
ExitMe:
    ' Close connection
    Call CloseHandle(hConnection)
    Call CloseHandle(hSession)
    Exit Sub
    
ErrorHandling:
    ' Close connection
    Call CloseHandle(hConnection)
    Call CloseHandle(hSession)
     
    ' Rethrow
    Call ReThrowError(Err)

End Sub

Excel VBA: Re-throw error/exception

2011-11-01T18:04:00.000+01:00

The current version of Microsoft Excel does not support the new Try/Catch blocks found in VB.net or any other modern programming languages. To handle errors within a procedure the "On Error Goto" statement must be used. When used, VBA expects the procedure to handle the error by either displaying a message visible to the user or just ignore it and continue as before.

However, if you are a Java or .NET developer you might want to restore the state of the application when a error is caught and then re-throw the error to let another part of the application handle it, just like when the "using" statement or throw/catch blocks are applied in C# code. There is no direct way of re-throwing a error in VBA, like throw or throw [Exception] in Java and .NET. The correct way of solving this challenge is to use the Err.Raise method, and specify all the existing error parameters in the following way:

Sub Test()
    On Error GoTo ErrorHandler

    ' Do Something
    Exit Sub
ErrorHandler:
    ' Restore state

    ' Re-throw/Raise existing error
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,    Err.HelpContext
End Sub

A custom method can be made to simplifying the re-throwing of errors:

' Rethrow
Public Sub ReThrowError(ByVal objError As ErrObject)
    ' Raise
    Err.Raise objError.Number, objError.Source, objError.Description, objError.HelpFile, objError.HelpContext
End Sub

.NET: Alternatives to .NET Reflector

2011-10-23T09:53:00.000+02:00

.NET Reflector is dead, long live ILSpy. Well, .NET Reflector is not really dead, as you might already know, but you now have to pay if you want to use the disassembler. Below we have listed the most common alternatives to the previously popular tool:

Personally I prefer to use the ILSpy because it is open-source, and works in the similar way as the old .NET reflector.

JavaScript: Get URL Query Argument

2011-10-23T09:09:00.001+02:00

Often I have the need to parse the URL query to retrieve the value of a specific named argument. There is no direct way of doing this is JavaScript, so this requires a custom function.

function findQueryArgument (strQuery, strArgumentName) {

// Valid query?

if (strQuery) {

// Split the parameteres

var arrParameters = strQuery.split("&");

// Walk through the parameters

for (var i = 0; i < arrParameters.length; i++) {

// Get the key/value pair

var arrPair = arrParameters[i].split("=");

// Is this the event argument?

if (arrPair[0] == strArgumentName) {

// Has value?

if (arrPair.length > 1) {

// Get the value

return arrPair[1];

}

// Not set

break;

}

// Not found

return '';

}

The strQuery argument is the full URL query i.e. a=val1&b=val2 etc. The full URL should not be included.
strArgumentName is the name of the argument you're looking for, i.e. a or b.

Please notice that the returned value is URL encoded. To extract the actual string value you need to use the built in JavaScript method unescape.

Here is an example:

// Query string

var strQuery = 'a=val1&b=val2';

// Get the value of the 'b' argument

var strEscaped = findQueryArgument(findQueryArgument, strQuery, 'b');

// Get the unescaped value

var strUnescaped = unescape(strEscaped);

// Display the value

alert(strUnescaped);

Excel VBA: Sum Values IF

2011-10-10T20:44:00.001+02:00

Below we have made a function which sums the values of one column/range given that the value of another column is equal to a fixed value. This function ignores the N/A values, which is not the case for the built-in function called SumIf in Excel. If the built-in function finds a N/A value the function returns N/A.

' SUM Value If

' Ignores N/A

Public Function SumValueIf(ByVal objRange As Range, ByVal objCriteria As Range, ByVal objSumRange As Range) As Currency

Dim intRow As Integer

Dim objRangeValue As Object

Dim objCriteriaValue As Object

Dim objValue As Object

Dim dblValue As Currency

Dim dblSum As Currency

' Get the criteria value

objCriteriaValue = objCriteria(1, 1)

' Walk through the rows

For intRow = 1 To objRange.Rows.Count Step 1

' Get the current value

objRangeValue = objRange(intRow, 1)

' Compare values

If (objRangeValue = objCriteriaValue) Then

' Get the value

objValue = objSumRange(intRow, 1)

' Is valid number?

' Ignore any strings, #N/A, #Error, etc.

If (IsNumeric(objValue)) Then

' Get the value

dblValue = CCur(objValue)

' Sum

dblSum = dblSum + dblValue

End If

' Get the value

SumValueIf = dblSum

End Function

The function can be used in the following way:

= SumValueIf(A1:A10; "abc"; B1:B10)

where A1:A10 is the compare column. Only sum the value in column B1:B10 if the value in column A is equal to "abc".

Excel VBA: Convert Variant Array to String Array

2011-10-10T20:36:00.000+02:00

To simplify the conversion of a variant array to a string array we have made a set of utility functions.

' Array Variant to String

Public Function VariantArrayToStringArray(ByVal arrVariants As Variant) As String()

Dim arrStrings() As String

' Get the string array

Call ParamArrayToStringArray(arrVariants, arrStrings)

' Get the string array

VariantArrayToStringArray = arrStrings

End Function

' Array Variant to String

Public Sub ParamArrayToStringArray(ByVal arrVariants As Variant, ByRef arrStrings() As String)

Dim intLength As Integer

' Handle the array

Call ParamArrayToStringArrayInternal(arrVariants, arrStrings, intLength)

End Sub

' Array Variant to String

Private Sub ParamArrayToStringArrayInternal(ByVal arrVariants As Variant, ByRef arrStrings() As String, ByRef intLength As Integer)

' Parameter is array?

If (IsArray(arrVariants)) Then

Dim i As Integer

Dim objValue As Variant

' Walk through the specified partner objects

For i = LBound(arrVariants) To UBound(arrVariants) Step 1

' Get the value

objValue = arrVariants(i)

' Array to string

Call ParamArrayToStringArrayInternal(objValue, arrStrings, intLength)

Else

' Next item

intLength = intLength + 1

' Expand array

ReDim Preserve arrStrings(1 To intLength)

' Set the value

arrStrings(intLength) = CStr(arrVariants)

End If

End Sub

Excel VBA: Expand and Collapse Group

2011-10-10T20:28:00.001+02:00

In Excel it is possible to group rows and columns. To expand or collapse a a group you can use the VBA method called ShowLevels. In the example below we are expanding a row group. We have also disabled automatic recalculation to improve the performance.

' Show Level

Public Sub ShowGroupLevel(ByVal intRowLevel As Integer)

Dim intCalculation As XlCalculation

' Get the old calculation state

intCalculation = Application.Calculation

' Disable re-calculation

Application.Calculation = xlCalculationManual

' Show the specified level (expanded)

On Error Resume Next

Call ActiveWorkSheet.Outline.ShowLevels(RowLevels:=intRowLevel)

On Error GoTo 0

' Restore automatic calculation

Application.Calculation = intCalculation

End Sub

Please notice that expand and collapse work in the same way. Collapsing a group is the same as only displaying the first level. Expand is the same as showing level 2 or more.

Excel VBA: Get all selected rows

2011-10-10T20:13:00.003+02:00

If the user does does a selection of rows in Excel, the selection can contain multiple areas. An area is a subselection of rows created when the user presses the Shift button while doing a multiselect. In VBA we need to handle each area independently to be able to retrieve all the selected rows.

' Example
Public Sub Test()

Dim objSelection As Range

Dim objSelectionArea As Range

    Dim objCell As Range
    Dim intRow As Integer

    ' Get the current selection

Set objSelection = Application.Selection

' Walk through the areas

For Each objSelectionArea In objSelection.Areas

' Walk through the rows

For intRow = 1 To objSelectionArea.Rows.Count Step 1

' Get the row reference

Set objCell = objSelectionArea.Rows(intRow)

' Get the actual row index (in the worksheet).

' The other row index is relative to the collection.

intActualRow = objCell.Row

' Get any cell value by using the actual row index

' Example:

strName = objNameRange(intActualRow, 1).value

End Sub

In the example we have not defined the variables strName and objNameRange. For your reference the variables are defined as follows:

Dim strName As String

Dim objNameRange As Range

objNameRange can be any range in your selected worksheet.

Excel VBA: Send E-mail from Excel

2011-10-10T19:36:00.006+02:00

There are several ways to send e-mail from Excel using Microsoft Outlook.

It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments.

In this example we are going to use the COM library of Microsoft Outlook to get more control. Attachments are not covered in this example.

We start by checking if an instance of Outlook is already running. To be independent of the installed version of Outlook we use the GetObject to try to get an existing instance. Another option would have been to reference the Outlook library directly, but then we are required to update the reference every time a new version of Microsoft Office is installed. If no instance is running, we start Outlook by calling CreateObject. Please remember to close down Outlook when you are finished. Otherwise you will get multiple instance of the same application without the user knowing it.

We get a reference to the current users inbox by calling getDefaultFolder(6).The value 6 is a constant referring to the Inbox. If you used a reference to the COM library, you would have used the enum value olFolderInbox.

' Send E-mail

' strTo - Recipients. List of e-mails (separated by ';')

Public Sub SendEmail(ByVal strTo As String)

Dim objOutlookApp As Object

Dim objEmail As Object

Dim objMapi As Object

Dim objInboxFolder As Object

' Valid e-mails?

If (strTo = "") Then

' Error

MsgBox "No e-mails have been set!", vbExclamation, "No e-mails"

' Finished

Exit Sub

End If

' Attach to outlook

On Error Resume Next

Set objOutlookApp = GetObject(, "Outlook.Application")

On Error GoTo 0

' Is Outlook running?

If (objOutlookApp Is Nothing) Then

' Create new instance of outlook

Set objOutlookApp = CreateObject("Outlook.Application")

' Get the MAPI namespace (e-mails)

Set objMapi = objOutlookApp.GetNamespace("MAPI")

' Get the inbox folder

Set objInboxFolder = objMapi.getDefaultFolder(6)

' Display the inbox folder (make outlook visible)

Call objInboxFolder.Display

End If

' Create the new e-mail

Set objEmail = objOutlookApp.CreateItem(0)

' Set the properties of the new email

With objEmail

' Set the recipients

.To = strTo

' Show the message

Call .Display

' Resolve all recipients (Same as pressing the "Check Names" button)

Call .Recipients.ResolveAll

End With

' Free memory

Set objEmail = Nothing

Set objOutlookApp = Nothing

End Sub

Blogger: Free Templates for your Blog

2011-10-08T19:30:00.002+02:00

Want to have a proffessional looking blog on blogger.com (blogspot.com)? The available templates for blogs on blogger.com are very limited. You can download free templates from the following sites:

Facebook Dev: Free webhosting for your App

2011-10-08T19:06:00.001+02:00

If you're a student and want to learn Facebook App development, or just need a free webhost for your app, you can apply for free webhosting from numerous free webhosting providers.

If you are developing your Facebook App in ASP.NET, check out the list of free webhosts that support ASP.NET and Microsoft SQL Server:

Free webhosting for ASP.NET

Since there are few licenses related to setting up a webserver running Linux, it is much easier to find a free webhost if your Facebook application does not require a Windows server.

0000cost (1.5 GB storage, 15 GB bandwidth)
Free domain site (1 GB storage, unlimited bandwidth)
Bammz (unlimited storage and bandwidth)
HelioHost (500 MB, unlimted bandwidth)
Somee (150 MB storage, 5 GB transfer)
No Fee Host (100 MB storage)
7 Host (50 MB storage)
Brinkster (30 MB storage, 2 TB bandwidth)
10k Host (unknown storage and bandwidth)
Millenium Systems (only for webdesigners)

Please notice that the webhosts running Windows servers and ASP.NET normally also provide support for PHP and MySQL databases.

You can also get free SSL certificates for your webserver (which is required from October 1st for all Facebook Apps):

Free SSL Certificates

Webhosting: Free webhosting for ASP.NET

2011-10-08T18:42:00.002+02:00

Believe it or not, it is possible to host your ASP.NET application for free. Storage, bandwidth, etc. is of course limited, but it is a good offer for students and test applications.

Below we have listed different hosts that offer free ASP.NET hosting:

Free ASP Web Hosting (2 GB storage, 20 GB bandwidth, includes MS SQL)
Web Host for ASP.net (150 MB storage)
Somee (150 MB storage, 5 GB transfer)
AspHost4Free (100 MB storage, unlimited bandwidth)
Free domain site (1 GB storage, unlimited bandwidth)
7 Host (50 MB storage)
ASP Spider
Brinkster (30 MB storage, 2 TB bandwidth)
HelioHost (using Mono Project on Linux servers)

You can also get free SSL certificates for your webserver:

Free SSL Certificates

Facebook Dev: Choosing a SDK for ASP.NET

2011-10-08T17:48:00.001+02:00

When first deciding to create a Facebook App in ASP.NET I was struggling to find a up to date Facebook SDK for .NET. Some of the SDKs contains very low level functions, while others don't support the latest features. When selecting SDK you also have to make sure that the "new" signed_request parameter is used by the framework. From October 1st 2011 all Facebook Apps and Facebook Pages must use this parameter to get information about the current user, page id, access token etc.

At the time of writing, it seems like it is only the offical Facebook SDK at CodePlex which is satisfying all the requirements when it comes to Facebook Development in .NET.
Unfortunately, the SDK contains a lot of bugs, and is very hard to debug if you happens to find one.

Personally I was forced to write most of my Facebook code from scratch without using the Facebook SDK due to all the bugs.

You can find the documentation of the Facebook API at the Developer area.

CSS: Support for transparent PNG in Internet Explorer

2011-10-08T16:32:00.002+02:00

Versions prior to Internet Explorer 7 has limited support for transparent PNGs. To enable transparent PNGs in IE 6 you should use the following statement in your CSS:



Opacity Bug in IE
If you specify the opacity (transparency) for a already transparent PNG in IE 7 and 8, the transparent area will turn totally black. If you use a transparent PNG together with f. example jQuery plug-ins, make sure that the opacity/filter is set to 255 (no transparency).

.NET: SVG Support

2011-10-08T16:23:00.000+02:00

After hours of searching for code for rendering SVG pictures I finally stumbled upon the SVG rendering engine at Codeplex.

The good thing about SVG is that it is supported in most modern browsers. However, SVG is not supported in .NET so you need to find an external library. It seems like Microsoft has focused all their energy on XAML, even though the two formats are very similar.

Fortunately for us the SVG Rendering Engine works more or less out of the box. I had to do some rewriting of the code to make it work with all SVG images. The library is still under development and needs a lot of new features to make it work 100% in accordance with the specifications.

ASP.NET: Free scheduling of your tasks (Cron jobs)

2011-10-08T16:03:00.003+02:00

If you choose to run your website in a shared hosting environment on windows servers, most webhosts don't allow you to schedule your own tasks, like backup, changing status etc. If you run plug-and-play solutions like WordPress or Drupal you are more or less dependent on scheduled tasks.

There are numerous providers of webbased scheduling of tasks. The tasks can normally be sceduled by specifying the URL of a given webpage on your server and when/how often the should run.

Free scheduling services include:

Host Tracker (1 free job)
My Web Cron (free service)
SetCronJob (limited cron jobs are free)
Web Scheduler (one cron job for free)

For your reference I have also compiled a list of commersial cron job providers (in case the free ones don't fullfill your needs):

Cons
The disadvantage when using free scheduling services, is that the number of tasks to schedule is limited, short timeout and the scheduling needs to be re-activated after a few months. If your site is heavily dependent on scheduled tasks, you should consider to use a paid service, or change to VPS or Dedicated Server hosting.

Scheduling in Windows plans
Some webhosts have made their own control panel or use commersial panels providing scheduled tasks as an option to their shared window plans. After checking multiple webhosts I realized that it is rather rear that such option is available. The following known webhosts provide scheduled tasks as an option in their control panel:

VPS and Dedicated Servers
If you run your website on a VPS or dedicated server, the need for a web based task scheduler does not apply. You can then either use the built-in scheduler in windows or set up your own windows service. This is possible since you will have full root-access.

MS SQL: Custom Date and Time functions

2011-10-08T15:33:00.002+02:00

Transact-SQL really lack a the neccessary support for creating and modifying dates and times. Now and then I have the need for creating custom dates based on year, month and day. This is not an easy task. To simplify the manipulation of dates and times a small toolkit of userdefined functions has been developed:

Date and Time functions

Calendar functions

MS SQL: Create DateTime

2011-10-08T15:00:00.002+02:00

To create a full DateTime structure in Transact-SQL we need to use two of our earlier defined userdefined functions.

First we generate a raw base date, and then we add the hours, minutes and seconds.

-- Returns a dateTime value for the date and time specified.
create function [dbo].[sudf_Common_DateTime]

(

@intYear int,

@intMonth int,

@intDay int,

@intHour int,

@intMinute int,

@intSecond int

)

returns datetime

begin

-- Create the datetime structure

return dbo.sudf_Common_Time(@intHour, @intMinute,@intSecond, dbo.sudf_Common_Date(@intYear, @intMonth, @intDay))

end