Tags: add-in, area, button, code, custom, excel, following, microsoft, msdn, procedure, run, software, workbook, worksheet

Worksheet/General Procedure problem

On Microsoft » Microsoft Excel

3,334 words with 2 Comments; publish: Thu, 22 May 2008 18:20:00 GMT; (306107.42, « »)

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 = ".excel.todaysummary.com."

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

All Comments

Leave a comment...

  • 2 Comments
    • Dim cell as Range

      --

      Gary's Student

      "paulquinlan100.excel.todaysummary.com.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 = ".excel.todaysummary.com."

      > 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

      >

      #1; Thu, 22 May 2008 18:21:00 GMT
    • 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 = ".excel.todaysummary.com."

      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.excel.todaysummary.com.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 = ".excel.todaysummary.com."

      > 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

      #2; Thu, 22 May 2008 18:22:00 GMT