ByRef argument type mismatch - problem wint function

[kmeld]

Hi,
I have a problem with function and sub using this function. Everything work if I use value of argument (integer in this case) and gives and ByRef argument type mismatch if I try to use integer variable.

situation is as follows:
I have a public function in ThisWorkbook:

Please Login or Register  to view this content.

This function works.

Than in user form I have sub that uses this function. This sub is than used by various controlls.

Please Login or Register  to view this content.

as you can see in the line :

Please Login or Register  to view this content.

I had put values "I" and 500 (now they are static to avoid problems).

Than in one of control buttons I call SprawdzNumer sub. and that is where I got error.

Please Login or Register  to view this content.

numer is integer variable declared in this userform. Its value is 500 (for tests).

If I run the same code only not using numer variable but value of 500 I got no errors.

What is wrong?

Cris

[TMS]

It looks as though you have a function AND a subroutine with the same name. I would guess that's cause for confusion.

Regards, TMS

[Izandol]

Where and how is numer declared - you have not shown? There must be:

Please Login or Register  to view this content.

at the top of userform module and before all routines.

[Norie]

What is numer?

[kmeld]

Hi,

answering to three suggestions above:
1) Name of function and subroutine are in fact the same but it dose not cose any confusion because everything works if I only do not use "numer" variable but just put any integer value as argument.

2) variable "numer" is declared, and was there from beginning , as you show in the top of userform module.

3) numer is name of variable of type integer. (or if you referring to spelling it is spelled in polish, but this is of no importance to VBA). This variable has a value assigned before function is called.

Cris

[Norie]

Cris

Can we see the whole code and/or a sample workbook?

By the way, you really should avoid using the same name for 2 different things.

Using the same name might appear to work but the chances of it causing problems are pretty high.

Oh, and you shouldn't really have public functions/subs in the ThisWorkbook module, but then in a standard module.

[Izandol]

Nowhere in this code do you show:

Please Login or Register  to view this content.

You must have this at top of userform module or you will have ByRef error because you pass Variant and not Integer - even if Variant contains an Integer that is not OK.

[kmeld]

Hi,

I have quit a lot of code in this workbook (doing other stuff than this function). Therefore I will post parts related to this only.


In the meantime I had changed mane of function to SprawdzNum as you suggested. (althught this wasn't cause of problem).

Workbook part: (I will move it later to module). If I do that would I need to change ThisWorkbook.functionname to Module1.functionname when I call this function?

Please Login or Register  to view this content.

Userform part:

Please Login or Register  to view this content.

Cris.

[Norie]

Cris

It would be far easier, for us, if you uploaded a sample workbook.

[Izandol]

Agreed - I have no error with this last posted code.

[kmeld]

Hi,
I have attached whole workbook.

It is still undergoing project so there probably are some parts that may cause problems on your computer.

I had deleted other forms but most of functions are still there.

Also it create text files (I deleted this parts)

I did not translate all to english.

Generally I had to remove big parts and something my not work.

Strange thing just happened. When I was testing file I am attaching this function started to work for some reason.
I will have to investigate this.

Cris.

[Izandol]

If function is working what will we test?

[kmeld]

Hi,

minute after it did not work again

Generally this function is to search for number that user enters to find if it is in base.
If not it returns 0
if found 1
and if input data is wrong iit returns -1

It will be used in various forms so I want it public not to define it again and again.

Cris.

[Norie]

Cris

How would you have replicated the error when it was still happening?

[Norie]

Cris

I can't replicate the error this thread is about but I am finding others.

The first one I encountered, type mismatch, was here.

Please Login or Register  to view this content.

It's caused by osoby being empty, and when I go back through the code I find that osoby is declared in the ThisWorkbook module and initialized in the workbook's open event.

That seems a bit strange to me, why isn't osoby declared and initialized elsewhere?

For example the declaration could be in a standard module, if it's in the ThisWorkbook module it isn't actually public even though it's been declared as so.

If the declaration was in a standard module you could use this code to populate the osoby combobox in the form's Initialize event.

Please Login or Register  to view this content.

[kmeld]

Hi,

I have no problem with Osoby. It works fine on my computer.

Generally I will have different forms for various task performed in database. This task can only be done by selected employees so I need a list of them and corresponding passwords.

I do not want to create additional sheet with names and passwords so I need a public variable array of strings.
I had put it in Workbook initialize to have it prepared before anything else will take place.

I felt it was not perfect solution no one I asked had any idea of what I want and they all advised me "put this in worksheet". And since I managed to get this working in the way you see I give up in looking for another solution.

What would be your suggestion on how to code this?

Ok lets say I will put this in standard module.

How do I do that? When module is initialized?


Cris

[Norie]

Cris

The variables you've declared as Public in the ThisWorkbook module are not public.

To make them public put them in a standard module (Insert>Module).

You can refer to them in any module in the workbook and you'll only need to use the variable name, eg osoby, not the module and the variable name, eg ThisWorkbook.osoby.

[kmeld]

Hi

OK I put declaration in module but how do I initialize them?

Please Login or Register  to view this content.

Cris

[Norie]

All you need to do is move the declarations, nothing else.

[kmeld]

OK I moved this but now noting compiles.


I assume I need to move all public functions and subs definitions also to this module and remove ThisWorkbook. from wherever they or variables are used.

Am I correct?

Cris.

[Norie]

Oops, forgot.

You'll also need to remove ThisWorkbook. (that is a dot at the end) from throughout the code, you can do that with find and replace (CTRL+H).

[kmeld]

Hi,
I am doing that this very moment. I However can not use find and replace automatically because in some lines I have ThisWorkbook.Patch.

But I am getting forward.

Cris.

[Norie]

Cris

What is Patch?

[Norie]

Forgot attachment.

[kmeld]

Hi,

Patch stands for ThisWorkbook.Path with typing mistake

I got threw this changes and it seems that everything is working after doing this.

This however bring me to another question concerning global variables.

It is common information that local and global variables can have same names. But I didn't find information on how to refer to global and local variables in any tutorial I had seen.

How do I specify whether I want global or local?

Cris.

[Izandol]

I do not think it is good idea - however:

Please Login or Register  to view this content.

[kmeld]

Hi,

OK, after moving variables and function definitions to module1 I am still getting ByRef argument error in function SprawdzNum.

It happens when this function is called by subroutine in form.
This Sub is to cheque if number entered by user is already in database. So it takes input from textbox and combo prepares data for SprawdzNum function calls this function and dose different updates upon result of function.

Error doesn't unfortunately occur every time this sub is done so I can not give you precise instructions on how to reproduce this error.

Would it be some solution to use ByVal prefix for arguments in function definition?

Cris

[Izandol]

It should be simple when error does occur to see what are values passed to the function.

[kmeld]

I do not quite understand.

What should be simple or what should I do?

Cris

[Izandol]

You may use debug.print, or Locals window or Watch to see what values are passed when code breaks. Are you familiar with debugging code?

[kmeld]

Hi,
no I am not familiar with debugging code.

I will do some searching but it would be much easier if you could point me in the right direction.

Cris

[Izandol]

I recommend reading this page: http://www.fmsinc.com/tpapers/vbacode/debug.asp
It is written by Access developers but the VB Editor is the same in all Office applications.

[Norie]

Cris

Can you upload the workbook with all the changes you've made?

[kmeld]

Hi,

I have enclosed whole project, so you have exactly the same as me. This is mxml and directory structure. It should work after placing in any standard directory. I do not know if it will work ok if put in to some system directory like Mydocuments or something.

1) What is it:
This is a database intended for equipment monitoring in a company (if you wander why in excel - I know excel, I have it, other have it too + ability to do that is fun). This DB is to be operated by userforms only. It still is in development stage so userforms are still to be initialized from VBA developer.

2) How it works:
This DB uses log file for monitoring it's activity (rejest.log) placed in the same directory as .mxls. In subdirectories logfiles and pictures for indiwidual records are stored.

Record is added to DB on "Dodanie_Nowego" Form. To add run this form.
- select user (Osoba Dodająca) and type password (same as username)
- select group* (Grupa) and check "AUTO" for auto number* assignment. DB is organized in groups corresponding with equipment purposes.
- enter name* (nazwa), serial number (Numer serynjy), quantity* (ilość), and mass*( masa)
- enter description (opis) and remarks (uwagi)
- add picture if you desire (dodaj zdjęcie) low left.
* - required fields
- click "wprowadź" (apply). Than code verifies input and if fields are left blank you will get message with question if you want to enter value. If you do not click "NO". If any required field is left empty you will get message and after OK you will be brought back to form.
After validating code creates logfile, copies picture, fills cells in worksheet, updates DB log, saves and closes form.

3) the problem:
Function being subject of this thread is SprawdzNum(g as string, num ad integer). It is defined in module1.
It's purpose is to check if record corresponding with g (group) and num (number) is already present in DB. This function returns -1 if g dose not match to any of groups (worksheet's names), 1 if record found and 0 if record not found. It also sets CurrentEdit global variable if record was found, if not clears this variable.
This function is called by Sub defined in form. This Sub is named SprawdzNumer(). its purpose is to take userinput, prepare input for SprawdzNum function, call this function and do appropriate updates in form's controls.
Currently on three forms SprawdzNumer is defined (only search form dose not use it). On various forms its Sub dose different updates but they all call for SprawdzNum function.
Sometimes there is a ByRef argument error thrown during use. This error is related with local form's variable "numer" as int. passed to SptawdzNum function.



If you have any doubts on what I wanted to achieve by some parts of code let me know. This file is not entirely English, so you may have some difficulty. If you need some explanation please also let me know.

Cris.

[Izandol]

In two forms the numer variable is declared as Variant.

Please Login or Register  to view this content.

in form Szukaj and

Please Login or Register  to view this content.

in form Czynnosc_serwisowa. You must specify type for each variable:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[kmeld]

Ok fixed that, and also had specified all other variable's types.

thanks.

[Norie]

Izandol

Where those declarations in the code of the first workbook the OP uploaded?

[Izandol]

I do not know - I have only looked in the latest workbook. Cris stated that function was working in uploaded file and I presumed it to be a reduced file from the real workbook.

[kmeld]

In file I had posted earlier declarations had been different.

I was not aware that each variable has to be specified explicitly for its type. Once (some tie ago) I was taught that they will have the same type assigned.

This moment everything seems to work fine.

Cris