Excel Macros for Data Grids


I have written a few simple macros for excel to help import data into the Data Grids.

Place the macros inside a module in the VB Editor.

Function ConcatBalsamiqGrid(cellRange As Range)
    result = ""
    For Each currCell In cellRange.cells
        result = result & CStr(currCell.Value) & ","
    result = Left(result, Len(result) - 1)
    result = "{" & CStr(result) & "}"

    ConcatBalsamiqGrid = result
End Function

Function WidthPercent(cells As Range)
    cellSum = 0
    Count = 0
    For Each cell In cells
        cellValue = Left(cell.Value, Len(cell.Value) - 1)
        '  Someday test if cell value doesn't have a alignment attr and ignore
        cellSum = cellSum + cellValue
        Count = Count + 1
    totalWidthStr = "Total Width: " & CStr(cellSum) & "%, "
    totalColCountStr = "Total Column Count: " & CStr(Count) & ", "
    evenWidthStr = "Default Col Width: " & Round(100 / Count, 1) & "%"

    WidthPercent = totalWidthStr & totalColCountStr & evenWidthStr
End Function


This is super cool! Thanks @deanapeterson!


Thanks Dean

Can I contribute the following alternative… rather than type in width and alignment values (as shown in row 4 in your GIF) this will just take the actual Excel cell widths and alignment for the selected header range, resulting in a data grid that’s very similar to what someone creates in Excel.

Function BalsamiqGrid(cellRange As Range)
    For Each cell In cellRange
        result = result & Int(cell.Width)
        Select Case cell.HorizontalAlignment
            Case Excel.XlHAlign.xlHAlignCenter
                result = result & "C"
            Case Excel.XlHAlign.xlHAlignRight
                result = result & "R"
        End Select
        result = result & ", "
    result = Left(result, Len(result) - 2)
    result = "{" & CStr(result) & "}"
    BalsamiqGrid = result

End Function

Column widths do not have to total 100 as the Balsamiq application does that math for us. It’s all relative/proportional.

(Yes, I typically add a tiny, empty column to all my data grids just for better appearance (with lines off). I’d usually put one on the right side as well.)




Thanks for the contribution @Ryan! :slight_smile:


@Ryan, Thanks, I did consider this. I didn’t do it because I am not that VB savvy. I also have lot’s of grids on a single page and it would make it a little weird to work with.

I’m definitely going to see how I can integrate this. Thanks.