Home |
Search |
Today's Posts |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Zeros in front of numbers in cells using Excel 2003
True, there are few cases that I haven't been hard fastened to it, which the
data type conversions is one of them. However, certain other ones like Left, Right, or many of the other string functions as examples, I am hard fastened to have done that way. There has been so many things that I have seen done with Left and Right and I have seen weird interactions, thus by prequalifying those, it most definitely avoids that sort of issue, though most of the weird ones that I have seen been more so in Access. That doesn't mean it couldn't happen in any other Office programs. Main reason why with Access, there's often times codes from different sources that's interacting with each other that's much more common to happen than with other Office programs. Another one that I have seen so often that is within Excel, if you don't prequalify certain objects such as prequalifying the range object, it will assume the active parent object, such as the active worksheet at the time it comes to the range object that isn't prequalified, which may not necessarily be the correct parent object 100% of the time. For that reason, that's another situation that I am hard fastened to prequalify, at least all the way up to the workbook level. Example: I have production reports to run at certain times. However, if I didn't prequalify my objects, but rather relied on the select, activate, and allowed the code to use active objects as the parents of such child objects, I would be able to do other things on that system or if I was to do other things on that system, at least 1 of 2 things, if not both will happen. 1) Focus will be moved from the application that I'm working in to Excel, which often times is the case with the Activate method. 2) Tasks being done to the wrong parent object such as tasks being done to range objects on the work worksheet object. This was one of the first issues that I ran into when I was first learning VBA, so it didn't take me long to realize that I had to prequalify my objects to avoid these sorts of issues. That was when I was working with XL97 on a WIN NT 4.0 system, which to tell the truth, I hated XL97 cause of the various issues that I ran into with XL97 on the spreadsheet side, which made Lotus 1-2-3, v2.3 so much easier to work with then XL97, even with SR-2 installed on XL97. However, once XL2K came out, a lot of the issues that I had in XL97 were resolved in XL2K. It did present some issues, but that's only cause I had to work between XL2K on my system while others had XL97 on their system. Why did I have XL2K on my system? MS sent me the Office 2000 disk free of charge as a fix to one of the charting bugs that I faced in XL97 and didn't really have a viable work around to address the issue in XL97. Back then, my VBA skill was only like a 3 or 4 on a scale of 1 to 10. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "Tom Ogilvy" wrote in message ... the references are unique to the workbook. Moving the file to another computer doesn't magically add references. In the thousands of author's whose code I have seen, you are the first I have seen who recommends routinely prefacing VBA commands with the VBA qualifier. And you don't even seem to be hard over on it: Recent post Workbooks("Book1.xls").Worksheets("Sheet1").Range( CStr(x) & ":" & CStr(y)).Rows.Count instead of Workbooks("Book1.xls").Worksheets("Sheet1").Range( VBA.CStr(x) & ":" & VBA.CStr(y)).Rows.Count -- Regards, Tom Ogilvy "Ronald Dodge" wrote in message ... Well if anything that I have learned from my years of programming, it's best practice not to leave things to potential ambiguity. When you don't prequalify your code, as time passes by, your code could become ambiguity by other additions/modifications, so it's best right from the start not to leave that chance as a possibility. As for Left and Right, those are very common ones to get mixed up. Yes, you can set the priority order of the different references, but that doesn't resolve every possible ambiguity situation. Yes, the VBA should be the second one in the list, only to the Excel Object to be the first one in the list as far as VBA in Excel is concerned. But even then, how do you even know it's that same order on another computer, if others are using it? -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to display numbers with zeros in front of them, eg 005 | Excel Discussion (Misc queries) | |||
add zeros in front of numbers | Excel Discussion (Misc queries) | |||
How do I keep zeros in front of other numbers (like a zip code)? | Excel Discussion (Misc queries) | |||
insert zeros in front of cells already filled with data. | Excel Discussion (Misc queries) | |||
How do I keep the zeros in front of numbers when i split a cell | Excel Worksheet Functions |