Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts

    Where's help for VBA in Excel

    I'm writing some VBA to read in info to an Excel workbook. The script is now working but requires some fettling. So I thought to use help (as one does). But it's useless, as it takes me to a web page which doesn't have anything about VBA other than for creating graphs (not what I want). I'm obviously missing something here. So where can I find a simple guide to VBA objects et al please?

    This is Excel 2013 (via O365).

    It was all so easy in O2003

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Thanks Rory, I have tried that page. I could only download in IE (wouldn't do anything in FF or Chrome). Trouble is, all the pages are blank, I can see the topics but I can't see any text. I thought the only reference to VBA was the graph one.

  4. #4
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    RTFM - I read the instructions on the download page and it tells me to click on unblock in the properties of the chm file. Suddenly I can read it!

    I'll see if this helps me.

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    I quick look and I'm not so sure. All I want to know (at this point) is what parameters a dim statement takes. I would also like to be able to see what parameters various statements have - like Mid or instr (I can never remember them). I've just found that right-clicking on such statements can give more info (right click is your friend!), but not for dim.

    It used to be that clicking help brought up a search box and I just entered the statement I wanted more info on. This doesn't happen in 2013 (unless I've missed something).

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    MSDN is your friend. VBA Language Reference.

    Dim Statement:

    Syntax

    Dim [WithEvents] varname[([subscripts])] [As [New] type] [, [WithEvents] varname[([subscripts])] [As [New] type]] . . .

    Details Here.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    I just found this which you may find useful:

    From the product (installed version): To view the version of this reference that is included with Office 2010, click the Help button in any Office product, and then click the Search drop-down arrow. Under Content from this computer, click Developer Reference.

    Gets you the same stuff in slightly different format.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Thanks RG, that first link looks good so I'll peruse it. The dim statement I wanted a list of all the 'type's. As I said, I have written the code and it works (reading data from a text file), but I want to improve it. The dates come with colons rather than slashes and I just want to see if the dim statement can convert them to 'real' dates so I don't have to process them.

    Your second post, I don't have a drop down arrow in the search box, see attachment
    search.png

    That's what the problem is - I can't do a quick search on a statement etc.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    Dates are only numbers that are interpreted in a certain manner (Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal date values can be assigned to Date variables.). Use the Format command to display the date the way you want it.

    i.e.: Format(dteVariable, Format)

    Code:
    Option Explicit
    
    Sub Test()
    
     Dim dteVariable As Date
    
     dteVariable = DateValue("June 25, 2014")
    
     Debug.Print Format(dteVariable, "mm/dd/yyyy")
    
    End Sub
    Result: 06/25/2014

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    I've tried that way of doing the dates but I get an error 13. type mismatch. Here's the full code and it's the line where DateShot = DateValue(myDate) where it fails

    Code:
    Sub Import_metadata()
    
    Dim FileName As String, Tags As String, Latitude As String, device As String, Longitude As String, DateShot As Date, myDate As String
    Dim lr As Integer, LatitudeR As String, LongitudeR As String, dir As String, PicType As String
    
    dir = InputBox("Enter directory (including final \)", "dir")
    PicType = "Digital"
    
    Open dir + "CommonMetadata.txt" For Input As #1
    
    lr = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    Line Input #1, a
    Do
        Line Input #1, a
        If InStr(a, "FileName") > 0 Then
            FileName = Mid$(a, 35)
        End If
        If InStr(a, "Keywords") > 0 Then
            Tags = Mid$(a, 35)
        End If
        If InStr(a, "GPSLatitudeRef") > 0 Then
            LatitudeR = Mid$(a, 35, 1)
            Line Input #1, a
            Latitude = Mid$(a, 35)
        End If
        If InStr(a, "GPSLongitudeRef") > 0 Then
            LongitudeR = Mid$(a, 35, 1)
            Line Input #1, a
            Longitude = Mid$(a, 35)
        End If
        If InStr(a, "Model") > 0 Then
            device = Mid$(a, 35)
        End If
        If InStr(a, "DateTimeOriginal") > 0 Then
            myDate = Mid$(a, 35)
            DateShot = DateValue(tDate)
        End If
        If InStr(a, "======") > 0 Then
            Worksheets("Sheet2").Cells(lr, 1) = FileName
            Worksheets("Sheet2").Cells(lr, 2) = Tags
            Worksheets("Sheet2").Cells(lr, 3) = Latitude
            Worksheets("Sheet2").Cells(lr, 4) = LatitudeR
            Worksheets("Sheet2").Cells(lr, 5) = Longitude
            Worksheets("Sheet2").Cells(lr, 6) = LongitudeR
            Worksheets("Sheet2").Cells(lr, 7) = device
            Worksheets("Sheet2").Cells(lr, 8) = DateShot
            Worksheets("Sheet2").Cells(lr, 9) = dir
            Worksheets("Sheet2").Cells(lr, 10) = PicType
            lr = lr + 1
            FileName = ""
            Tags = ""
            Latitude = ""
            LatitudeR = ""
            Longitude = ""
            LongitudeR = ""
            device = ""
            DateShot = ""
        End If
    Loop Until (EOF(1))
    Close #1
    
    End Sub
    As I said before, I'm now in fettling mode (having got it working) and this was one of the first 'fettles' I did.

    Here's an example of the date field

    1925:01:01 00:01:00+00:00

  11. #11
    jwoods
    Guest
    Quote Originally Posted by access-mdb View Post
    I've tried that way of doing the dates but I get an error 13. type mismatch. Here's the full code and it's the line where DateShot = DateValue(myDate) where it fails

    Code:
    Sub Import_metadata()
    
    Dim FileName As String, Tags As String, Latitude As String, device As String, Longitude As String, DateShot As Date, myDate As String
    Dim lr As Integer, LatitudeR As String, LongitudeR As String, dir As String, PicType As String
    
    dir = InputBox("Enter directory (including final \)", "dir")
    PicType = "Digital"
    
    Open dir + "CommonMetadata.txt" For Input As #1
    
    lr = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    Line Input #1, a
    Do
        Line Input #1, a
        If InStr(a, "FileName") > 0 Then
            FileName = Mid$(a, 35)
        End If
        If InStr(a, "Keywords") > 0 Then
            Tags = Mid$(a, 35)
        End If
        If InStr(a, "GPSLatitudeRef") > 0 Then
            LatitudeR = Mid$(a, 35, 1)
            Line Input #1, a
            Latitude = Mid$(a, 35)
        End If
        If InStr(a, "GPSLongitudeRef") > 0 Then
            LongitudeR = Mid$(a, 35, 1)
            Line Input #1, a
            Longitude = Mid$(a, 35)
        End If
        If InStr(a, "Model") > 0 Then
            device = Mid$(a, 35)
        End If
        If InStr(a, "DateTimeOriginal") > 0 Then
            myDate = Mid$(a, 35)
            DateShot = DateValue(tDate)
        End If
        If InStr(a, "======") > 0 Then
            Worksheets("Sheet2").Cells(lr, 1) = FileName
            Worksheets("Sheet2").Cells(lr, 2) = Tags
            Worksheets("Sheet2").Cells(lr, 3) = Latitude
            Worksheets("Sheet2").Cells(lr, 4) = LatitudeR
            Worksheets("Sheet2").Cells(lr, 5) = Longitude
            Worksheets("Sheet2").Cells(lr, 6) = LongitudeR
            Worksheets("Sheet2").Cells(lr, 7) = device
            Worksheets("Sheet2").Cells(lr, 8) = DateShot
            Worksheets("Sheet2").Cells(lr, 9) = dir
            Worksheets("Sheet2").Cells(lr, 10) = PicType
            lr = lr + 1
            FileName = ""
            Tags = ""
            Latitude = ""
            LatitudeR = ""
            Longitude = ""
            LongitudeR = ""
            device = ""
            DateShot = ""
        End If
    Loop Until (EOF(1))
    Close #1
    
    End Sub
    As I said before, I'm now in fettling mode (having got it working) and this was one of the first 'fettles' I did.

    Here's an example of the date field

    1925:01:01 00:01:00+00:00
    Your code is using tDate, not myDate.

    DateShot = DateValue(tDate)

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    Could you please provide a sample data file with a couple of lines of data?

    I assume that the first "Line Input" command is to do away with a header line?

    If you use Option Explicit at the top of all your modules the compiler will catch variables that are not defined like the one pointed out by jwoods.

    Also it is a better practice to do one variable definition per line. Easier to read.

    FYI: You use the variable "dir". You should not use the names of commands as variables as it can cause unpredictable results.

    HTH
    Last edited by RetiredGeek; 2015-06-06 at 07:01.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    OK, I've fettled a bit more! Note that I had spotted the tDate typo and had corrected it without success - obviously I hadn't corrected the code in the post - sorry for that.

    I've attached the spreadsheet and a sample data file (should have three entries).

    I will be fettling more (the input is clumsy and other coding issues) though I've never defined variables one per line before - perhaps I should!
    Attached Files Attached Files

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    Here's a version of your code that just cleans up a few things:
    Code:
    Option Explicit
    
    Sub Import_metadata()
    
       Dim zFileName     As String
       Dim zTags         As String
       Dim zLatitude     As String
       Dim zDevice       As String
       Dim zLongitude    As String
       Dim zMyDate       As String
       Dim zLatitudeR    As String
       Dim zLongitudeR   As String
       Dim zSrcDir       As String
       Dim zPictType     As String
       Dim zRawData      As String
       Dim dteDateShot   As Date
       Dim lLastRow      As Long
       Dim sht2          As Worksheet
    
    '*** Setup ***
       Set sht2 = Worksheets("Sheet2")
       lLastRow = sht2.Cells(Rows.Count, "A").End(xlUp).Row + 1
       zPictType = "Digital"
    '*** End Setup ***
    
       zSrcDir = InputBox("Enter zSrcDirectory (including final \)", "zSrcDir")
    
       Open zSrcDir + "CommonMetadata.txt" For Input As #1
    
       Line Input #1, zRawData  '*** Discard First Line ***
       
       Do
         Line Input #1, zRawData
        
         If InStr(zRawData, "FileName") > 0 Then
             zFileName = Mid(zRawData, 35)
         End If
        
         If InStr(zRawData, "Keywords") > 0 Then
           zTags = Mid(zRawData, 35)
         End If
        
         If InStr(zRawData, "GPSLatitudeRef") > 0 Then
           zLatitudeR = Mid(zRawData, 35, 1)
           Line Input #1, zRawData
           zLatitude = Mid(zRawData, 35)
         End If
        
         If InStr(zRawData, "GPSLongitudeRef") > 0 Then
           zLongitudeR = Mid(zRawData, 35, 1)
           Line Input #1, zRawData
           zLongitude = Mid(zRawData, 35)
         End If
        
         If InStr(zRawData, "Model") > 0 Then
           zDevice = Mid(zRawData, 35)
         End If
        
         If InStr(zRawData, "DateTimeOriginal") > 0 Then
           zMyDate = Mid(zRawData, 35)
           dteDateShot = DateValue(zMyDate)
         End If
        
         If InStr(zRawData, "======") > 0 Then
           With sht2
               .Cells(lLastRow, 1) = zFileName
               .Cells(lLastRow, 2) = zTags
               .Cells(lLastRow, 3) = zLatitude
               .Cells(lLastRow, 4) = zLatitudeR
               .Cells(lLastRow, 5) = zLongitude
               .Cells(lLastRow, 6) = zLongitudeR
               .Cells(lLastRow, 7) = zDevice
               .Cells(lLastRow, 8) = dteDateShot
               .Cells(lLastRow, 9) = zSrcDir
               .Cells(lLastRow, 10) = zPictType
           End With 'sht2
            
           '*** CLean Up ***
           lLastRow = lLastRow + 1
           zFileName = ""
           zTags = ""
           zLatitude = ""
           zLatitudeR = ""
           zLongitude = ""
           zLongitudeR = ""
           zDevice = ""
           dteDateShot = ""
         End If
        
       Loop Until (EOF(1))
    
       Close #1
    
    End Sub 'Import_metadata()
    Of course, the above is my personal coding style. YMMV!

    I have a question about halfway down your IF's you read a new line. Shouldn't the following if's be included/nested in the IF that reads the new line? Although your code will work as written it is inefficient (executing code that is not necessary) since as I "assume" the values checked for only exist if the new line is read?

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Sorry RG but that still doesn't work on my spreadsheet, still the same line; I assume it works on yours.

    Obviously I could do some processing of the relevant data to create a genuine date format, but I wanted to be a bit more elegant in my coding, hence my request.

    The reason why the lat and long sections are the way they are is that all the fields looked for won't always be present (otherwise I could use the Case construct (maybe I could, I haven't fettled that far yet )).

    My method of writing code from scratch (better to reuse other scripts if possible), is to build it up line by line, then fettle it (that word again) so that I know I'm fettling from a solid base.

    BTW it must be late there!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •