CODEWITHSUNDEEP
excelvba
user2536125
user2536125

Reputation: 245

Save a string into non-initialiazed Variant variable in VBA

I am using a VBA Macro from another worksheet. I can only pass a string as input (or I want to stick to this structure as any change will results in changing alot of code). However, the available macro is using a Variant:

Private Sub create_tempsheet(DCMfile As String)
    
    Dim i As Long
    Dim dcm_files As Variant
    'oeffnen des DCMs
    If DCMfile = "" Then
        dcm_files = Application.GetOpenFilename("dcm-Files(.dcm),*dcm", Title:="Select dcm files", MultiSelect:=True)
    Else
        dcm_files(1) = DCMfile
    End If
    'Rest_OF_CODE

the initial variable is dcm_files and I introduced the variable DCMfile The above code results in a type missmatch error at line dcm_files(1) = DCMfile . The conversion function CVar didn't help neither.

Any idea how can I save DCMfile into dcm_files(1) without changing the two variables types?

Upvotes: 1

Views: 158

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

GetOpenFilename

  • GetOpenFilename, ReDim

  • When you do Dim dcm_files As Variant, dcm_files is not an array, it's a variable of type variant that can hold any variable type including an array.

  • When you use GetOpenFilename with MultiSelect set to True, the result will be False (a boolean) if you press Cancel, or a 1D one-based array if you press OK (pick one or more files) which will be passed to the variant.

  • You forgot to ReDim the variant in the Else statement:

    ReDim dcm_Files(1 to 1)

    Only now it's (a variant holding) a 1D one-based one-element array.

Private Sub create_tempsheet(DCMfile As String)
    
    Dim i As Long
    Dim dcm_files As Variant
    'oeffnen des DCMs
    If DCMfile = "" Then
        dcm_files = Application.GetOpenFilename("dcm-Files(.dcm),*dcm", _
            Title:="Select dcm files", MultiSelect:=True)
        If dcm_files = False Then
            MsgBox "You canceled."
            Exit Sub ' or whatever
        End If
    Else
        ReDim dcm_files(1 To 1)
        dcm_files(1) = DCMfile
    End If
     
    ' Print the file names in the VBE Immediate window (Ctrl+G)
    'Debug.Print Join(dcm_files, vbLf)
     
    'Rest_OF_CODE

End Sub

Upvotes: 2

Raymond Wu
Raymond Wu

Reputation: 3387

It's giving you error because dcm_files is not an array, you just need to change dcm_files(1) = DCMfile to dcm_files = DCMfile.

Dim i As Long
Dim dcm_files As Variant
'oeffnen des DCMs
If DCMfile = "" Then
    dcm_files = Application.GetOpenFilename("dcm-Files(.dcm),*dcm", Title:="Select dcm files", MultiSelect:=True)
Else
    dcm_files = DCMfile
End If

Upvotes: 0

Related Questions