This class addresses and manipulates a single excel cell and its contents. You can find the methods and properties documentation on github. The code is also below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
'gistThat@mcpher.com :do not modify this line - see ramblings.mcpher.com for details: updated on 8/18/2014 4:47:40 PM : from manifest:3414394 gist https://gist.github.com/brucemcpherson/3414216/raw/cCell.cls ' a data Cell - holds value at time of loading, or can be kept fresh if there might be formula updates Option Explicit ' Version 2.04 - 'for more about this ' https://ramblings.mcpher.com/classes/datamanip/ 'to contact me ' https://gitter.im/desktopliberation/community 'reuse of code ' https://ramblings.mcpher.com/reusing-code-from-this-site/ Private pValue As Variant ' value of cell when first loaded Private pColumn As Long ' column number Private pParent As cDataRow ' cDataRow to which this belongs Public Property Get row() As Long row = pParent.row End Property Public Property Get column() As Long column = pColumn End Property Public Property Get parent() As cDataRow Set parent = pParent End Property Public Property Get myKey() As String myKey = makeKey(pParent.parent.headings(pColumn).toString) End Property Public Property Get where() As Range ' return the range from whence it came If row = 0 Then ' its a heading Set where = pParent.where.Resize(1, 1).Offset(row, pColumn - 1) Else Set where = pParent.where.Resize(1, 1).Offset(, pColumn - 1) End If End Property Public Property Get refresh() As Variant ' refresh the current value and return it pValue = where.value refresh = pValue End Property Public Property Get toString(Optional sFormat As String = vbNullString, _ Optional followFormat As Boolean = False, _ Optional deLocalize As Boolean = False) As String ' Convert to a string, applying a format if supplied Dim s As String, os As String, ts As String If Len(sFormat) > 0 Then os = Format(value, sFormat) Else If followFormat Then s = where.NumberFormat If Len(s) > 0 And s <> "General" Then os = Format(value, s) Else os = CStr(value) End If Else os = CStr(value) End If End If If deLocalize Then If VarType(value) = vbDouble Or VarType(value) = vbCurrency Or VarType(value) = vbSingle Then ' commas to dots ts = Mid(CStr(1.1), 2, 1) os = Replace(os, ts, ".") ElseIf VarType(value) = vbBoolean Then If value Then os = "true" Else os = "false" End If End If End If toString = os End Property Public Property Get value() As Variant ' return the value, refreshing it if necessary If pParent.parent.keepFresh Then value = refresh Else value = pValue End If End Property Public Property Let value(p As Variant) parent.parent.columns(pColumn).dirty = True If pParent.parent.keepFresh Then Commit p Else pValue = p End If End Property Public Function needSwap(cc As cCell, e As eSort) As Boolean ' this can be used from a sorting alogirthm Select Case e Case eSortAscending needSwap = LCase(toString) > LCase(cc.toString) Case eSortDescending needSwap = LCase(toString) < LCase(cc.toString) Case Else needSwap = False End Select End Function Public Function Commit(Optional p As Variant) As Variant Dim v As Variant If Not IsMissing(p) Then pValue = p End If where.value = pValue Commit = refresh End Function Public Function create(par As cDataRow, colNum As Long, rCell As Range, _ Optional v As Variant) As cCell ' Fill the Cell up ' if v is specifed we knw the value without needing to access the sheet If IsMissing(v) Then pValue = rCell.value Else pValue = v End If pColumn = colNum Set pParent = par Set create = Me ' return for convenience End Function Public Sub tearDown() ' clean up Set pParent = Nothing End Sub |