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

On 12/10/2014 07:13 PM, Joel Madero wrote:
> Hey Andrew,
>
> First - thanks for giving explicit examples of where I went wrong :)
> I'll study your examples and learn for the future.

Sometimes you need to specifically spelled out, I get that.... and your 
welcome.

> 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.

When possible, at least in other programming languages, I would simply 
provide the string along with a format specifier and perform the entire 
conversion in one shot. I don't know off hand if you can do a conversion 
by removing the "D". Certainly this might fail with two digit years. I 
managed to damage a bunch of data by allowing LO decide how to convert a 
two digit 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).

Simple examples are a good way to start. Your entire set of macros is 
very ambitious for a person who does not do this professionally.

> 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.

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info:  http://www.pitonyak.org/oo.php


--
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