The Glade 4.0
https://gladerebooted.net/

My first VBA macro
https://gladerebooted.net/viewtopic.php?f=5&t=568		 Page 1 of 1
Author:  Shelgeyr [ Sat Oct 17, 2009 1:46 pm ]
Post subject:  My first VBA macro
Not so terribly much to look at, but I spent about 6 hours yesterday researching:
  • how to define a function that will check for the existence of a file in a specified folder
  • how to write a formula that will parse a file path from the hyperlink formula of another cell (which got replaced by the following)
  • how to define a function that will extract the file path from a cell that has had a hyperlink inserted rather than formula-encoded
  • how to write a macro that will loop through all selected cells, looking for filenames concatenated from the first cell in the row and the first cell in the column, then place an "X" in that cell if the file is present or a "-" if it is not.
In essence, my template is a spreadsheet with a list of client names down the left-hand side and a series of numbers across the top. The cells which contain the client names have had hyperlinks inserted which point to the shared folders in which the required files are stored. When files are moved into these folders, their names are edited to include the number of the condition they satisfy. My macro scans the folders, using DIR to find matches for filenames like "Shared Drive\Project Folder\Client Documentation\Fictitious Client Number One\5.*.*" where the filename itself might be anything, and the determining factor is that it's preceded by "5.". A column to the right of the grid is encoded to count the number of "X"s in each row, displaying the result in "# of 12" format and conditionally formatted to be shaded red, orange, yellow or green depending on the number of files found.
The whole thing was educational, entertaining and rewarding; and the best part is that it's something that's actually productive in terms of my current assignment rather than being a flashy toy I coded merely with which to amuse myself.
Author:  shuyung [ Sat Oct 17, 2009 3:02 pm ]
Post subject: 
Your mission, should you choose to accept it, is now to find out how many security flaws you have introduced.
Author:  Shelgeyr [ Tue Oct 20, 2009 6:50 pm ]
Post subject: 
Not aware of any security flaws as of yet, but I've upgraded the macro with a safety valve. It now checks the range of selected cells to determine that they're all within the area that the macro is allowed to update before it does anything.
I also coded a progress meter, as the macro can take some time to run when a large range of cells are selected.
Author:  shuyung [ Wed Oct 21, 2009 10:06 am ]
Post subject: 
https://www.securecoding.cert.org/confl ... +Practices

There's a lot more out there, this should get you started.
Author:  Shelgeyr [ Thu Oct 22, 2009 7:40 am ]
Post subject: 
Good stuff.

Anybody know how I can dynamically name and conditionally format a series of data labels?
Author:  Shelgeyr [ Thu Oct 22, 2009 7:59 pm ]
Post subject: 
Found what I needed (mostly):
Code:
Sub AttachLabelsToPoints()
 
   'Dimension variables.
   Dim PointCount As Integer, xVals() As String
   Dim SeriesCount As Integer
   Dim Max As Integer
   Max = ActiveChart.SeriesCollection.Count
   ReDim xVals(1 To Max)
       
   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False
 
   'Store the formula for each series in "xVals(SeriesCount)".
    For SeriesCount = 1 To Max
        On Error Resume Next
        xVals(SeriesCount) = ActiveChart.SeriesCollection(SeriesCount).Formula
    Next SeriesCount
 
   'Extract the range for the data from xVals.
    For SeriesCount = 1 To Max
        On Error Resume Next
        xVals(SeriesCount) = Mid(xVals(SeriesCount), InStr(InStr(xVals(SeriesCount), ","), xVals(SeriesCount), Mid(Left(xVals(SeriesCount), InStr(xVals(SeriesCount), "!") - 1), 9)))
        xVals(SeriesCount) = Left(xVals(SeriesCount), InStr(InStr(xVals(SeriesCount), "!"), xVals(SeriesCount), ",") - 1)
        Do While Left(xVals(SeriesCount), 1) = ","
            xVals(SeriesCount) = Mid(xVals(SeriesCount), 2)
        Loop
    Next SeriesCount
 
   'Attach a label to each data point in the chart.
   For SeriesCount = 1 To Max
        For PointCount = 1 To Range(xVals(SeriesCount)).Cells.Count
            With ActiveChart.SeriesCollection(SeriesCount).Points(PointCount)
            .HasDataLabel = True
            .DataLabel.Border.ColorIndex = 1
            Select Case SeriesCount
                Case Is = 1
                    .DataLabel.Interior.ColorIndex = 4
                Case Is = 2
                    .DataLabel.Interior.ColorIndex = 3
            End Select
            .DataLabel.Text = "=" & Range(xVals(SeriesCount)).Cells(PointCount, 1).Offset(0, -1).Address(True, True, xlR1C1, True)
            End With
        Next PointCount
    Next SeriesCount
   
End Sub

My data is organized in four columns: Labels, X values, Y values for met deadlines, Y values for missed deadlines.
My X values come from the project completion dates on another spreadsheet.
My 'met' and 'missed' columns are conditionally formatted to display either the desired height of the data point or "N/A#", based on whether or not the completion dates fall after the pre-determined due dates.
If I have 7 'met' deadlines and 3 'missed' deadlines, series 1 will have 7 data points with green labels while series 2 has 3 data points with red labels and the text values of the labels are cell references to the first data column.
My only issue is that because only the red or the green data point is created for any given deadline, if the status changes, the data point that suddenly becomes visible is not one that received the desired labeling/formatting. What I'd really like is to have all points created for both series, then conditionally determine which to show and which to hide after their appearance has been set.

Edit : Sonofabitch :(
This failed functionality, with only those data points which will be displayed actually being formatted, apparently does not apply to all versions of Excel. It most certainly does apply to Excel2003, which is what I've got at work; but when I fiddled with it in Excel2007, which is what Taamar has on her computer, ALL of my data points received the proper formatting and the whole thing worked beautifully. Moot point because I'm pretty sure there's no way I'm going to talk TIAA-CREF into upgrading their license, but I am now torn between the slick functionality combined with the hideous "Ribbon" menu interface of 2007 vs the deficient functionality combined with the simple utilitarian menu interface of 2003.
Page 1 of 1 All times are UTC - 6 hours [ DST ]
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/