Re: [libreoffice-users] Re: macro pasting text instead of formula

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: users+unsubscribe@global.libreoffice.org
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