راهکار برای مشکل نشانی‌های فارسی‌دار در برنامه‌نویسی اکسل (VBA)

در برنامه‌نویسی VBA اکسل، اگر سرستون‌های جدول فارسی باشد، مشکل پیش می‌آید. با این راهکار می‌توان برای چنین جدول‌هایی نشانی نسبی ساخت.

در این چندین سالی که با برنامه‌نویسی اکسل (VBA) آشنا شده‌ام، با آن برای خودم و برای جاهایی که کار کرده‌ام ابزارهای مفیدی ساخته‌ام. وقتی کار گره می‌خورد و آدم عرق‌ریزان تلاش می‌کند هرجور شده آن را پیش ببرد، نوآوری گل می‌کند. در اینجا می‌خواهم نتیجهٔ یکی از چاره‌جویی‌هایم را با علاقه‌مندان در میان بگذارم.

مشکل فارسی‌نویسی در VBA

خبره‌های برنامه‌نویسی تأیید می‌کنند با اینکه سال‌هاست زبان فارسی در کامپیوتر استاندارد شده است و می‌شود در نرم‌افزارها و زبان‌های برنامه‌نویسی با آن کار کرد، هنوز جاهایی هست که گیروگور دارد. یکی از آن جاها زبان VBA است. مشکل این است که اگر تنظیمات زبان ویندوز مناسب کار با VBA نباشد، اصلا نمی‌شود در محیط کدنویسی آن فارسی نوشت. این هم اگر نباشد، مشکل دارد. اگر مستقیم در آن فارسی بنویسید، حرف ی فارسی را نمی‌نویسد؛ عربی می‌نویسد. اگر هم از بیرون متن فارسی را کپی کنید و در کد بچسبانید، ممکن است اجق‌وجق نشان بدهد. این‌ها همه در کار برنامه‌نویسی اکسل اشکال پیش می‌آورد.

مشکل کدنویسی برای آبجک جدول‌هایی که سرستون فارسی دارند

چنان‌که می‌دانید، اکسل آبجکت جدول (Table) دارد. در VBA می‌شود نشانی رنج (Range) را به‌جای روش مطلق، یعنی با ترکیب نام ستون‌ها و ردیف‌های شیت مانند A12، به‌شکل نسبی یعنی با نام جدول درست کرد.

برای نمونه آبجکت جدولی را در نظر بگیرید که نامش Table1 است. اگر فرض کنیم نشانی مطلق سطح آن در روی شیت، از B2 تا D10 باشد، نشانی مطلقِ خانهٔ اول بخش Data جدول، یعنی بخش پایین سرستون‌ها، این‌شکلی می‌شود:

Range(&quotB3&quot)

نشانی همین خانه را اگر به‌شکلی نسبی با آبجکت جدول Table1 درست کنیم، این‌شکلی می‌شود:

Range(&quotTable1&quot).Cells(1, 1)

از فایده‌های استفاده از نشانیِ نسبی جدولی این است که اگر جدول روی شیت جابه‌جا بشود یا حتی برود در شیت دیگر، نشانی عوض نمی‌شود. اگر جابه‌جا شد، مجبور نیستیم برویم درون کد همه را درست کنیم.

در ساختن نشانی نسبیِ جدولی، اگر بخواهیم به یکی از ستون‌ها یا بخشی از ستون آدرس بدهیم، می‌باید اسم سرستون را در ترکیب نشانی بیاوریم. برای نمونه نشانی خانهٔ اولِ ستونی که نام سرستونش «ردیف» است، می‌شود این:

Range(&quotTable1[ردیف]&quot).Cells(1, 1)

هیچ راهی ندارد! ساختن نشانیِ نسبی جدولی بدون مقدار نام سرستون ممکن نمی‌شود. اگر نتوانیم در کد با همان کاراکترهایی بنویسیم که روی شیت در سرستون نوشته‌ایم، برنامه خطا می‌دهد. گفتم که در محیط کد VBA نمی‌شود ی فارسی نوشت؛ ي عربی می‌نویسد. پس اگر ی فارسی در نام سرستون جدول داشته باشیم، با ي عربی منطبق نمی‌شود. اگر هم از روی شیت کپی کنیم و در کد بچسبانیم، مانند زیر اجق‌وجق می‌شود و برنامه خطا می‌دهد.

Range(&quotTable1[ط±ط¯ظٹظپ]&quot).Cells(2, 1)

راهکار: «همان که در فلان‌جا نوشته است»!

وقتی برای این مشکل چاره‌جویی می‌کردم، دستم در فایل اکسلی بود که دوازده شیت برای هر ماه از سال داشت. این شیت‌ها از نظر ساختار همانند بودند و رفتار یکسان داشتند. برنامه به‌شکل خودکار کار می‌کرد و کلید اجرای فرمان‌های برنامه در ایونت شیت‌ها بود. کد ایونت‌ها هم یکی بود؛ فقط اسم آبجکت جدولِ داخل هر شیت فرق داشت.

من برای چنین فایل‌هایی یک شیت پنهان (Hide) به نام شیت «تنظیمات» درست می‌کنم و کلیدهای تغییر رفتار برنامه را روی آن می‌گذارم. برای اینکه هروقت خواستم رفتار برنامه تغییر کند، مجبور نباشم بروم داخل کد را دست‌کاری کنم. برای نمونه اگر خواسته باشم به‌اختیار بتوانم رنگ پس‌زمینه را قرمز یا زرد کنم، به VBA می‌گویم اگر فلان خانه در شیت تنظیمات مقدارش ۱ بود قرمز کن، اگر ۲ بود زرد کن. حالا هر وقت بخواهم یکی از دو حالت را انجام بدهد، مقدار آن خانه‌ای را که نشانی‌اش را در کد داده‌ام، در شیت تنظیمات تغییر می‌دهم و ۱ یا ۲ می‌نویسم تا رنگ تغییر کند. این کار همچنین برای وقتی خوب است که بخواهیم تنظیماتی را در اختیار کاربر بگذاریم؛ ولی به کد دسترسی نداشته باشد.

با این مقدمه، راهکاری که برای نشانی‌ساختن با جدول‌های دارای سرستون فارسی پیدا کرده‌ام این است: به‌جای اینکه مقدار نام سرستون فارسی را در کد VBA بنویسم، به VBA می‌گویم: «همان که در فلان‌جا نوشته است».

چگونه راهکارم را اجرا می‌کنم

مرحلهٔ ۱: ساختن جدول سایه

برای هر جدول که سرستون‌های فارسی دارد، در شیت تنظیماتی که در آخر باید پنهان (Hide) بشود، یک جدول (Table) با همان تعداد ستون درست می‌کنم که فقط یک ردیف دارد؛ یعنی سرستون و یک ردیف. در آن یک ردیف، نام سرستون‌های فارسی جدول اصلی را با همان ترتیبی که هست با کپی می‌آورم. سپس در سرستونِ بالای هر نام فارسی، یک نام انگلیسی می‌نویسم.

بدین ترتیب هر ستون این جدولِ سایه بدین شکل می‌شود:

• سرستون: نام انگلیسی یکی از ستون‌های جدول اصلی
• خانهٔ زیرش: کپی نام فارسی آن ستون در جدول اصلی

نام آبجکت این جدول دوم را چیزی می‌گذارم که به ذهن نزدیک باشد تا در کدنویسی وقتی آن را می‌بینم، بدانم مربوط به کدام جدول اصلی است.

بالا: سرستون‌های جدول «ثبت اشخاص»، پایین: جدول سایه‌اش

مرحلهٔ ۲: متد (Method) نشانی‌ساز

در Calss فایل اکسلم، یک متد نشانی‌ساز ساده با نام MakeFaTableRng دارم با چهار آرگومان: ۱) نام جدول اصلی، ۲) نام جدول سایه‌اش در شیت تنظیمات، ۳) نام انگلیسی سرستون، ۴) شمارهٔ ردیف جدول سایه که همیشه ۱ است؛ چون یک ردیف بیشتر ندارد.

متد نشانی‌ساز با این چهار آرگومان مقدار نشانی را می‌سازد. مقداری که می‌سازد ترکیب شده است از نام جدول اصلی و عبارت فارسی سرستونی که خواسته‌ایم نشانی‌اش را بسازیم.

کد متد نشانی‌ساز:

Public Function MakeFaTableRng(ByVal TargetTable As String, ByVal AddressTable As String, ByVal AddressTableColumnName As String, ByVal AddressTableColumnRowNumber As Integer) As String
          Dim HeadStr As String
          HeadStr = Range(AddressTable & &quot[&quot & AddressTableColumnName & &quot]&quot).Cells(AddressTableColumnRowNumber, 1).Value
          MakeFaTableRng = TargetTable & &quot[&quot & HeadStr & &quot]&quot
End Function

مقدار (String) زیر نمونه‌ای است از آنچه این متد می‌سازد:

Persons[شناسه شخص]

مرحلهٔ ۳: ساختن پراپرتی (Property) برای مقدار نشانی

حالا فرض کنید می‌خواهم برای اولین بار، برای یکی از سرستون‌های فارسیِ یکی از جدول‌ها نشانی درست کنم. برای آن سرستون در Class برنامه، یک پراپرتی از نوع Get می‌سازم. متد نشانی‌ساز MakeFaTableRng را درونش اجرا می‌کنم و مقدارش را به پراپرتی می‌دهم. نام پراپرتی را هم طوری می‌گذارم که یادآور نام آن سرستون جدول فارسی باشد که می‌خواهم.

کد پراپرتی سرستون «شناسهٔ شخص» از جدول «ثبت اشخاص»:

'سرستون شناسهٔ شخص در جدول ثبت اشخاص

Property Get RNIDPerson() As String
          RNIDPerson = MakeFaTableRng(&quotPersons&quot, &quotPersonsHeadName&quot, &quotIDPerson&quot, 1)
End Property

مرحلهٔ ۴: استفاده در کد

حالا هروقت بخواهم در کد VBA برای آن ستون فارسی نشانی نسبی بسازم، به‌جای نوشتنِ مقدار ترکیب‌شده از نام جدول و نام فارسی سرستون، از پراپرتی آمادهٔ آن استفاده می‌کنم.

مانند کد زیر که با آن خواسته‌ام بدانم آیا نشانی Target ایونت، درون رنج ستون «شناسهٔ کاربر» در جدول «ثبت اشخاص» هست یا نیست. در کد زیر، پراپرتی با نام «RNIDPerson» را از کلاس «Tools» فراخوانی کرده‌ام. این پراپرتی همان مقدارِ دارای نوشتهٔ فارسی را دارد که اگر مستقیم در محیط کد می‌نوشتیم یا کپی می‌کردیم، اشکال پیدا می‌کرد.

If Class.IntersectControl(Target, Range(Tools.RNIDPerson)) = True Then
          ...
End if

نتیجه

بدین ترتیب می‌شود بدون اینکه مقدار فارسی را در کد بنویسیم، از ستون‌های با عنوان فارسی، نشانیِ نسبی جدولی بسازیم. البته باید یادمان باشد که هروقت در جدول اصلی نام سرستونی را تغییر دادیم یا ستون کم‌وزیاد کردیم، جدول سایه‌اش را هم مانند آن درست کنیم؛ وگرنه خطا پیش می‌آید.

شغل من کار با زبان فارسی است و به اکسل هم علاقه‌مندم.
لینکدین و توئیتر نویسنده.