mshbhwn98
03-05-2015, 03:48 AM
Hi,
I'm pretty new to vba and I'm teaching myself about functions. I have written a sub that takes the contents of a column of cells and puts them into a comma delimited string like so
' creates a comma delimited string from a table of comma delimited strings
Private Sub CreateAnalysisString()
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range("rngStartCur").Offset(i, 0), .Range("rngFinishCur")) Is Nothing)
' If the cell is blank do nothing
If .Range("rngStartCur").Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first part of the string does not need a ,
AnalysisString = .Range("rngStartCur").Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range("rngStartCur").Offset(i, 0).Value
End If
End If
i = i + 1
wksModeller.Range("C1").Value = AnalysisString
Loop
End With
End Sub
I now want to try putting it into a function to gain a better understanding of how vba works. I did this with the following code
' creates a comma delimited string from a table of comma delimited strings
Private Function CreateAnalysisString(rngStart As Range, rngFinish As Range) As String
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range(rngStart).Offset(i, 0), .Range(rngFinish)) Is Nothing)
' If the cell is blank do nothing
If .Range(rngStart).Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first par of the string does not need a ,
AnalysisString = .Range(rngStart).Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range(rngStart).Offset(i, 0).Value
End If
End If
i = i + 1
Loop
End With
CreateAnalysisString = AnalysisString
End Function
Sub test()
Dim z As String
z = CreateAnalysisString(.Range("rngStartCur"), .Range("rngFinishCur"))
MsgBox z
End Sub
I am getting a Compile error: Ambiguous name detected:CreateAnalysisString - but I dont know what's wrong with it. Could someone guide me in teh right direction please?
Kind regards
I'm pretty new to vba and I'm teaching myself about functions. I have written a sub that takes the contents of a column of cells and puts them into a comma delimited string like so
' creates a comma delimited string from a table of comma delimited strings
Private Sub CreateAnalysisString()
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range("rngStartCur").Offset(i, 0), .Range("rngFinishCur")) Is Nothing)
' If the cell is blank do nothing
If .Range("rngStartCur").Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first part of the string does not need a ,
AnalysisString = .Range("rngStartCur").Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range("rngStartCur").Offset(i, 0).Value
End If
End If
i = i + 1
wksModeller.Range("C1").Value = AnalysisString
Loop
End With
End Sub
I now want to try putting it into a function to gain a better understanding of how vba works. I did this with the following code
' creates a comma delimited string from a table of comma delimited strings
Private Function CreateAnalysisString(rngStart As Range, rngFinish As Range) As String
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range(rngStart).Offset(i, 0), .Range(rngFinish)) Is Nothing)
' If the cell is blank do nothing
If .Range(rngStart).Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first par of the string does not need a ,
AnalysisString = .Range(rngStart).Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range(rngStart).Offset(i, 0).Value
End If
End If
i = i + 1
Loop
End With
CreateAnalysisString = AnalysisString
End Function
Sub test()
Dim z As String
z = CreateAnalysisString(.Range("rngStartCur"), .Range("rngFinishCur"))
MsgBox z
End Sub
I am getting a Compile error: Ambiguous name detected:CreateAnalysisString - but I dont know what's wrong with it. Could someone guide me in teh right direction please?
Kind regards