Okay, with a bit of encouragement, I've got a project I'm going to embark on. I've got huge chunks of it lying around, but I'm going to try and pull it all together into something coherent. Alright, I'll spill the beans. I'm going to (slowly) start on pulling together a reasonable alternative to SPSS all written in VB6. But some caveats are in order. I'm certainly not going to try and replicate every SPSS feature. In fact, for the most part, I do all my data massaging in Excel, and then copy-paste into SPSS to generate p-values and confidence intervals. And that's the approach I'll take here.
But the very first task is a way to reasonably manage datasets. As I said, I think Excel is uniquely suited to do this. However, we must get these datasets into VB6 to be able to analyze them.
My thought is to use the FlexGrid. I think it'll serve the purpose quite well. No need for editing of data (as that'll be done in Excel). But I do need a way to easily move data between Excel and the FlexGrid. I was thinking of just some buttons that say something like "Clear Grid", "Copy Grid as Tab/CrLf Delimited", and "Paste Tab/CrLf Delimited (Excel) to Grid". I'm pretty sure I can manage this, but I thought I'd bounce it off the crowd.
Many Thanks,
Elroy
p.s. Once some chucks are completed into semi-polished segments, I'll make an entry into the codebank.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
In terms of Copy+Paste, simply use the .Clip property (I think I got the name right), as it converts the cells to+from Tab/CrLf Delimited.
Editing with a FlexGrid isn't too hard, you just need a textbox (or other apt controls) which you then show in the right place, I'm pretty sure the CodeBank has examples.
Hmmm, well, I'm assuming the user can figure out how to do Ctrl-C and Ctrl-V in Excel. It's the VB6 side I'm more worried about.
And, since my OP, I've charged off and done it. Here's the code I've come up with, and it's also attached as a ZIP. I'm open to people hammering on it with "anything" in the clipboard. I think I've made it pretty bulletproof, and it does seem to work well for my version of Excel.
Code:
Option Explicit
Private Sub cmdClear_Click()
ClearData
End Sub
Private Sub cmdCopy_Click()
Dim iRet As Long
Dim bHeader As Boolean
Dim s As String
Dim rGridPtr As Long
Dim cGridPtr As Long
Dim rGridStart As Long
'
' Include header row?
iRet = MsgBox("Include the header row (variable names) in the clipboard?", vbYesNoCancel Or vbQuestion, App.Title)
If iRet = vbCancel Then Exit Sub
bHeader = (iRet = vbYes)
If bHeader Then rGridStart = 0 Else rGridStart = 1
'
' Build a string.
For rGridPtr = rGridStart To Data.Rows - 1
For cGridPtr = 1 To Data.Cols - 2
s = s & Data.TextMatrix(rGridPtr, cGridPtr) & vbTab
Next cGridPtr
' Get the last column, but don't add a vbTab. Add vbCrLf instead.
s = s & Data.TextMatrix(rGridPtr, Data.Cols - 1) & vbCrLf
Next rGridPtr
'
' Place into clipboard.
Clipboard.Clear
Clipboard.SetText s
End Sub
Private Sub cmdPaste_Click()
Dim iRet As Long
Dim bHeader As Boolean
Dim s As String
Dim sClipRows() As String
Dim sClipCols() As String
Dim iColCount As Long ' Including record number col.
Dim iRowCount As Long ' Including header.
Dim rGridPtr As Long
Dim cGridPtr As Long
Dim rClipPtr As Long
Dim cClipPtr As Long
Dim i As Long
Dim rClipStart As Long
Dim d As Double
'
' Is there a header row?
iRet = MsgBox("Note that only valid numeric data will paste." & vbCrLf & vbCrLf & "Is there a header row (variable names) included?" & vbCrLf & "You are responsible for making sure the variable names are unique.", vbYesNoCancel Or vbQuestion, App.Title)
If iRet = vbCancel Then Exit Sub
bHeader = (iRet = vbYes)
'
' Start with scrubbed grid.
ClearData
'
' Parse clipboard into rows.
s = Clipboard.GetText
sClipRows = Split(s, vbCrLf)
If Not IsDimmedStr(sClipRows) Then Exit Sub
'
If sClipRows(UBound(sClipRows)) = "" Then ReDim Preserve sClipRows(0 To UBound(sClipRows) - 1)
iRowCount = UBound(sClipRows) - LBound(sClipRows) + 1
If Not bHeader Then iRowCount = iRowCount + 1
'
' Figure out max of columns.
For rClipPtr = LBound(sClipRows) To UBound(sClipRows)
i = CountOf(vbTab, sClipRows(rClipPtr))
If i > iColCount Then iColCount = i
Next rClipPtr
iColCount = iColCount + 2 ' One for record number column, and one for trailing data after last vbTab.
'
' Make a grid.
Data.Rows = iRowCount
Data.Cols = iColCount
'
' Put header row into grid.
If bHeader Then
sClipCols = Split(sClipRows(0), vbTab)
If UBound(sClipCols) = -1 Then ReDim sClipCols(0, iColCount - 2)
If UBound(sClipCols) < (iColCount - 2) Then ReDim Preserve sClipCols(0, iColCount - 2)
For cClipPtr = 0 To iColCount - 2
CellAlign 0, cClipPtr + 1, flexAlignCenterCenter
If Trim$(sClipCols(cClipPtr)) <> "" Then
Data.TextMatrix(0, cClipPtr + 1) = Trim$(sClipCols(cClipPtr))
Else
Data.TextMatrix(0, cClipPtr + 1) = Num2Let(cClipPtr + 1)
End If
Next cClipPtr
rClipStart = 1
Else
For cGridPtr = 1 To iColCount - 1
CellAlign 0, cGridPtr, flexAlignCenterCenter
Data.TextMatrix(0, cGridPtr) = Num2Let(cGridPtr)
Next cGridPtr
rClipStart = 0
End If
'
' Put record numbers into first column.
For rGridPtr = 1 To iRowCount - 1
CellAlign rGridPtr, 0, flexAlignCenterCenter
Data.TextMatrix(rGridPtr, 0) = Format$(rGridPtr)
Next rGridPtr
'
' Put data into grid.
rGridPtr = 1
For rClipPtr = rClipStart To UBound(sClipRows)
sClipCols = Split(sClipRows(rClipPtr), vbTab)
If UBound(sClipCols) = -1 Then ReDim sClipCols(0, iColCount - 2)
If UBound(sClipCols) < (iColCount - 2) Then ReDim Preserve sClipCols(0, iColCount - 2)
For cClipPtr = 0 To UBound(sClipCols)
CellAlign rGridPtr, cClipPtr + 1, flexAlignRightCenter
If Val(Trim$(sClipCols(cClipPtr))) <> 0 Or Left$(Trim$(sClipCols(cClipPtr)), 1) = "0" Then
d = Val(Trim$(sClipCols(cClipPtr)))
If d = Int(d) Then
Data.TextMatrix(rGridPtr, cClipPtr + 1) = Format$(d, "#0")
Else
Data.TextMatrix(rGridPtr, cClipPtr + 1) = Format$(d, "#0.0############################")
End If
End If
Next cClipPtr
rGridPtr = rGridPtr + 1
Next rClipPtr
End Sub
Private Sub ClearData()
Data.Clear
Data.Rows = 1
Data.Cols = 1
End Sub
Private Function CountOf(sNeedle As String, sHay As String) As Long
Dim iStart As Long
Dim iPos As Long
'
iStart = 1
Do
iPos = InStr(iStart, sHay, sNeedle)
If iPos = 0 Then Exit Function
CountOf = CountOf + 1
iStart = iPos + 1
Loop
End Function
Private Function IsDimmedStr(s() As String) As Boolean
On Error GoTo AnError
IsDimmedStr = (UBound(s) >= LBound(s))
AnError:
End Function
Private Function Let2Num(sLetter As String) As Long
Select Case Len(sLetter)
Case 1
Let2Num = Asc(UCase$(sLetter)) - 64
Case 2
Let2Num = ((Asc(UCase$(Left$(sLetter, 1))) - 64) * 26) + Asc(UCase$(Right$(sLetter, 1))) - 64
End Select
End Function
Private Function Num2Let(iNumber As Long) As String
If iNumber > 26 * 26 Then Exit Function
If iNumber <= 26 Then
Num2Let = Chr$(iNumber + 64)
Else
Num2Let = Chr$((iNumber - 1) \ 26 + 64) & Chr$(((iNumber - 1) Mod 26) + 65)
End If
End Function
Private Sub CellAlign(iRow As Long, iCol As Long, iAlign As MSFlexGridLib.AlignmentSettings)
' Not that cell selection isn't reset.
Data.Row = iRow
Data.Col = iCol
Data.CellAlignment = iAlign
End Sub
I just feel that it's imperative that I give the user a way to peruse their data in VB6 in an organized way. Also, organized in a grid control will make getting to it for analysis fairly easy.
Again, any suggestions are welcome.
Regards,
Elroy
EDIT1: Ahhh, Si, I re-read your post. I do have code in production I use for editing the FlexGrid, but I'm going to defer that whole thing for a while. For now, I'll assume that all editing will be done on the Excel side. Once I open up the whole editing-bag-of-worms, I'm opening myself up to actually massaging the dataset from within VB6, and that takes me down a very deep and windy rabbit-hole. I'm more interested (at least initially) in being able to analyze the data, and not actually massage it.
Last edited by Elroy; Jan 11th, 2017 at 06:39 PM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Hmmm, well, I'm assuming the user can figure out how to do Ctrl-C and Ctrl-V in Excel. It's the VB6 side I'm more worried about.
I wasn't referring to Excel, but the FlexGrid.
This code:
Code:
If bHeader Then rGridStart = 0 Else rGridStart = 1
'
' Build a string.
For rGridPtr = rGridStart To Data.Rows - 1
For cGridPtr = 1 To Data.Cols - 2
s = s & Data.TextMatrix(rGridPtr, cGridPtr) & vbTab
Next cGridPtr
' Get the last column, but don't add a vbTab. Add vbCrLf instead.
s = s & Data.TextMatrix(rGridPtr, Data.Cols - 1) & vbCrLf
Next rGridPtr
...if memory serves correctly, could be replaced by:
Code:
' Get a string.
Data.Row = IIf(bHeader, 0, 1)
Data.Col = 1
Data.RowSel = Data.Rows
Data.ColSel = Data.Cols
s = Data.Clip
I don't use the flexgrid that often, and I'm still brushing up on all the magic it's got. I'm a bit nervous about how it uses vbCr instead of vbCrLf, but I could easily do a Replace to fix that problem.
I'll change it. Thanks.
Elroy
EDIT1: Hmmmm, I may change it for the "Copy" into the clipboard, but I think I'll leave what I have for the "Paste" from clipboard into the grid. I just feel compelled to do data validation before stuffing things into the grid.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.