Category Archives: VBA

VBA 96 well plates positions

Function IsValidPosition(position As Integer) As Boolean
    If 1 <= position And position <= 96 Then
        IsValidPosition = True
    Else
        IsValidPosition = False
    End If
End Function


Function GetCoordinateFromVerticalPosition _
            (verticalPosition As Integer) As String
    Dim row As Integer
    Dim rowString As String
    Dim column As Integer
    Dim returnValue As String
        
    rowString = "A"
    
    If IsValidPosition(verticalPosition) Then
        row = (verticalPosition - 1) Mod 8 + 1
        rowString = Chr(Asc(rowString) + row - 1)
        column = (verticalPosition - row) / 8 + 1
        returnValue = rowString & column
    Else
        MsgBox "Invalid position number." & _
                vbNewLine & vbNewLine & _
                "Allowed values are 1-96.", _
                vbCritical, "GetCoordinateFromVerticalPosition: Invalid input"
        returnValue = vbNullString
    End If
    GetCoordinateFromVerticalPosition = returnValue
End Function

VBA Write2dArrayToSheet

A subroutine to write the data of a 2D-string array to a worksheet.

Sub Write2dArrayToSheet(data() As String, ByRef sheet As Worksheet)
    Dim i, j, n, m As Long
    
    n = 1
    For i = LBound(data, 1) To UBound(data, 1)
        m = 1
        For j = LBound(data, 2) To UBound(data, 2)
            sheet.Cells(n, m) = data(i, j)
            m = m + 1
        Next j
        n = n + 1
    Next i
End Sub