Hey Andrew, First - thanks for giving explicit examples of where I went wrong :) I'll study your examples and learn for the future.
Second - as for the switch, I was actually trying to conceptualize abstracting more and doing it per digit but I need to think about it a lot more. Thanks for the pointer for the simple if. If I can abstract then I can have one switch that takes care of day, month, and year. Lastly, thanks for the pointer to the other examples. I certainly learn by doing (I even have some patches submitted to the code...completely learned by doing/guess and check/looking at examples). Warmest Regards, Joel > > On 12/10/2014 12:23 PM, Joel Madero wrote: >> Hi All, >> >>> Well, you set the string of a cell. >>> Set the formula instead. >> So I just started from scratch because I had a ton of if statements >> before, decided to go to switch to make it faster but I'm hitting the >> same type of problem. >> >> Document: >> https://drive.google.com/file/d/0B5S-XtOUGGH5UmpBdnIzUUM3OFE/view?usp=sharing >> >> >> Click "Create Ledger" and you'll see the macro begin. >> >> Problem: Look at sheet "Ledger" under header "Date" (B7:B10). At first I >> thought those were right but they are not. Convert those to date, you'll >> see that all of them are 12/10/14. >> >> I literally have no clue where those are coming from. They should be: >> B7: 06/26/2009 >> B8: 06/26/2009 >> B9: 06/27/2009 >> B10: 06/28/2009 >> >> (all taken from RawData sheet). >> >> If you look at the macro (Sub fillData) you'll see that the pasteValue >> gets Date(year,month,date), furthermore you can see that year, month, >> date all get the correct values (uncomment the "print" values in the >> switch)..... >> >> Terribly confused at this point, suggestions welcome. >> >> >> Best, >> Joel >> > > I can see many things that you have done wrong. Here are a few > comments.... > > Your first problem... replace > > pasteSheet.getCellByPosition(1,k).value = Date(year,month,day) > > with > > pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day) > > DateSerial returns a date built from the component parts, Date does not. > > Also, you probably want to set the format to use a Date. I won't > bother with a complicated specified Numeric format for this, but will > provide this simple example: > > Dim oFormats > oFormats = ThisComponent.NumberFormats > Dim aLocale As New com.sun.star.lang.Locale > pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day) > pasteSheet.getCellByPosition(1,k).NumberFormat = > oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale) > > > Now for a few other comments. > > You have this big select case for some things. Consider this: > > Day month and year area already defined as type integer. This means > that those big case statements can be removed as follows: > > month = mid(currentCellValue, 5,2) > year = mid(currentCellValue, 10,2) > If (year < 10) Then > year = year + 2010 > ElseIf (year < 99) Then > year = year + 2000 > End If > day = mid(currentCellValue, 2,2) > > Dim oFormats > oFormats = ThisComponent.NumberFormats > Dim aLocale As New com.sun.star.lang.Locale > pasteSheet.getCellByPosition(1,k).value = > DateSerial(year,month,day) > pasteSheet.getCellByPosition(1,k).NumberFormat = > oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale) > > > Hope this helps. > > AndrewMacro.odt is a good place to find specific focused examples. > OOME_3_0.odt contains numerous examples, but is more focused towards > learning. > -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
