Excel95: Using range from dialog as array in VBA

Excel95: Using range from dialog as array in VBA

Post by Hans D. Jense » Sat, 13 Apr 1996 04:00:00


I am just started using Excel and try to make new macros.
I have a problem using custom dialog boxes and manipulating data.
In a dialog box I have a EditBox with type Reference, and when used I
can enter or select a range from a worksheet. In a Module I want
to access the data in that range as an array, so I need to put
the values of the selection into a VBA array. I have tried

Dim myarray As Variant
Dim temp As Text

With DialogSheet("the Dialog") Do
    If .Show Then
        temp = .EditBoxes("theArray").Text ' Text or something else?
        myarray = [temp]                   ' probably still Text?
or      myarray = Range(temp).Value ' adding ActiveSheet. no good
and a couple of other (horrible) variations. None work. How
do I do this?

Second, I wish to put back the values of myarray in another place
in the worksheet references by a top-left cell. can i do that in one
go, or must I establish the bounds of myarray, select and appropriate
range and the use e.g. Selection.Value = myarray ?

Hans D. Jensen                            | Tel.: +45 4593 1144
Danish Institute of Fundamental Metrology | Dir.: +45 4525 5864
Building 307                              | Fax.: +45 4593 1137
Anker Engelunds Vej 1                     | Internet: