[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to remove run-time error '457' in my PP macro?

Posted on 2011-10-20
2
Medium Priority
?
233 Views
Last Modified: 2012-05-12
Hi experts,
I need your help to eliminate errors in a macro that I use. My macro reports an error when running on 29 row my code:
If ITM <> "" Then dict.Add ITM, ITM
For better orientation, attach a printscreen error, my project where I need to generate random numbers without repetition.
Thank you in advance for any help,
TM
Run-time Error test-random-number.ppt
Function getnum2() As Long
Dim lngLow As Long
Dim lngHigh As Long
Dim lngNum As Long
Static dict As Object
Dim fso As Object
Dim readFile As Object
Dim strFileText As String
Dim arr As Variant
Dim itm As Variant
Const strFileName As String = "c:\deleteme\somefile4.txt"

    
    Randomize Now()
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(strFileName) Then
        fso.createTextFile strFileName, False
    End If
    If dict Is Nothing Then
        Set dict = CreateObject("scripting.dictionary")
        Set readFile = fso.OpenTextFile(strFileName, 1, False)
        If readFile.atendofstream Then
            readFile.Close
        Else
            strFileText = readFile.ReadAll
            readFile.Close
            arr = Split(strFileText, vbCrLf)
            For Each itm In arr
                If itm <> "" Then dict.Add itm, itm
            Next
        End If
    End If
    lngLow = 200
    lngHigh = 45000000
    lngNum = CLng((lngHigh - lngLow + 1) * Rnd() + lngLow)
    Do While dict.exists(lngNum)
        lngNum = CLng((lngHigh - lngLow + 1) * Rnd() + lngLow)
    Loop
    dict.Add lngNum, lngNum
    Set readFile = fso.OpenTextFile(strFileName, 8, False)
    readFile.WriteLine (lngNum)
    readFile.Close
    getnum2 = lngNum

End Function

Open in new window

0
Comment
Question by:Thomas_Meyer
2 Comments
 
LVL 4

Accepted Solution

by:
CharlWiehahn earned 2000 total points
ID: 37003415
Hi Thomas,

I think you are experiencing this error because the value are writing into your somefile4.txt is not actually random. So when you run the script again and it gets to line 29 you are entering the same item into the dict dictionary twice and this is not allowed.

I am not sure if this will work for you, but try adding the following code and seeing if your script completes.


For Each itm In arr
	If itm <> "" Then
		Count = Count + 1
		dict.Add Count, itm
	End If
Next

Open in new window

0
 

Author Closing Comment

by:Thomas_Meyer
ID: 37006324
Perfect, the error seems to be removed.
Thank you for your help.
TM
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question