transform a very simple macro into a udf ?

[excelNewbie22]

hi,
so i have randbetween generate random numbers,
and i need to copy the results as values only,
so i thought recording a simple macro
and modify it to an udf
like:

=CopyValOnly(a1:f1)

with a range of my choosing
so i can place the formula/udf which consistently copy the random numbers as values only
hope i explained right
can anyone help me out?

Sub copyval()
'
' copy macro1
'

'
    Range("A1:D5").Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Joe4]

Note that a UDF is really just a formula, and as such, can ONLY return values to the cell it resides in (like any other formula).
So you can either have a formula or a hard-coded value in a cell, but never both at the same time, and no formula (or UDF) will automatically convert itself from a formula to a hard-coded formula.
A UDF really is just a formula, so it behaves like any othe built-in Excel formula.

What you need is a Sub Procedure (aka "Macro") that you can run.
You can either have it run on the selected cells, or prompt the user to input the range.
Here is one that runs on the currently selected cells and changes to formulas to hard-coded values:

Sub MyCopyValues()
    Selection.Value = Selection.Value
End Sub

 

[excelNewbie22]

thanks joe, i'm not sure i understand correctly,
i need the 'formula' to run like MyCopyValues(a1:d5) at F1 (example) and return the same value as in a1:d5 (which is random numbers),
and what you saying it isn't possible?
i tried your macro, it worked fine on the selected range, a1:d5, but not with "MyCopyValues(a1:d5)", but also i need to keep the a1:d5 formulas intact

 

[excelNewbie22]

ok, did a little googling and found this:

Function Copy_Only_Values(Rng As Range)

Dim Output() As Variant
ReDim Output(Rng.Rows.Count - 1, Rng.Columns.Count - 1)

For i = 1 To Rng.Rows.Count '
    For j = 1 To Rng.Columns.Count
        Output(i - 1, j - 1) = Rng.Cells(i, j)
    Next j
Next i

Copy_Only_Values = Output

End Function

thanks joe!

 

[Joe4]

ok, did a little googling and found this:

Function Copy_Only_Values(Rng As Range)

Dim Output() As Variant
ReDim Output(Rng.Rows.Count - 1, Rng.Columns.Count - 1)

For i = 1 To Rng.Rows.Count '
    For j = 1 To Rng.Columns.Count
        Output(i - 1, j - 1) = Rng.Cells(i, j)
    Next j
Next i

Copy_Only_Values = Output

End Function

thanks joe!

Have you tried it out?
Does that do what you want it to?
When I tried it out, if I changed the values in my original range, the values in the destination range seemed to change also.

I think I misunderstood your original question, but here is a solution that I came up with where the copied over values will not change when the values in the original range change:
It is a procedure that when you run, will prompt your what range you want to copy from, and where you want to paste it to:

Sub MyCopyValues()
    
    Dim rng1 As Range, rng2 As Range

'   Prompt user what range to copy from
    Set rng1 = Application.InputBox( _
      Title:="Copy From Range", _
      Prompt:="Select the range you want to copy values from", _
      Type:=8)
      
'   Prompt user what range to copy to
    Set rng2 = Application.InputBox( _
      Title:="Copy To Range", _
      Prompt:="Select the FIRST cell of the range you want to copy values to", _
      Type:=8)
      
'   Make sure selection is exactly one cell
    If rng2.CountLarge <> 1 Then
        MsgBox "You have not selected a single cell in Copy To prompt", vbOKOnly, "PLEASE TRY AGAIN!"
    Else
'       Paste values
        rng1.Copy
        rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End If
      
End Sub

 

[excelNewbie22]

thanks again joe, it's actually good,
can it be like what i wanted? to write it as a formula, instead of the need to run it from menu, and enter the range in 'external' window?

 

[excelNewbie22]

example

test
	A	B	C	D	E	F	G	H	I
1	7	2	7	6	joe's-->	6	6	5	6
2	8	4	7	4		1	4	7	1
3	1	1	5	2		3	5	6	7
4	4	3	6	4		8	1	7	2
5	1	2	4	2		3	2	7	5
6
7					googling-->	7	2	7	6
8						8	4	7	4
9						1	1	5	2
10						4	3	6	4
11						1	2	4	2
test

Cell Formulas
Range	Formula
A1:D5	A1	=RANDBETWEEN(1,8)
F7:I11	F7	=Copy_Only_Values(A1:D5)
Dynamic array formulas.

 

[Joe4]

The issue is that when you use "RANDBETWEEN", anytime there is a new entry or recalculation anywhere on your sheet, all those values are re-calculated and can change.
So the method that you found on-line does NOT do a Copy/Paste Special Values. It dynamically changes as the underlying data changes (as shown in your example), where mine will copy the hard-coded values, at that particular point in time, and will NOT change as the underlying values in the original range change.

can it be like what i wanted? to write it as a formula, instead of the need to run it from menu, and enter the range in 'external' window?

I repeat, and cannot stress this enough, Excel formulas (regardless of whether they are native Excel functions or UDFs) can only return values to the cells that the formulas are placed in (keeping in mind that some new functions have the ability to "spill" over into neighboring cells). And formulas, as you can see, are NOT hard-coded values, so their values are not set in stone and can change as the values they reference change. You cannot "hard-code" any number using an Excel formula, regardless of whether it is a UDF or not.

 

[excelNewbie22]

ok joe, thanks for the time and explaining !

 

[Joe4]

You are welcome.