max lenght for a string

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
i want to put 136 symbols in 1 string, when i try to run the macro's it always stops at the point that string is filled, telling me:
Run-Time error '13'
Type Mismatch

what variable should i use for that amount of symbols?

Thanks in Advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Can you post the code you already have?

This is from Excel Help:
String Data Type


There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31) characters.


A fixed-length string can contain 1 to approximately 64K (2^16) characters
 

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
this is the code i used, the line that is cousing the trouble is "Text2= ...."

Private Sub CommandButton2_Click()
Dim qstring As String, q2string As String, Text2 As String


Sheets("TOP Teams").Select
Date001:
Range("IU655").Select
frmCalendar.Show ' i found this calendar macro somewhere else, and found it rather usefull
Qwerty = ActiveCell.Value
qstring = Qwerty
Range("IU654").Select
ActiveCell.Formula = "=Today()"
Qwerty2 = ActiveCell.Value
q2string = Qwerty2
If Qwerty - Qwerty2 >= 1 Then
QZX = MsgBox("De gekozen Datum " + qstring + " is in de toekomst. Het is vandaag " + q2string + ", kies en andere datum", vbOKOnly, "DatumSelectie")
GoTo Date001:
End If

Date002:
Range("IV655").Select
frmCalendar.Show ' i found this calendar macro somewhere else, and found it rather usefull
Qwerty3 = ActiveCell.Value
q3string = Qwerty3
Range("IV654").Select
ActiveCell.Formula = "=Today()"
Qwerty4 = ActiveCell.Value
q4string = Qwerty4
If Qwerty3 - Qwerty4 >= 1 Then
QZX = MsgBox("De gekozen Datum " + q3string + " is in de toekomst. Het is vandaag " + q4string + ", kies en andere datum", vbOKOnly, "DatumSelectie")
GoTo Date002:
End If
If Qwerty3 - Qwerty < 1 Then
QZX2 = MsgBox("De gekozen Datum " + q3string + " is eerder dan de begindatum " + qstring + ", kies en andere datum", vbOKOnly, "DatumSelectie")
GoTo Date002:
Else
Range("IT654").Select
ActiveCell.FormulaR1C1 = "=YEAR(R[1]C[1])"
Jaar = ActiveCell.Value
Range("IT655").Select
ActiveCell.FormulaR1C1 = "=MONTH(R[0]C[1])"
Maand = ActiveCell.Value
Text2 = "Haal nu met MRS de Moves binnen, en sla deze op als X:\Departments_M_Z\TPD\METL\Teams\data\" + Jaar + "\moves\" + Maand + "\Selectie.xls"
Title2 = "Selectie Bevestiging"
MsgBox prompt:=Text2, Buttons:=vbOKOnly, Title:=Title2

End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
I really can't see any problem with the code. Perhaps if you change the '+' to '&' in the line "Text2=".

BTW Text2 isn't a control on a form is it.

BTWA what is the code actually supposed to do and why are you using worksheet functions to return the year etc?
 

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
thanks for the tip about the "+" and the "&" indeed works better

The function of this macro is to let the user chose a starting date and a end date (the macro looks wheter start or end are in the future or not and whetere start is before end)
After that it tells the user what program to start "MRS" and to make a dump file of a function in that program.

The reason i use worksheet functions is because i'm not as experienced in VBA as many others users here and dont know a better way to do that.

i started a project a month ago with almost no VBA knowledge, my boss told me that i had to make a excel solution for monthly reports (concerning certain machines in the plant)

if i knew a shorter way to get the data i need, i would use that, until now i only exeptional use different approaches (code i got from previous topics).

Anyways thanks for your help
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Well to the current year in VBA you could use this:

Year(Now())

and similarly for the month

Month(Now())

If you wanted the year from a cell containg a date you could use this:

Year(Range("IT654"))

and again for the month

Month(Range("IT655"))
 

Forum statistics

Threads
1,148,048
Messages
5,744,501
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top