March 21, 2009

Unique Random Numbers in VBA

As mentioned in my last post, this is how I got random numbers for my new Concentration game. It uses the fact that only unique items (as far as I know) can be added to a collection. After attempting to add a new item (random integer), the code then counts the items. If continues to do this until the limit (in this case 52) is reached.

Sub GetUniqueRandomNumbers()
    Dim iAdd As Integer
    Dim iLimit As Integer
    Dim iCheck As Integer
    Dim iRnd As Integer
    Dim UniqueNumbers As New Collection


    iLimit = 52

    For iAdd = 1 To iLimit


        iRnd = Int((iLimit - 1 + 1) * Rnd + 1)

        On Error Resume Next
        UniqueNumbers.Add iRnd, CStr(iRnd)
        On Error GoTo 0

    Next iAdd

    If UniqueNumbers.Count < iLimit Then GoTo StartAgain

    For iCheck = 1 To iLimit

        ActiveSheet.Cells(iCheck, 1).Value = UniqueNumbers.Item(iCheck)

    Next iCheck

End Sub

As promised, the Concentration game has been addded to JMT Excel Utilities, along with some other new utilities.

Export Charts As Images
This allows you to choose what charts (all of them if you want) to export in the active workbook in 3 different image formats.

Show Fonts Sheet
This shows what fonts you can use with Excel, as well as samples of how the fonts appear.

In the near future, I hope to add some more chart utilities. But for now, you can download the most recent version here.

I have also improved the code for Classic Excel Colors. It's download link is here.  

Posted by andrewe at 09:51

March 16, 2009


There is nothing like making a new game for a change of pace. This one will be added to JMT Excel Utilities soon but you can try an advance version if you like.

Here is the download link. I'll also post how to get random numbers to shuffle the cards :-)  
Posted by andrewe at 19:30

March 09, 2009

New Tools

JMT Excel Utilities has 3 new tools -

  • Highlight Row Differences (as previously promised)
  • Compare Sheets
  • Copy VBE Modules

    The following tools were re-written to work properly with Excel 2007.

  • Delete Active Workbook
  • Rename Active Workbook

    Rounded Comments has been updated too. You can now drag the form to make it larger (or smaller). Great for those oversize comments.

  • Posted by andrewe at 19:34

    March 01, 2009

    Document Properties 2

    CustomDocumentProperties are as the name custom properties that you can add to a workboook, just as BuiltinDocumentProperties exist by default.

    You can add CustomDocumentProperties using a variation of this code.
    ThisWorkbook.CustomDocumentProperties.Add LinkToContent:=False, Name:="Internal Use", Type:=msoPropertyTypeBoolean, Value:=True

    Looking at the Help files, we see that Type in this case is msoPropertyTypeBoolean, there are five types in MsoDocProperties Enumeration.






    I think msoPropertyTypeFloat is use for decimal numbers (msoPropertyTypeNumber is used for integers or whole numbers)

    Looking at the bigger picture, CustomDocumentProperties allow you to save your own information regarding a workbook. You can also use save settings to the registry, write to another file such as a text file, or add things to the workbook's cells, comments, even add VBA code to a module. But CustomDocumentProperties have the advantages of both going everywhere the workbook goes and being less likely to be deleted by mistake.
    Posted by andrewe at 10:32