How to pass a range into a string?

How do I read from an excel range and pass the results into a function?

I want to collect email addresses from a range and pass them to an email function as a string.
LVL 2
SiHodgy007Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Then you may have this Function on a standard module...
Function getEmailString(ByVal ws As Worksheet, ByVal Rng As Range) As String
Dim Cell As Range
Dim Str As String
For Each Cell In Rng
    If Cell <> "" Then
        If Str = "" Then
            Str = Cell.Value
        Else
            Str = Str & ";" & Cell.Value
        End If
    End If
Next Cell
getEmailString = Str
End Function

Open in new window


Then use this function in another function or macro like this...

Sub Test1()
Dim ws As Worksheet
Dim Rng As Range
Dim EmailStr As String
Set ws = ActiveSheet    'Change it as per your requirement
Set Rng = ws.Range("B5:C9")
MsgBox getEmailString(Rng)
End Sub

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
Hi,

Either use the Cstr() function, or concatenate the range's value with an empty string.
Dim wb As Excel.workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("B5")

email Cstr(rng.Value)
email rng.Value & vbNullString

Open in new window

Notes: Ensure that the range variable hold only one cell.
0
 
SiHodgy007Author Commented:
Will this work with combining multiple calls together?

Set rng = ws.Range("B5:B9")

email = Cstr(rng.Value)
debug.print email
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Fabrice LambertFabrice LambertCommented:
Nope, it won't.
Because with multiple cells, the range value return an array.
0
 
SiHodgy007Author Commented:
That's my question how do I get a range of cells into a value.
0
 
Fabrice LambertFabrice LambertCommented:
With multiple cells, you'll need to loop over each cells, and build a string:
Dim value As String
Dim cell As Excel.range
For Each cell in Rng.Cells
    value = value & "cell.Value
Next

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Dim Arr
Dim EmailStr As String
Arr = Range("B5:B9").Value
EmailStr = Join(Application.Transpose(Arr), ";")

Open in new window

The above code will return EmailStr as semi colon separated emails. If you don't want semi colon as a delimiter, please change it with the desired one.
0
 
SiHodgy007Author Commented:
Thanks

Subdoh, How can this be done with:

 Arr = Range("B5:C9").Value
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Array Arr will read the values from the range B5:B9.
You can read the values from a range into an Array.

Though in your code, don't forget to qualify the range with worksheet reference.

Arr = ws.Range("B5:C9").Value

Open in new window

0
 
SiHodgy007Author Commented:
I get invalid procedure or argument. the previous worked just not with the extra dimension.
0
 
Fabrice LambertFabrice LambertCommented:
Though in your code, don't forget to qualify the range with worksheet reference.
Unfortunatly, recurrent issue with Excel.

Never ever use objects such as ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart, Selection, Cells, Range, Sheets because they are subject to change upon any user interraction, thus are unreliable.
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Open a blank workbook and insert a new module and place the following code in there...

Sub Test()
Dim ws As Worksheet
Dim Arr
Dim EmailStr As String
Set ws = ActiveSheet
Arr = ws.Range("B5:B9").Value
EmailStr = Join(Application.Transpose(Arr), ";")
MsgBox EmailStr
End Sub

Open in new window

Now on the active sheet, input emails in the range B5:B9 and run the code. Does that work for you?
0
 
SiHodgy007Author Commented:
That works but this doesn't

1:Sub Test()
2:Dim ws As Worksheet
3:Dim Arr
4:Dim EmailStr As String
5:Set ws = ActiveSheet
6:Arr = ws.Range("B5:C9").Value
7:EmailStr = Join(Application.Transpose(Arr), ";")
8:MsgBox EmailStr
9:End Sub
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
This doesn't work actually doesn't describe an issue with the code you are having.
Do you get an error? If yes, what's the error number and description?
0
 
SiHodgy007Author Commented:
I get invalid procedure or argument. the previous worked just not with the extra dimension
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You must be doing something wrong there, maybe placed the code at wrong place?
Anyways, please carry on with what worked for you. Is your issue resolved?
0
 
SiHodgy007Author Commented:
Are you saying B5:C9 works for you?
0
 
Fabrice LambertFabrice LambertCommented:
The join function only work with 1D array.

Before it get too confusing, let me explain what the Value property of the range object return:
- If the range is only one cell, it return a string (or number).
- If the range is multiple cells, but all cells are on the same row, it return an array with one dimention (1D array).
- If the range is muliple cells across multiple rows, it return a two dimentional array (2D array).

Conclusion:
Better loop over all cells, at least it is generic and work in all cases.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Are you saying B5:C9 works for you?
Ah... I didn't notice that.
BTW,why are you referring that range? Which column contains the email addresses?

Instead, you must upload a sample workbook with some dummy data along with your expected output mocked up manually, that helps to visualize the requirement more clearly.
0
 
SiHodgy007Author Commented:
Yes there are just a block of email addresses not just in the same line.
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
You can also prompt for a range
Dim range As Range
Set range = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

Open in new window

0
 
SiHodgy007Author Commented:
Thanks Subodh

You missed passing the range see below.

Sub Test1()
Dim ws As Worksheet
Dim Rng As Range
Dim EmailStr As String
Set ws = ActiveSheet    'Change it as per your requirement
Set Rng = ws.Range("B5:C9")
MsgBox getEmailString(ws, Rng)
End Sub
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
My apologies. I forgot to remove the sheet parameter from the function. It should be like this...

Function getEmailString(ByVal Rng As Range) As String
Dim Cell As Range
Dim Str As String
For Each Cell In Rng
    If Cell <> "" Then
        If Str = "" Then
            Str = Cell.Value
        Else
            Str = Str & ";" & Cell.Value
        End If
    End If
Next Cell
getEmailString = Str
End Function

Open in new window

And then can be used as...

Sub Test1()
Dim ws As Worksheet
Dim Rng As Range
Dim EmailStr As String
Set ws = ActiveSheet    'Change it as per your requirement
Set Rng = ws.Range("B5:C9")
MsgBox getEmailString(Rng)
End Sub

Open in new window

0
 
SiHodgy007Author Commented:
Thanks for your assistance
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sihodgy007!

If that takes care of your original question, please take a minute to mark your question as Solved by accepting the answers.

Please don't forget to assign some points to Fabrice Lambert as well for the assistance he has provided to you.
0
 
Fabrice LambertFabrice LambertCommented:
Option Explicit

Public Sub export()
    Const workbookName As String = "FileWithData.xlsm"
    Const worksheetName As String = "Sheet1"
    Const rangeAddress As String = "C10:AA5000"
    Const csvPath As String = "C:\FileStorage\ImportedData.csv"
    
    Dim wb As Excel.Workbook
    Set wb = Workbooks(workbookName)    '// or open the workbook
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets(worksheetName)
    
    Dim rng As Excel.Range
    Set rng = ws.Range(rangeAddress)
    
    exportRangeToCsv rng, csvPath
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Public Sub exportRangeToCsv(ByRef rng As Excel.Range, ByVal filePath As String)
    Const ForWriting As Long = 2
    Dim fso As Object   '// Scrpting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim stream As Object    '// Scripting.TextStream
    Set stream = fso.OpenTextFile(filePath, ForWriting, True)
    
    Dim row As Excel.Range
    For Each row In rng.Rows
        Dim data As String
        data = vbNullString
        
        Dim cell As Excel.Range
        For Each cell In row.Cells
            If (data <> vbNullString) Then
                data = data & "," & cell.Value
            Else
                data = cell.Value
            End If
        Next
        stream.WriteLine data
    Next
    stream.Close
    Set stream = Nothing
    Set fso = Nothing
End Sub

Open in new window

This is more or less the solution I suggested in my 3rd answer (loop over cells, and build a string) ...
0
 
SiHodgy007Author Commented:
One last request, is there away to make sure duplicate strings aren’t added

Have tried  else if not instr(str, cell.value) then

Str = str & “;” & cell.value
0
 
Fabrice LambertFabrice LambertCommented:
Ops, can't edit anymore . well ignore my previous comment.
One last request, is there away to make sure duplicate strings aren’t added

Have tried  else if not instr(str, cell.value) then

Str = str & “;” & cell.value
You should make a new thread, as this issue is completly different from the original.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To avoid adding duplicate emails in the string, replace your existing Function with this.
Function getEmailString(ByVal Rng As Range) As String
Dim Cell As Range
Dim Str As String
For Each Cell In Rng
    If Cell <> "" Then
        If Str = "" Then
            Str = Cell.Value
        Else
            If InStr(Str, Cell.Value) = 0 Then Str = Str & ";" & Cell.Value
        End If
    End If
Next Cell
getEmailString = Str
End Function

Open in new window

0
 
SiHodgy007Author Commented:
Thanks all
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.