Posts by lewisgmorris


    I appreciate your comments, what do you do as a full time job?


    To add - I wasn't using this as something that would genuinely be used by people, just as a platform to showcase what I'm able to do.

    Having said that, as Pike said, you obviously know what you are doing with VBA coding so I doubt you would get any meaningful improvement suggestions if the entire code was reviewed.


    I want someone to tell me either "go back to the drawing board and learn more" or "what are you doing working in a warehouse half your day and the other half being a dogsbody to the boss. You should be a full time programmer."

    Agreed, nice code! :)


    I seriously suggest you run a virus scan on your file. I doubt it is the code that is causing the alert when trying to download your file, but a virus that has somehow got attached to your file.



    I've scanned it and as I suspected it believes its a virus because it downloads the updated version and runs it BUT ONLY IF THE USER ACCEPTS. This could be used maliciously and thats why its been flagged by ZoneAlarm as a trojan downloader ...



    I am 100% positive there is no virus in my code. This is a classic examples of how a virus scanner can misinterpret files sometimes.


    [Blocked Image: https://i.imgur.com/o9u4BbW.png]


    You can even navigate to http://schipshop.co.uk/TSP/version.php?action=3 in your browser and check the downloaded file if you so wish. It's just version 1.17


    Just as a side note - It also logs in a mySQL database whos downloaded and whos opened etc when you do !! :) So i'm watching you. LOL


    Who's 1.156.6.41? Would like to know what you thought?


    [Blocked Image: https://i.imgur.com/bb4qEoZ.png]

    I cannot even download it. As I download VBA enabled workbooks all day then I find this suspicious. maybe it's th download site, zip it and attach here


    You sound like you know what your talking about - really would appreciate comments from you.


    Not sure if your anti-virus is on red alert because i've coded in a way to update the workbook & code to the latest version. It checks my website for the latest version number via PHP and then downloads if a new version is found, it then creates a batch file to close the excel process, rename the newly downloaded file and overwrite the current workbook with the new one. Then reopens the newly downloaded file. That could be misconstrued as malicious I guess.


    I've put an excerpt of the update module below.


    Zipped version attached.



    [VBA]
    Option Explicit
    Sub checkForNewVersion()


    Dim ws As Worksheet
    Dim wbLoc As String
    Dim location As String
    Dim currentV, webV As Double
    Set ws = Worksheets("info")
    currentV = ws.Range("B1")
    'set current run location
    wbLoc = Application.ActiveWorkbook.Path
    'get the current web version
    If getCurrentVersion(webV, location) <> "Error" Then
    If webV > currentV Then
    'ask user if they want to update
    If MsgBox("Update Available..." & vbNewLine & vbNewLine & "Current version V" & currentV & " lastest version V" & webV & vbNewLine & vbNewLine & "Would you like to download and run?", vbOKCancel) = vbOK Then
    If MsgBox("This will close all running instances of Excel, please proceed with caution. Are you sure you would like to update?", vbYesNo) = vbYes Then
    'if yes download new version if error exit with message
    If CDbl(libFunctions.downloadImage("http://schipshop.co.uk/TSP/version.php?action=3", wbLoc & "\TSP Genetic Algorithm Web.xlsm")) = 0 Then
    createBatchCloseExcelAndReopen Application.ActiveWorkbook.FullName
    Debug.Print
    Else
    GoTo errExit
    End If
    End If
    End If
    End If


    End If


    Exit Sub
    errExit:
    MsgBox "There was a critical error downloading latest version", vbCritical
    End Sub



    Function getCurrentVersion(version As Double, location As String)


    Dim doc As MSHTML.HTMLDocument
    Dim xm As New XMLHTTP60
    'Dim xm As New InternetExplorer
    On Error GoTo errTime:
    xm.Open "Get", "http://schipshop.co.uk/TSP/version.php?action=1", False
    xm.send
    Set doc = New MSHTML.HTMLDocument
    doc.body.innerHTML = xm.responseText
    version = doc.getElementById("Version").innerText
    location = doc.getElementById("File").innerText
    getCurrentVersion = "Ok"
    Exit Function
    errTime:
    version = "Error"
    location = "Error"
    getCurrentVersion = "Error"
    End Function



    Function createBatchCloseExcelAndReopen(workbookPath)
    Dim fso As New FileSystemObject
    Dim fl As TextStream
    Dim loc As String
    loc = ActiveWorkbook.Path
    Set fl = fso.OpenTextFile(loc & "\update.bat", ForWriting, True)
    With fl
    .WriteLine "taskkill /f /im excel.exe"
    .WriteLine "timeout 3"
    .WriteLine "del """ & workbookPath & """ /f /q"
    .WriteLine "timeout 1"
    .WriteLine "ren " & """" & loc & "\TSP Genetic Algorithm Web.xlsm" & """" & " " & """" & "TSP Genetic Algorithm.xlsm" & """"
    .WriteLine "del """ & loc & "\TSP Genetic Algorithm Web.xlsm" & """ /f /q"
    .WriteLine "msg * /self /w ""TSP genetic algorithm has successfully been updated"""
    .WriteLine "START """" excel.exe """ & loc & "\TSP Genetic Algorithm.xlsm" & """"
    .WriteLine "DEL ""%~f0"""
    .WriteLine "exit 0"
    End With
    fl.Close
    Call Shell(loc & "\update.bat")

    End Function


    [/VBA]

    I would appreciate you read this, it is a bit wordy and maybe not suited for this sub-forum. Please let me know if this is the case. There is code review but it's rather lengthy to post directly here so there is a link to download the workbook.



    I started working in a sales role with a small family run wholesaler nearly 10 years ago. They soon realised that I could do a great deal more than just sales because I was leaps and bounds better than any of them with computers. (I have had one since 1992 – you just get good at something when you use it every day). Soon enough the jobs were piling up all over the place and I just didn’t have the time to do it all myself, I turned to excel, which I had used before but had never had the need to master in the past. I realised the old adage rang true “Work smarter not harder” and excel helped me accomplish this but I still had more jobs than I could chew and needed to find better ways to streamline my work and give me the upper hand.


    I learned you could use excel to RECORD tasks and play them back in excel, how amazing is this, it was a lightbulb moment for me, I squirreled away for months automating my tasks to some degree just solely recording macros with no programming knowledge and no idea of what the code meant, I chopped and changed it into a somewhat coherent mess of functions that I was using a daily basis to speed up my workflow. Then I realised that this can’t be it. This can’t be the limits of what excel can do…


    I started researching VBA and integrating small chunks of it into my work, slowly learning from my mistakes, writing and rewriting code until it worked. Slowly gaining traction day by day learning at home in my spare time, from books and online resources. I’ve reached a point where I think I'm fairly good at it.


    But now I need some help!


    I’ve now hit an inspiration roadblock and don’t know what to do or where to turn, I don’t know if I should keep working away with VBA or move on, I’ve had a dabble with VB.net, python, r & others and I seem to pick it all up very quickly.


    I’m also bored at work, I feel undervalued, underappreciated and overqualified for my position, and I’ve got no further up the ladder I can go (being a small company there is only a few rungs). I want to be a full time programmer and not someone who just puts the knowledge to use for his own personal gain.


    I lack the confidence to apply for jobs relating to VBA, and I don’t even know if I am technically any good. I’ve got no friends in the profession & only have the opinions of my office to go by (but some struggle to turn a pc on).


    I need some motivation, some guidance and some direction, so I’ve been working on a genetic algorithm to calculate the travelling salesman problem, to showcase what I can do. I've used all the tricks in my arsenal to try and impress.


    I’m looking for someone to have a look over it and give me your honest opinion of my code, let me know your thoughts either way, good or bad.


    You can download it here. http://schipshop.co.uk/TSP/TSP Genetic Algorithm V1.15.xlsm or see the attachment to this post.


    Any comments would be appreciated, any idea on what I should do next, where I should go from here or what I should focus on? Anything you can offer would be amazing for me.


    Thanks


    Lewis