Ufdjrw

How to secure an aircraft at a transient parking space?

'The literal of type int is out of range' con número enteros pequeños (2 dígitos)

Why does a recursive function stop on random numbers?

Reversed Sudoku

Can Mathematica be used to create an Artistic 3D extrusion from a 2D image and wrap a line pattern around it?

Why does liquid water form when we exhale on a mirror?

Was Luke Skywalker the leader of the Rebel forces on Hoth?

Counting all the hearts

How do I express some one as a black person?

How to detect if C code (which needs 'extern C') is compiled in C++

Doesn't allowing a user mode program to access kernel space memory and execute the IN and OUT instructions defeat the purpose of having CPU modes?

Shifting between bemols (flats) and diesis (sharps)in the key signature

Why would one plane in this picture not have gear down yet?

Should I tell my boss the work he did was worthless

Database Backup for data and log files

Rewrite the power sum in terms of convolution

Conservation of Mass and Energy

Can I pump my MTB tire to max (55 psi / 380 kPa) without the tube inside bursting?

Is "conspicuously missing" or "conspicuously" the subject of this sentence?

What are some noteworthy "mic-drop" moments in math?

What's the "normal" opposite of flautando?

Do I really need to have a scientific explanation for my premise?

Does "Until when" sound natural for native speakers?

Examples of a statistic that is not independent of sample's distribution?

ByRef Argumenttype mismatch when passing an array to a sub or function

1

I am working on a little project for work. All I am trying to do is create an array in Sub1 and call a function, pass the array to that function and have it fill the array.

Very simplified, it looks something like this:

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr As String)
 '- do stuff here
End Sub

I have googled so much and just can't figure out what I'm doing wrong. I have also tried:

• call workArray(arr)


• call workArray arr


• workArray(arr)


• workArray arr

I've read that I can only pass an array ByRef, so I am sure that should be alright. The array arr is of type String, the array the function expects is declared as String - that should be alright too. I always get "ByRef argument type mismatch". Is the problem maybe with the type of String? Are String-arrays behaving differently?

Anyone in here kind enough to release me of my pain?

arrays excel vba string byref

share|improve this question

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of How to pass an array to a function in VBA?
  
  – Mr.Burns
  Mar 6 at 15:39
  

  
  
  
  

add a comment | 

1

I am working on a little project for work. All I am trying to do is create an array in Sub1 and call a function, pass the array to that function and have it fill the array.

Very simplified, it looks something like this:

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr As String)
 '- do stuff here
End Sub

I have googled so much and just can't figure out what I'm doing wrong. I have also tried:

• call workArray(arr)


• call workArray arr


• workArray(arr)


• workArray arr

I've read that I can only pass an array ByRef, so I am sure that should be alright. The array arr is of type String, the array the function expects is declared as String - that should be alright too. I always get "ByRef argument type mismatch". Is the problem maybe with the type of String? Are String-arrays behaving differently?

Anyone in here kind enough to release me of my pain?

arrays excel vba string byref

share|improve this question

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of How to pass an array to a function in VBA?
  
  – Mr.Burns
  Mar 6 at 15:39
  

  
  
  
  

add a comment | 

I am working on a little project for work. All I am trying to do is create an array in Sub1 and call a function, pass the array to that function and have it fill the array.

Very simplified, it looks something like this:

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr As String)
 '- do stuff here
End Sub

I have googled so much and just can't figure out what I'm doing wrong. I have also tried:

• call workArray(arr)


• call workArray arr


• workArray(arr)


• workArray arr

I've read that I can only pass an array ByRef, so I am sure that should be alright. The array arr is of type String, the array the function expects is declared as String - that should be alright too. I always get "ByRef argument type mismatch". Is the problem maybe with the type of String? Are String-arrays behaving differently?

Anyone in here kind enough to release me of my pain?

arrays excel vba string byref

share|improve this question

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr As String)
 '- do stuff here
End Sub

share|improve this question

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

share|improve this question

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

edited Mar 7 at 7:54

Pᴇʜ

23.8k62952

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

asked Mar 6 at 15:27

McMuellermilchMcMuellermilch

87

1 Answer
1

active

oldest

votes

0

You are missing () in the function, an array needs () after the variable when passing it somewhere else so VBA knows it as an array

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr() As String)
 '- do stuff here
End Sub

Edit

I look around a little bit cperson has an excellent in depth guide on passing arrays to functions and back again. When in doubt, check cperson. He has a load of useful stuff regarding VBA

share|improve this answer

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hello Mr. Burns, thank you very much for your answer! Now I get error code 13 - type mismatch. AND I edited my post, maybe my mistake is somewhere else?
  
  – McMuellermilch
  Mar 6 at 15:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @McMuellermilch You now have a new issue, I recommend a new question. SO is to help everyone, present or future. My answer is now invalid when looking at your question now. I recommend rolling back your question to what it was originally and if my answer resolved that issue, mark it as accepted and then ask a new question with your new issue
  
  – Mr.Burns
  Mar 6 at 16:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will do, thank you very much!
  
  – McMuellermilch
  Mar 6 at 16:12
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55026636%2fbyref-argumenttype-mismatch-when-passing-an-array-to-a-sub-or-function%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You are missing () in the function, an array needs () after the variable when passing it somewhere else so VBA knows it as an array

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr() As String)
 '- do stuff here
End Sub

Edit

I look around a little bit cperson has an excellent in depth guide on passing arrays to functions and back again. When in doubt, check cperson. He has a load of useful stuff regarding VBA

share|improve this answer

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hello Mr. Burns, thank you very much for your answer! Now I get error code 13 - type mismatch. AND I edited my post, maybe my mistake is somewhere else?
  
  – McMuellermilch
  Mar 6 at 15:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @McMuellermilch You now have a new issue, I recommend a new question. SO is to help everyone, present or future. My answer is now invalid when looking at your question now. I recommend rolling back your question to what it was originally and if my answer resolved that issue, mark it as accepted and then ask a new question with your new issue
  
  – Mr.Burns
  Mar 6 at 16:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will do, thank you very much!
  
  – McMuellermilch
  Mar 6 at 16:12
  

  
  
  
  

add a comment | 

0

You are missing () in the function, an array needs () after the variable when passing it somewhere else so VBA knows it as an array

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr() As String)
 '- do stuff here
End Sub

Edit

I look around a little bit cperson has an excellent in depth guide on passing arrays to functions and back again. When in doubt, check cperson. He has a load of useful stuff regarding VBA

share|improve this answer

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hello Mr. Burns, thank you very much for your answer! Now I get error code 13 - type mismatch. AND I edited my post, maybe my mistake is somewhere else?
  
  – McMuellermilch
  Mar 6 at 15:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @McMuellermilch You now have a new issue, I recommend a new question. SO is to help everyone, present or future. My answer is now invalid when looking at your question now. I recommend rolling back your question to what it was originally and if my answer resolved that issue, mark it as accepted and then ask a new question with your new issue
  
  – Mr.Burns
  Mar 6 at 16:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will do, thank you very much!
  
  – McMuellermilch
  Mar 6 at 16:12
  

  
  
  
  

add a comment | 

You are missing () in the function, an array needs () after the variable when passing it somewhere else so VBA knows it as an array

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr() As String)
 '- do stuff here
End Sub

Edit

I look around a little bit cperson has an excellent in depth guide on passing arrays to functions and back again. When in doubt, check cperson. He has a load of useful stuff regarding VBA

share|improve this answer

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

Private Sub whatever()
 Dim arr(10, 2) As String
 workArray arr
End Sub

Sub workArray(ByRef arr() As String)
 '- do stuff here
End Sub

share|improve this answer

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720

answered Mar 6 at 15:32

Mr.BurnsMr.Burns

5261720