-
-
-
-
-
-
-
-
-
-
-
You are absolutely correct. So somebondy read my code after all! Thanks. -
Gentlemen,
Thank you for your suggestions. I was hoping that Excel could handle what I wanted directly, but (as Steve pointed out) it appears that it doesn’t. However, I adapted Peter’s suggestion to set the format based on the results of “casting out ones.” This appears to give the look that I want (at least on the test data that I’m using
). I had to write my own function (dModD) to perform the modulo division on Doubles, since the VBA mod operator and CLng() function both round round floating point inputs, instead of truncating them. That coding was pretty straightforward, though.The discussion of side effects on the ability to do calculations was also very informative. It hadn’t occurred to me that one could get a useful numerical result by “adding” text strings. After playing with it, I was even more surprised that it gave the correct answer when SUM() wouldn’t!
'if value is within 1 min. of an hour then format without decimal point If dModD(Cell.Value, 1) 0.983 Then Cell.NumberFormat = "0" Else Cell.NumberFormat = "0.##" End IfAnother interesting aspect of this discussion is what I left out of my question (because I didn’t realize that it was relevant
). My worksheet is generated by VBA code, using data that were generated by another program. The worksheet’s purpose is to format/present and in some cases to graph that data, so calculations aren’t necessary. My user is in the US, so the international issues won’t matter. It’s always good to know the limitations of pieces of code, though, because somehow they seem to get re-used. -
Hmm, that looks a lot more elegant than the function I wrote. Why would anyone object? Because of the GoTo? My code looks to see whether there are any user-defined custom lists. If any are present, it checks those lists to see if the first and last entries are those of my list. If not, it creates my list from a worksheet. I’d still have to do some of that in my error handling routine, but that still might have been a better approach. However, since it’s working, I’ll leave it alone. Thanks.
Function lMakeCustomList(rgListData As Range) As Long ' 'Checks to see if custom list containing "Walnut" and "Production Alternate" already exists. 'Creates it from "hard-wired" range in StationInfo if it does not. Returns List Number. 'VBA NOTE: Contrary to Help, AddCustomList method fails if list already exists. '26-Aug-2002 M.L.May -- working procedure with argument and variant list array Dim lLists As Long 'number of Custom Lists Dim l As Long 'list counter Dim i As Integer 'generic loop counter Dim bFound As Boolean 'true if station list was found Dim ListArray() As Variant 'variant array to hold Custom List 'if lList > 4 then custom lists exist, so check 'em all for first and last entries Application.StatusBar = "Looking for Custom List -
-
Hmmm
. Depending on how your program code works, you may want to load it after the data.vzg file, and modify your code to operate on the previously-opened .vzg workbook.
Author
