Excel

Sample Data Maker

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

On running the code twelve hundred rows of fake data is generated. (A progress bar shows the completed number of rows of data) 

Discussion:

You may want to demonstrate how data can be collated/generated with VBA coding 

Code:

instructions for use

			

'************************************ '<< CODE FOR MODULE1 >> Option Explicit Sub UndoIt() Sheet2.Cells.Copy Destination:=Sheet1.Cells End Sub Sub SampleData() UndoIt '< (just in case user forgets) UserForm1.Show End Sub '************************************ '************************************ '<< CODE FOR USERFORM1 MODULE >> Option Explicit Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'cancel user clicking userform X If CloseMode = 0 Then Cancel = True End Sub Private Sub UserForm_Activate() Dim RanNum As Long, N As Long 'position labels etc. With UserForm1 .Width = 270 .Height = 72 End With With TextBox1 .Width = 240 .Height = 14 .Top = 29 .Left = 10 .Width = 244 End With With Label1 .Width = 1 .Height = 9 .Top = 31.5 .Left = 12 .BackColor = &HFF& End With With Label2 .Width = 155 .Height = 14 .Top = 8 .Left = 9 .Font = "Verdana" .Font.Size = 10 End With DoEvents 'create dummy data Application.ScreenUpdating = False For N = 2 To 1201 'show progress Label1.Width = N / 5 Label2.Caption = "Progress: " & (100 * N / 1201) \ 1 & "%" UserForm1.Caption = "Processing row " & N - 1 & " of 1200" DoEvents Randomize GetNum1: 'N.B. A to Z = Chr(65) to Chr(90) RanNum = Int((90 * Rnd) + 1) If RanNum < 65 Then GoTo GetNum1 'put 1st random letter at front of text in col A Range("A" & N) = Chr(RanNum) & Range("A" & N) GetNum2: RanNum = Int((90 * Rnd) + 1) If RanNum < 65 Then GoTo GetNum2 'put 2nd random letter at front of text in col B Range("B" & N) = Chr(RanNum) & Range("B" & N) GetNum3: RanNum = Int((90 * Rnd) + 1) If RanNum < 65 Then GoTo GetNum3 'put 3rd random letter in col C Range("C" & N) = Chr(RanNum) With Range("D" & N + 1) .NumberFormat = "mm dd yyyy" .Value = Range("D" & N) + 1 End With With Range("E" & N + 1) .NumberFormat = "mm dd yyyy" .Value = Range("E" & N) + 1 End With With Range("F" & N + 1) .NumberFormat = "0.00" .Value = Range("F" & N) + 1 End With With Range("G" & N + 1) .NumberFormat = "0.00" .Value = Range("G" & N) + 1 End With With Range("H" & N + 1) .NumberFormat = "00000" .Value = Range("H" & N) + 1 End With Next N Unload Me 'tidy up Rows(1202).EntireRow.Clear Cells.Columns.AutoFit Application.ScreenUpdating = True End Sub '************************************

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Userform (this will create Userform1)
  4. In the Toolbox shown, click 'textbox', now put the mouse pointer near the top left of the Userform and move the pointer down and to the right to create TextBox1
  5. From the Toolbox, click 'label', now put the mouse pointer near the top left of the Userform and move the pointer down and to the right to create Label1
  6. Repeat to create Label2 (need 2 labels)
  7. Double-click the userform and a code pane will appear
  8. Delete any code in this code pane and copy and paste the code for Userform1 from above
  9. Select Insert/Module, copy and paste the code for Module1 from above into it
  10. Now select File/Close and Return To Microsoft Excel
  11. Save your work
 

Test the code:

  1. It's easiest to download the zip attachment, unzip and open the workbook SampleDataMaker
  2. You can run the code in this demonstration workbook.
  3. Alternatively, copy Sheet 1 and paste this into the workbook you've coded (or else create your own base data)
 

Sample File:

SampleDataMaker.zip 43.82KB 

Approved by mdmackillop


This entry has been viewed 468 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express