Well, this is what I've done so far:
- Learned a bit of VBA for a day. Going to learn a bit more VBA, doing scripts that tie in with Excel.
- Going to get a compiler and stuff set up for C# and Python. Will pick up both if I have spare time.
- Taking a look at Google scripts as well, possibly.
Will just drop / stop learning whatever I feel doesn't suit my needs. Not doing this for work, more just personal development like practising piano.
So far I've found VBA to feel a tad bit... worse than it could be, for what it is? Most of the pre-built code that interacts with Excel is completely unintuitive. There's weird pre-built stuff that has odd names and strange conditions. Feels like they could've made things more user-friendly.
Right now this is the code I made to read the furthest used column and row in an Excel file, assuming max # of rows is 5000, and max # of columns is 50 (can modify to go larger). Pretty simple, don't know if there's better functions available, but just to practice. It was one of the first functions I wanted if I'm going to make a larger set of code to create object arrays (correct term?) for each tab in Excel. What I'd do next is record all cells in Excel, including formatting, and record all the cells as part of an array. Then I could call on functions to adjust positioning and formatting, and rewrite the whole file by adjusting the array instead of moving cells organically in Excel in real-time.
The other thing I'm looking at is writing code to track all actions done in Excel. This would update the array as mentioned above. A separate file essentially would be recorded that would contain the entire Excel file's data but in my own way. Don't really know if where I'm going with this has practical applications, or if I'm just doing a worse version of stuff that exists.
Code
Public Function readMax() As Collection
'The following code reads the max X and max Y coordinates from the current Excel sheet, and returns the number of columns as readMax(1), and number of rows as readMax(2)'
Set shtJT = ActiveWorkbook.ActiveSheet
Set readMax = New Collection 'Required to make readMax a usable collection. readMax is used to output X and Y values'
Dim xMax As Long: xMax = 50 'xMax is the maximum number of columns read (columns ascend on x plane)'
Dim yMax As Long: yMax = 5000 'yMax is the maximum number of rows read (rows descend y plane)'
Dim lRow As Long: lRow = xMax
Dim lCol As Long: lCol = yMax
Dim xCatch As Long: xCatch = 0
Dim yCatch As Long: yCatch = 0
Do While lCol > 0
Do While lRow > 0
If IsEmpty(Cells(lCol, lRow)) = False Then
If (lRow > yCatch) = True Then
yCatch = lRow
lRow = 0
End If
If (lCol > xCatch) = True Then
xCatch = lCol
End If
End If
lRow = lRow - 1
Loop
lRow = xMax
lCol = lCol - 1
Loop
readMax.Add xCatch
readMax.Add yCatch
End Function
This post was edited by Canadian_Man on May 7 2017 10:05pm