Worksheet/General Procedure problem

[paulquinlan100@hotmail.com]

Hi

Im trying to get the following code to run from a custom add-in button.
The code works fine when its in the Workbook code area, however, when i
put it into a module in order to package it as an add-in i get the
error "Variable not defined" with cell highlighted. Any ideas?

Thanks a lot for any suggestions.

Paul

Sub FormatMIdates()

Selection.NumberFormat = "@"
For Each cell In Selection

midate = cell.Value

If midate <> "" Then

pYear = Left(midate, 4)
pmonth = Mid(midate, 5, 2)
pday = Right(midate, 2)

newDate = pday + "/" + pmonth + "/" + pYear

cell.Value = newDate
End If

Next

End Sub

[Gary''s Student]

Dim cell as Range
--
Gary's Student


"paulquinlan100@hotmail.com" wrote:

> Hi
>
> Im trying to get the following code to run from a custom add-in button.
> The code works fine when its in the Workbook code area, however, when i
> put it into a module in order to package it as an add-in i get the
> error "Variable not defined" with cell highlighted. Any ideas?
>
> Thanks a lot for any suggestions.
>
> Paul
>
> Sub FormatMIdates()
>
> Selection.NumberFormat = "@"
> For Each cell In Selection
>
> midate = cell.Value
>
> If midate <> "" Then
>
> pYear = Left(midate, 4)
> pmonth = Mid(midate, 5, 2)
> pday = Right(midate, 2)
>
> newDate = pday + "/" + pmonth + "/" + pYear
>
> cell.Value = newDate
> End If
>
> Next
>
> End Sub
>
>

[Dave Peterson]

You copied your code into a module that had:

Option Explicit

At the top.

This forces you to declare each of your variables.

I think I'd something more like:

Option Explicit
Sub FormatMIdates()

Dim Cell As Range
Dim miDate As String
Dim pYear As String
Dim pMonth As String
Dim pDay As String
Dim NewDate As Date

For Each Cell In Selection.Cells
miDate = Cell.Value
miDate = Right(String(8, "0") & miDate, 8)
If CLng(miDate) > 0 Then
pYear = Left(miDate, 4)
pMonth = Mid(miDate, 5, 2)
pDay = Right(miDate, 2)
NewDate = DateSerial(pYear, pMonth, pDay)
Cell.Value = NewDate
End If
Next Cell

'Selection.NumberFormat = "@"
Selection.NumberFormat = "mm/dd/yyyy"

End Sub


By using dateserial(), you won't be affected by the way excel/windows sees dates
(mdy or dmy or...).





paulquinlan100@hotmail.com wrote:
>
> Hi
>
> Im trying to get the following code to run from a custom add-in button.
> The code works fine when its in the Workbook code area, however, when i
> put it into a module in order to package it as an add-in i get the
> error "Variable not defined" with cell highlighted. Any ideas?
>
> Thanks a lot for any suggestions.
>
> Paul
>
> Sub FormatMIdates()
>
> Selection.NumberFormat = "@"
> For Each cell In Selection
>
> midate = cell.Value
>
> If midate <> "" Then
>
> pYear = Left(midate, 4)
> pmonth = Mid(midate, 5, 2)
> pday = Right(midate, 2)
>
> newDate = pday + "/" + pmonth + "/" + pYear
>
> cell.Value = newDate
> End If
>
> Next
>
> End Sub

--

Dave Peterson