Reference Cell in Excel VBA

I'm trying to reference the date value in a specific cell on my worksheet to use in my VBA code. But I can't seem to get it to work. Here is what I'm using to pull the data from that cell:

    Dim WorkDay As String

    Set WorkDay = Sheets("Sheet1").Range("G1")

Open in new window


Then I want to use that date value in a line of code that sends an email from excel vba. I want to put that value in the textbody portion of the email. Here is the line of code for that:

.TextBody = "As Of " & WorkDay

Open in new window



What am I doing wrong? It's not pulling the value. I'm getting a compile error "Object Required".
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
You are now using the wrong format; that's an Excel format, not an Excel VBA format.

Lines 9-11 should read:

    Dim Workday As String
  
    Workday = Sheets("Sheet1").Range("G1").Value

Open in new window

0
 
johnb25Commented:
Try this; you need to pick up the Value property.
Also, you don't need the Set command for the Variable.

 
  WorkDay = Sheets("Sheet1").Range("G1").Value

Open in new window


john
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this instead:

 Dim WorkDay As String

    WorkDay = Sheets("Sheet1").Range("G1").Value

Debug.Print "As Of " & WorkDay

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The reason for the problem is you said:

Dim WorkDay As String

So it is expecting a String.

Then you say:

Set WorkDay = Sheets("Sheet1").Range("G1")

What is the result of this - a Range! So it is conflicted between expecting a String and getting a Range.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks John. Now I'm getting another error when I switch the code to your revised code. I'm getting "The specified dimension is not valid for the current chart type". Which has nothing to do with my email code or referencing that cell. When I click the "Debug" button it goes to that new line of code you changed for me. How and why would I get that message now?
0
 
Harisha M GCommented:
You can simply write like this:

WorkDay = [Sheet1!G1]
.TextBody = "As Of " & WorkDay

Open in new window

0
 
johnb25Commented:
Sorry, I did not mean the  Dim WorkDay As String line to be left out; that should still be there.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
@ mgh

I'm getting a "Type Mismatch" error now.

@ John

I had the Dim WorkDay as String line still in there when I got that error message.
0
 
Harisha M GCommented:
Try with this:

WorkDay = [Sheet1!G1].Value2
.TextBody = "As Of " & WorkDay

Open in new window


By the way, why do you have WorkDay defined? Do you have Option Explicit at the top?
0
 
johnb25Connect With a Mentor Commented:
This works for me...can you try this on its own to eliminate anything else:

 Sub test()
  Dim Workday As Date
  
  Workday = Sheets("Sheet1").Range("G1").Value
  Debug.Print Workday
    
  End Sub

Open in new window

0
 
johnb25Commented:
Dim Workday as String also works....
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok it works when I test it using the subTest(). But it still won't work when I add it to my email code. Here's the entire email code function. Maybe you can see what is causing it to error out.

Sub CDO_Mail_Workbook()
    Dim WB As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    Dim Workday As Date
  
    Workday = Sheets("Sheet1").Range("G1").Value
    Set WB = ActiveWorkbook
    Set WB = Workbooks("BC Region Sales.xlsx")

    'This code pulls all the email address from the hidden sheet SHEET1 and loads them into the TO line of code
    Dim cell As Range
    Dim strto As String
    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("Sheet1") _
        .Range("A1:A4").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    On Error GoTo 0
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
    
    If Val(Application.Version) >= 12 Then
        If WB.FileFormat = 51 And WB.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                   "Save the file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Make a copy of the file/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    'TempFileName = "Copy of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    TempFileName = "BC Region Sales"
    FileExtStr = "." & LCase(Right(WB.Name, Len(WB.Name) - InStrRev(WB.Name, ".", , 1)))

    WB.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "larry@xyzcompany.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .To = "larry@xyzcompany.net"
        .CC = ""
        .BCC = ""
        .From = """Reports"" <Reports@xyzcompany.com>"
        .Subject = "Daily BC Region Sales"
        .TextBody = "As Of " & Workday
        .AddAttachment TempFilePath & TempFileName & FileExtStr
        .Send
    End With

    'If you do not want to delete the file you send delete this line
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
 
Harisha M GConnect With a Mentor Commented:
Here you are trying to append a string and a date, which won't work:

.TextBody = "As Of " & Workday

So, change WorkDay to String as you had earlier (or remove altogether), and fetch the value using "Value" or "Value2"

Workday = [Sheet1!G1].Value2
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok I changed it to a string and then to the new code. Now I'm getting "Object Required" and it highlights this line of code:

Workday = [Sheet1!G1].Value2

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You don't need the "2" at the end.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Still getting the same error even without the 2 at the end.
0
 
johnb25Commented:
The date does not seem to be the issue:

?iMsg.textbody
As Of 12/05/2014

I can run it through to AddAttachment, and I get an automation error.
Probably due to my set-up, e.g. not having a library installed.

How far does your code run?
Test the variables up to that point by typing ?VariableName in the Immediate window

John
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The code stops at that WorkDay line of code. When I remove that line and put the code back to the original way the email function works fine. It always worked fine until I added that line of code.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Are you saying that it stops in line 11? Is there a spreadsheet called Sheet1 in the Active Workbook?

Maybe you should post the entire spreadsheet.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes the code stops at line 11. There is a sheet called Sheet1. That's where it also pulls the email addresses for that email code as well. The spreadsheet is rather large and has a lot of links that would be difficult to break and show as a sample. That's why I just posted the entire email code. I just took the WorkDay code out and it works like a charm. I just don't get why it won't work by just adding that line of code.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I just took the WorkDay code out

Can you please give line numbers. It's difficult to follow you with "that line of code".

Maybe line 11 should be expanded to:

Workday = ActiveWorkbook.Sheets("Sheet1").Range("G1").Value

Open in new window


or, if it is a different workbook

Workday = Workbooks("NAME OF WORKBOOK").Sheets("Sheet1").Range("G1").Value

Open in new window

0
 
johnb25Commented:
Hi,

Workday is also the name of an Excel formula; can you change the variable name to something else and then try it.
Workday worked ok for me, but there is obviously something different in your setup.

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.