Fwd: [libreoffice-users] Value and Date function in Macro

Oh no, I did it again! I sent yet another reply directly to the
original poster. Will I never learn? I really hate the new behaviour
of this list, especially since it's the only list that I am subscribed
to that behaves this way. Maybe it's the only one that is correct, I
don't know, but it annoys me a lot…
Most of the time I don't even know that I sent it to the wrong
destination, but sometimes someone else gives a very similar reply as
mine, and I wonder why mine wasn't good enough, until I realise that
it never was sent to the list, like in this case.

I don't think my reply will spread some new light to this question,
but I'll send it anyway. Maybe someone find it useful in some strange
way that I can't figure out right now…

Anyway, here it is, this time sent to the list and nowhere else:
---------- Forwarded message ----------
From: Johnny Rosenberg <gurus.knugum@gmail.com>
Date: 2013/2/9
Subject: Re: [libreoffice-users] Value and Date function in Macro

2013/2/8 Joel Madero <jmadero.dev@gmail.com>:
> Hi All,
>
> I have a macro setup that can easily do the following:
>
> pasteValue = mid(currentCellValue,1,4)
>
> a string is stored to pasteValue which I then can manipulate how I want.
>
> What I want to do is make it actually more like this:
>
> pasteValue =
> date(value(mid(currentCellValue,1,4),value(mid(currentCellValue,5,2),value(mid(currentCellValue,8,2))
>
>
> I want to do this because the pasteValue as it stands is a text field, I
> need it converted to a date field.

Exactly what does the currentCellValue string look like? It seems like
you are trying to separate year, month and day from a string, but the
numbers doesn't make 100% sense to me… Looks like currentellValue
looks like:
YYYYMM-DD

I wrote a simple macro that prints the date number (number of days
from 1899-12-30 until given date) for the same date in two different
formats: YYYY-MM-DD and YYYYMMDD:
REM ***** BASIC *****

Option Explicit

Sub Main
    Dim pasteValue As Long
    Dim currentCellValue As String

    currentCellValue="2013-02-09" ' 41314 days since 1899-12-30.

    pasteValue=DateSerial(Val(Mid(currentCellValue,1,4)),
Val(Mid(currentCellValue,6,2)), Val(Mid(currentCellValue,9,2)))
    Print pasteValue ' Prints 41314

    currentCellValue="20130209"
    pasteValue=DateSerial(Val(Mid(currentCellValue,1,4)),
Val(Mid(currentCellValue,5,2)), Val(Mid(currentCellValue,7,2)))
    Print pasteValue ' Prints 41314
End Sub

Instead of the Print statement, I guess you would set a cell value and
so on, that's up to you. If you do, you can then format the cell to
display the date the way you want and you can perform date
calculations on the cell and so on.

Hope this helps.



Johnny Rosenberg

> I can do this just fine by manaully
> entering the above code into a cell directly (ie. not in a macro) but when
> I enter it in the macro I get:
>
> Sub-procedure or function procedure not defined.
>
>
> I know I can work out a micky mouse way by adding another column and
> referring to the previous pasted data and then doing a special paste of it,
> but this seems unnecessary.
>
>
> Thanks in advance.
>
> Best Regards,
> Joel
>
> --
> *Joel Madero*
> LibreOffice QA Volunteer
> jmadero.dev@gmail.com
>
> --
> For unsubscribe instructions e-mail to: users+help@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

-- 
For unsubscribe instructions e-mail to: users+help@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