Excel Macros for Data Grids


#1

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) & ","
    Next
    
    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
    Next
    
    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

#2

This is super cool! Thanks @deanapeterson!


#3

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 & ", "
    Next
    
    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.)

Result:

Regards
Ryan


#4

Thanks for the contribution @Ryan! :slight_smile:


#5

@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.