Consulting

Results 1 to 11 of 11

Thread: VBA / Formula approach

  1. #1
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location

    Question VBA / Formula approach

    I need to gather information from my users regarding the operating system and version of office in use. I would like this to be in a set format, and detailed...without the user having to run a macro (manually).

    My thoughts were to put together a workbook with an 'Open' event that looks at the registry to get this information, which is then presented in the workbook via a UDF e.g.

    =SystemVersion() {Windows 7 SP1, Windows XP SP2, Windows 2000, Windows Vista SP2}

    =OfficeVersion() {Office 2000, Office 2003, Office 2007 12.0, Office 2010}

    Any help would be appreciated. I tried using =INFO but would like more information than 'pcdos' and '12.0'.

    Using this method the user could then simply copy the cells and drop them to me in an email...

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    You could use
    Application.OperatingSystem
    Application.Version
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  3. #3
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location

    Question

    Tried this and doesn't work :

    [vba]Function OperatingSystem(sOSVer As String)
    Dim sOSVer As String
    sOSVer = Application.OperatingSystem
    End Function[/vba]

    But if it put it in a sub with a MsgBox then it does work... :/

    Think I am misunderstanding the differentiation of :

    Property
    Function
    Sub
    Last edited by Aussiebear; 04-18-2012 at 05:02 PM. Reason: Added vba tags to code

  4. #4
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Hi,

    You are double declaring your string. You don't need to pass the function anything so it would be. You also didn't define the function as the value.

    [VBA]Function fnOperatingSystem()
    Dim sOSVer As String
    sOSVer = Application.OperatingSystem
    fnOperatingSystem= sOSVer
    End Function[/VBA]

    To be honest for something this simple I wouldn't bother with a function. Just use a variable directly in your sub and assign the value directly to it. If you want to use it as a function the above will work and you would call it with a sub.

    [VBA]Sub getos()
    Dim sOS As String
    sOS = fnOperatingSystem
    MsgBox (sOS)
    End Sub[/VBA]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  5. #5
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location

    Question

    This still returns non-useful info e.g. I am using Windows 7 Enterprise but returns the Net version.

    Is there a newer version of this code available as (at the time) it appeared to cover all versions... ?

    http://www.vbaexpress.com/forum/show...61&postcount=2

    Looks like it may have started life here : http://vbadud.blogspot.co.uk/2010/05...using-vba.html

    And an incomplete list of codes vs. real world OS :
    http://www.officekb.com/Uwe/Forum.as...m-returns-what

  6. #6
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    This is generally what you are looking for. It doesn't quite work as it needs the 32 bit or 64 bit returns and I don't currently have time to input all that. My suggestion is to get a few users to return you the raw data and then you can convert it once you have a better list.

    [VBA]Function fnOperatingSystem()
    Dim sOSVer As String
    sOSVer = Application.OperatingSystem
    Select Case sOSVer
    Case "Windows NT 6.1"
    fnOperatingSystem = "Windows 7"
    Case "Windows NT 6.0"
    fnOperatingSystem = "Windows Vista"
    Case "Windows NT 5.2"
    fnOperatingSystem = "Windows Server 2003; Windows XP x64 Edition"
    Case "Windows NT 5.1"
    fnOperatingSystem = "Windows XP"
    Case "Windows NT 5.01"
    fnOperatingSystem = "Windows 2000, Service Pack 1 (SP1)"
    Case "Windows NT 5.0"
    fnOperatingSystem = "Windows 2000"
    Case "Windows NT 4.0"
    fnOperatingSystem = "Microsoft Windows NT 4.0"
    Case "Windows 98; Win 9x 4.90"
    fnOperatingSystem = "Windows Millennium Edition (Windows Me)"
    Case "Windows 98"
    fnOperatingSystem = "Windows 98"
    Case "Windows 95"
    fnOperatingSystem = "Windows 95"
    Case "Windows CE"
    fnOperatingSystem = "Windows CE"
    End Select
    End Function[/VBA]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  7. #7
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location
    Perfect thanks

    I have put this together in the meantime (checks for VB7 and 64 bit instlallations)

    Will expand to include your workings...

    [vba]
    Function OperatingSystem()
    Dim sOSVer As String
    sOSVer = Application.OperatingSystem
    OperatingSystem = sOSVer
    End Function
    Function OfficeVersion()
    Dim sOffVer As String
    sOffVer = Application.Version
    OfficeVersion = sOffVer
    End Function
    Function GetOS()
    Dim OS
    For Each OS In GetObject("winmgmts:").InstancesOf("Win32_OperatingSystem")
    GetOS = OS.Caption
    Next OS
    Set OS = Nothing
    End Function
    Function WhichBit()
    If WIN64 Then
    WhichBit = True
    Else
    WhichBit = False
    End If
    End Function
    Function WhichVBA()
    If VBA7 Then
    WhichVBA = True
    Else
    WichVBA = False
    End If
    End Function
    [/vba]

    Formulas :

    =OperatingSystem()

    =GetOS()

    =OfficeVersion()&" ("&IF(whichbit(),"64-Bit","32-Bit")&")"&"("&IF(WhichVBA(),"VB7","VB6")&")"

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Correct the last WhichVBA.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Also need the conditionals

    [vba]
    Function WhichBit()
    #If Win64 Then
    WhichBit = True
    #Else
    WhichBit = False
    #End If
    End Function
    Function WhichVBA()
    #If VBA7 Then
    WhichVBA = True
    #Else
    WhichVBA = False
    #End If
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by theta
    Perfect thanks

    I have put this together in the meantime (checks for VB7 and 64 bit instlallations)

    Will expand to include your workings...
    Why bother, your GetOS() already does that?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location
    I have been reading two VBA books -

    "Dummies Guide to Excel VBA Programming" - hate the 'dummies' range but it was written by a respected Excel expert.

    "VB & VBA In a Nutshell" - standard O'Reilly reference

    Am also reading up on Windows 32 API and will be studying as much information that I can get on ADO DB, ODBC etc...

    But cannot find any (immediate) reference to 'conditionals' - google isn't giving me much but guess I am not framing the question properly. Does anybody have a good resource to explain this?

Posting Permissions

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