0

I don't get what's false in my code. I searched the error the whole morning! So I hope you can help me.

First, here's the problem code (the names of the variables aren't their real names):

Sheets(sheet).Range(nameOfTheRange).FormulaR1C1 = _
    functionReturningString(functionReturningStrArr( _
      Range(nameOfAnotherRange).Value, AnInputWorkSheet, "colNameInInputSheet"))

So my description on that:

All functions work fine standing alone, but in combination there is always this error (Language: German):

Fehler beim Kompilieren:

Unverträglicher Typ: Datenfeld oder benutzerdefinierter Typ erwartet

functionReturningString is a function with the following parameters(strArr() as Variant) --> it returns a String like a bulletlist.

functionReturningStrArr(nameWhichISearchInSheet as String, dataSheet as Worksheet, dataColumn, as String) --> it returns a Variant() for the bulletListing

I'm not sure if the second method really works so here's the code of it.

Function functionReturningStrArr(ByVal nameWhichISearchInSheet As String, ByVal datasheet As Worksheet, ByVal datacolumn As String) As String()
Dim returnArray() As String
Dim rowindex As Integer
Dim ID As String


Sheets(rawdataOverall).Cells(1, getColNumFromColName("Project")).EntireColumn.Select

'search correct dataset
For Each cell In Selection
    If cell.Value = nameWhichISearchInSheet Then
        rowindex = cell.row
        Exit For
    End If
Next cell

'get ID
ID = Sheets(rawdataOverall).Cells(rowindex, getColNumFromColName("ID")).Value


'search data from file with this ID
datasheet.Cells(1, getColNumFromColName(datacolumn)).EntireColumn.Select
Selection.UsedRange.Select

For Each cell In Selection
    rowindex = cell.row
    'check if row contains to this project
    If Cells(rowindex, getColNumFromColName("ID")) = ID Then
        ReDim Preserve returnArray(UBound(returnArray) + 1)
        returnArray(UBound(returnArray)) = cell.Value
    End If
Next cell

functionReturningStrArr = returnArray()

If you are asking yourselves what is getColNumFromColName, it is a method which works really fine, I used it in other projects too.

Improve this question
4
  • Funny choice of function name: functionReturningStrArr returns a Variant... Why not declare it As String() to actually make it return a String array, as the name suggests? Commented Nov 20, 2013 at 10:59
  • @Jean-FrançoisCorbett this variant return is a string array! Commented Nov 20, 2013 at 11:01
  • @Jean-FrançoisCorbett ok I'll try. Hmm.. makes no difference. Commented Nov 20, 2013 at 11:08
  • I wasn't expecting it to make a difference in the execution; just trying to impart you some good habits that will reduce the amount of confusion in your code. Commented Nov 20, 2013 at 11:14

2 Answers 2

Reset to default
3

You really have to start declaring everything explicitly using Dim -- and force yourself to do this by writing Option Explicit at the top of your module. That way you will identify errors much more quickly.

Here

'get ID
ID = Sheets(rawdataOverall).Cells(rowindex, getcolnumformcolname("ID")).Value

you call a function called getcolnumformcolname; presumably form is a typo and you meant From as in getColNumFromColName. Had you had Option Explicit, you would have detected that error immediately.

The following three variables/arrays are not declared: rawdataOverall, cell, getDataFromThisProject. You should declare them and assign them a type explicitly.

Try fixing those things and see where that brings you.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Okay i will do this, thanks. rawDataOverall is a global variable.
why? I use it in almost every sub or function
sorry, did not make it better :S
Just type "global variable bad" in your favourite search engine and read to your heart's content. Example hit. Wikipedia on global variables.
What line of code is highlighted when the error message appears?
|
0

Is seems a small portion of the function code snippet is wrong. on the very last line, you should assign the value of returnArray() to your function name like so:

functionReturningStrArr = returnArray()

Otherwise, you would need to extract the array from a variable named "getDataFromActualProject", as is shown in the example.

EDIT:

Alter your function "functionReturningStrArr As Variant" to return "As String()" instead of Variant. It seems you cant cast a Variant to a string array as you would expect.

EDIT:

I created a function to test this. This compile error shows up when you try to cast Variant as Array of string. This also includes a fix, your function that returns Variant MUST return array of string instead.

Sub RunTest()

    Debug.Print getStringFromArray(getArray())
    Debug.Print getStringFromArray(getVariant()) ' compile error! you cannot cast variant to array of string

End Sub

Function getArray() As String()

    Dim returnArray(2) As String

    returnArray(0) = "A"
    returnArray(1) = "B"
    returnArray(2) = "C"

    getArray = returnArray()

End Function

Function getVariant() As Variant()

    Dim returnArray(2) As String

    returnArray(0) = "A"
    returnArray(1) = "B"
    returnArray(2) = "C"

    getArray = returnArray() ' Not a compile error, you can cast string array to variant

End Function

Function getStringFromArray(inputArray() As String) As String

    Dim returnString As String
    For i = LBound(inputArray()) To UBound(inputArray())
        If returnString = "" Then
            returnString = inputArray(i)
        Else
            returnString = returnString & "," & inputArray(i)
        End If
    Next i
    getStringFromArray = returnString

End Function
Improve this answer

5 Comments

sorry in my code ther it is right, but here I renamed the functions and so it was a mistake. But thanks for the Input. Look at my edit.
Still debugging it... I found that the old function name "getDataFromThisProject" occurs in other places too.
I figured how to make this compile error, the answer in my second edit exposes the cause of the error. My third edit demonstrates this error. Fix your function to return "As String()", solved.
IT FINALLY WORKED!! THANK YOU!
The compile error results from trying to cast Variant to String(). I provided an example to demonstrate this. While his naming conventions and method usages are questionable, they are not errors specifically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.